By Pheap


2010-10-19 09:00:47 8 Comments

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?

6 comments

@Ravi Mane 2015-02-19 20:40:43

Following code show, how to call store procedure with example

$con = mysql_connect('localhost','root','');  
mysql_select_db('books'); 
//Call the proc() procedure follow
$result= mysql_query("CALL proc();") or die(mysql_error()); 

while($row = mysql_fetch_row($result))
{
for($i=0;$i<=6;$i++)
{ 
echo  $row[$i]."<br>"; 
}  
}  
mysql_close($con); 

@alalmighty 2017-04-15 08:18:50

Please refer to Call a stored procedure from PHP from the Artful MySQL Tips List for calling a stored procedure

The official PHP docs also provide direction

@Pheap 2010-11-12 05:33:56

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.

@ajreal 2010-11-12 05:44:59

many people make mistake because they dun bother to check is if ($connection instanceof mysqli) { }, same as if ($arr[0]=='forget checking') { }; without having if (is_array($arr) && isset($arr[0]) && $arr[0]=='forget_checking') { }

@Praesagus 2014-11-07 01:34:27

mysqli_connect would not work for me without an or die statement at the end: $connection = mysqli_connect("hostname", "user", "password", "db", "port")or die("Error " . mysqli_error($connection));

@Dhiraj Thakur 2017-09-13 11:38:13

The mysql extension is deprecated and will be removed in the future

@GkDreamz 2013-02-01 06:06:39

i got solution from php.net

<?php
$mysqli = new mysqli("localhost", "root", "", "joomla2.5");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

/*if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)") ||
    !$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
    !$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;')) {
    echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$mysqli->multi_query("CALL p()")) {
    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        printf("---\n");
        var_dump($res->fetch_all());
        $res->free();
    } else {
        if ($mysqli->errno) {
            echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;
        }
    }
} while ($mysqli->more_results() && $mysqli->next_result());

?>

@phpadmin 2011-02-25 05:31:03

You can call a stored procedure using the following syntax:

$result = mysql_query('CALL getNodeChildren(2)');

@Praesagus 2014-11-07 01:35:22

+1 because it worked, however mysql_query stopped working for all following select statements.

@Sboniso Marcus Nzimande 2014-12-03 13:07:33

I assume that you can also add a variable like this: $result = mysql_query('CALL getNodeChildren($a)');

@Petah 2010-10-19 09:06:04

<?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;
    }

@Pheap 2010-10-19 20:52:29

Thanks Petah, it works.

@Pheap 2010-10-20 07:48:27

It works for the first question when the store procedure returns only one value, but the code for the multiple results does not work. Any idea?

@Petah 2010-10-20 08:08:52

@user480259, Ive modified the snippet I gave you to include error checking. Try that and see what you get.

@Pheap 2010-10-20 08:23:56

It said: "1305: FUNCTION getNodeChildren does not exist". I checked the database and it did exist and when i run it got the result as well. But it is a procedure not a function. Does that matter?

@Pheap 2010-10-21 00:50:55

I changed the code from "SELECT" to "CALL", then I got this error: "1312: PROCEDURE proc-name can't return a result set in the given context".

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

33 Answered Questions

[SOLVED] Reference - What does this error mean in PHP?

7 Answered Questions

[SOLVED] How does PHP 'foreach' actually work?

15 Answered Questions

[SOLVED] How to get a list of user accounts using the command line in MySQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

35 Answered Questions

[SOLVED] Deleting an element from an array in PHP

  • 2008-12-15 20:28:55
  • Ben
  • 2258407 View
  • 2232 Score
  • 35 Answer
  • Tags:   php arrays

26 Answered Questions

30 Answered Questions

[SOLVED] How do you parse and process HTML/XML in PHP?

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

15 Answered Questions

[SOLVED] List of Stored Procedures/Functions Mysql Command Line

Sponsored Content