How to call store procedure in MySQL uisng PHP and display the value return by the store procedure?

Question

Hi

I got stuck not knowing how to create API in PHP. I have store procedure that I created in the MySQL and want the PHP to call that store procedure and use the value return from the store procedure as return value of the API. There might be many ways to do it and I would like to know all of them and want to know how they are different.
-MySQL client version: 4.1.11
-MySQL Server version: 5.0.45

Here is my store 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 ;

If anything is unclear, please let me know. Thanks in advance.

---

Hi Petah

Now I have another store procedure that return more than one row for the the id that inputted. For example if i put 2 , then it return a column called name with more than one value such as
Name
People
Project

And another store procedure that return more than one fields such as
Name | Title
Economic | Economic is fun

I thought it supposed to be easy, but could not figure out.

Here is my code

DELIMITER $$
DROP PROCEDURE IF EXISTS `getNodeChildren` $$
CREATE PROCEDURE `getNodeChildren`(`nid` int)
BEGIN
    SELECT p.name from pro as p
    INNER JOIN tree as t 
    ON p.tree_node = t.id
    WHERE t.id = nid;
END $$
DELIMITER ;

<?php                   
  $nid = $_REQUEST["nid"];
  //either one did not work
  $res = mysql_query('SELECT getNodeChildren(2) as result');//('. $nid . )');      
  while ($obj = mysql_fetch_object($res)){
    echo $obj->result;
  }  
?>

The error read:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in *.php ... at line 16 (this line: $obj = mysql_fetch_object($res);)

Thanks

Answer 1

<?php
    $res = mysql_query('SELECT getTreeNodeName(1) AS result');
    if ($res === false) {
        echo mysql_errno().': '.mysql_error();
    }
    while ($obj = mysql_fetch_object($res)) {
        echo $obj->result;
    }