[RESOLVED] How to call MySQL stored proc from PHP

[A_PHP_Learner]

I have a MYSQL stored procedure named "spTop10MostExpensiveProducts()".

This procedure have no arguments. I want to call this procedure and fetch the row with PHP. How do I accomplish this? I am a PHP learner. Please help. Thanks.

$mdb2 = MDB2::connect($dsn);
if (PEAR::isError($mdb2))
{
die("Could not connect to the database. <br />");

}

      $mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
      $sp = $mdb2->execute("call spTop10MostExpensiveProducts ()");

      while ($row = $result->fetchRow())
      {
                 echo $row['productname'];
                 echo $row['unitprice'];
      }

---

[paulnaj]

This looks comprehensive ...http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/

[A_PHP_Learner]

Can someone help me with my codes ? I am using PEAR mdb2. How do I fetch rows from the result set? The WHILE LOOP does not seem to work.

$mdb2 = MDB2::connect($dsn);
if (PEAR::isError($mdb2))
{
die("Could not connect to the database. <br />");
}

      $mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
      $query = "call spTop10MostExpensiveProducts ( )";
      $result = $mdb2->query($query);
  if (PEAR::isError($mdb2))
      {
          die("Could no run report.".$mdb2->getMessage().");                           
      }
       while ($row = $result->fetchRow())
       {
                  echo $row['productname'];
                  echo $row['unitprice'];
       }

[paulnaj]

Hi, what's the reason for using mdb2 instead of mysqli, or PDO?

I've never used it, but googling 'mdb2 executeStoredProc' gave me a few results that might help. Eg. http://www.phpbuilder.com/board/archive/index.php/t-10332897.html
Basically though, seems that PDO will do sp's better than mdb2.

[A_PHP_Learner]

Now I have changed to use PDO instead of MDB2. What is wrong with the codes? It does not echo the product name in the WHILE loop. Please help. Thank You.

$dbh = new PDO("mysql:host=localhost;dbname=crm", "$username", "$password");

$stmt = $dbh->prepare("CALL spTop10MostExpensiveProducts()");
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
echo $row['productname'];

[A_PHP_Learner]

Problem Resolved. Thank You.