pdo_pgsql_stored_func1.htm
Objectives:
  • creating a function to convert Celsius to Fahrenheit
  • call this function using php
  • MySQL and PostgreSQL stored function
 

 
MySQL

 
code Used : PostgreSQL

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>pdo_pgsql_stored_func1</title>
</head>
<body>
<?php
$user="manas237";
$pwd ="pwmanas237";
//
try {
$dbconn = new PDO("pgsql:dbname=pgsdemo1;host=localhost", $user, $pwd );
echo "PDO connection object created";
echo "<br/> with user : ". $user ."and pwd ".$pwd;
echo "<br/>";
$sql= "SELECT round (cfcal(19.25),2)";
$stmt = $dbconn->query($sql);
$stmt->execute();

/* Fetch only the first row from the results */
$result= $stmt->fetch();
echo "29.25<sup>o</sup>C = ".$result[0]." <sup>o</sup>F ";
echo "<br/>db closed ";
/*** close the database connection ***/
$dbconn= null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

</body>

</html>
 

Displaying in a web browser
You can use SQL query to call  a PL/pgsql Function using PHP ,as shown below

Intranet Link :PSQL_Query_Tool.php

 
Code used in MYSQL

<html>
<head>
<title>pdo_stored_func4.php </title>
<head>
<body>
Using $stmt = $dbconn->prepare($sql);<br/>
$stmt->execute();<br/>
Fetch only the first row from the results <br/>
$result= $stmt->fetch();<br/><hr>
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'Manas9';
/* dbname */
$db = "test";
/*** mysql password ***/
$password = 'Manas9237';

try {

$dbconn= new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Retreive of MySQL stored function with PDP <br />';
echo 'Connected to database <br />';
/*** The SQL SELECT statement ***/
//$sql = "SELECT * FROM emp1 ORDER BY fname";
$sql= "SELECT ctof(29.25)";
echo" fetch into an PDOStatement object <br/>";
$stmt = $dbconn->prepare($sql);
$stmt->execute();

/* Fetch only the first row from the results */
$result= $stmt->fetch();
echo "29.25<sup>o</sup>C = ".$result[0]." <sup>o</sup>F ";
echo "<br/>db closed ";
/*** close the database connection ***/
$dbconn= null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
</body>
</html>

Web-Display
 
Compare Codes :
  • Connection
    • PostgreSQL :
      $dbconn = new PDO("pgsql:dbname=pgsdemo1;host=localhost", $user, $pwd );
    • MySQL :
      $dbconn= new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
  • SQL : Querying
    • PostgreSQL ( return value was default numeric ****)
      $sql= "SELECT round (cfcal(19.25),2)";
      $stmt = $dbconn->query($sql);
      $stmt->execute();
    • MySQL:
      $sql= "SELECT ctof(29.25)";
      echo" fetch into an PDOStatement object <br/>";
      $stmt = $dbconn->prepare($sql);
      $stmt->execute();
  • Output:
    • PostgreSQL:
      /* Fetch only the first row from the results */
      $result= $stmt->fetch();
      echo "29.25<sup>o</sup>C = ".$result[0]." <sup>o</sup>F ";
      echo "<br/>db closed ";
      /*** close the database connection ***/
      $dbconn= null;
    • MySQL

      /* Fetch only the first row from the results */
      $result= $stmt->fetch();
      echo "29.25<sup>o</sup>C = ".$result[0]." <sup>o</sup>F ";
      echo "<br/>db closed ";
      /*** close the database connection ***/
      $dbconn= null;

 
 
Note: With PostgreSQL, numeric data output was  different than that of MySQL; in MySQL NUMERIC (4,2) had to be defined to get 2 decimal data. By default MySQL returned Integer hiding decimal values.

MYSQL