Pheap Pheap - 1 year ago 62
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:


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


What is the PHP code to invoke the procedure getTreeNodeName?

Answer Source

I now found solution by using mysqli instead of mysql.


  //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.