Pheap Pheap - 5 months ago 20
SQL Question

How to call a MySQL stored procedure from within PHP code?

I have stored procedure that I created in MySQL and want PHP to call that stored procedure. What is the best way to do this?

-MySQL client version: 4.1.11

-MySQL Server version: 5.0.45

Here is my stored procedure:

DELIMITER $$

DROP FUNCTION IF EXISTS `getNodeName` $$
CREATE FUNCTION `getTreeNodeName`(`nid` int) RETURNS varchar(25) CHARSET utf8
BEGIN
DECLARE nodeName varchar(25);
SELECT name into nodeName FROM tree
WHERE id = nid;
RETURN nodeName;
END $$

DELIMITER ;


What is the PHP code to invoke the procedure getTreeNodeName?

Answer

I now found solution by using mysqli instead of mysql.

<?php 

  //connect to database
  $connection = mysqli_connect("hostname", "user", "password", "db", "port");

  //run the store proc
  $result = mysqli_query($connection, 
     "CALL StoreProcName") or die("Query fail: " . mysqli_error());

  //loop the result set
  while ($row = mysqli_fetch_array($result)){   
      echo $row[0] . " - " . + $row[1]; 
  }

?>

I found that many people seem to have a problem with using mysql_connect, mysql_query and mysql_fetch_array.