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

Question

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?

Walkthrough on creating a MySQL stored procedure: http://stackoverflow.com/a/20433501/445131 — Eric Leschinski, Dec 06 '13 at 21:25

score 54 · Accepted Answer · edited Sep 22 '19 at 20:40

54

I now found solution by using mysqli instead of mysql.

<?php 

// enable error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
//connect to database
$connection = mysqli_connect("hostname", "user", "password", "db", "port");

//run the store proc
$result = mysqli_query($connection, "CALL StoreProcName");

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

edited Sep 22 '19 at 20:40

Dharman

30,962
25
85
135

answered Nov 12 '10 at 05:33

Pheap

2,349
4
18
13

1

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') { } ` – ajreal Nov 12 '10 at 05:44
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)); – Praesagus Nov 07 '14 at 01:34
1

The mysql extension is `deprecated` and will be removed in the future – Dhiraj Thakur Sep 13 '17 at 11:38

score 13 · Answer 2 · edited Nov 28 '12 at 19:40

13

You can call a stored procedure using the following syntax:

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

edited Nov 28 '12 at 19:40

Troy Alford

26,660
10
64
82

answered Feb 25 '11 at 05:31

phpadmin

155
1
3

1

+1 because it worked, however mysql_query stopped working for all following select statements. – Praesagus Nov 07 '14 at 01:35
I assume that you can also add a variable like this: $result = mysql_query('CALL getNodeChildren($a)'); – Sboniso Marcus Nzimande Dec 03 '14 at 13:07

score 4 · Answer 3 · edited Dec 14 '19 at 17:15

This is my solution with prepared statements and stored procedure is returning several rows not only one value.

<?php

require 'config.php';
header('Content-type:application/json');
$connection->set_charset('utf8');

$mIds = $_GET['ids'];

$stmt = $connection->prepare("CALL sp_takes_string_returns_table(?)");
$stmt->bind_param("s", $mIds);

$stmt->execute();

$result = $stmt->get_result();
$response = $result->fetch_all(MYSQLI_ASSOC);
echo json_encode($response);

$stmt->close();
$connection->close();

Petah · Answer 4 · 2010-10-20T08:08:07.797

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

edited Oct 20 '10 at 08:08

answered Oct 19 '10 at 09:06

Petah

45,477
28
157
213

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? – Pheap Oct 20 '10 at 07:48
@user480259, Ive modified the snippet I gave you to include error checking. Try that and see what you get. – Petah Oct 20 '10 at 08:08
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 Oct 20 '10 at 08:23
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". – Pheap Oct 21 '10 at 00:50

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

4 Answers4

Linked

Related