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: stackoverflow.com/a/20433501/445131 — Eric Leschinski
– Eric Leschinski, Commented Dec 6, 2013 at 21:25
That's not a procedure (call procedurename), its a function (SELECT functioname(args)) — symcbean
– symcbean, Commented Jan 17, 2024 at 15:50

Dharman · Accepted Answer · 2019-09-22 20:40:52Z

56

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, 2019 at 20:40

Dharman♦

33.9k27 gold badges105 silver badges157 bronze badges

answered Nov 12, 2010 at 5:33

Pheap

2,3894 gold badges18 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Troy Alford · Accepted Answer · 2012-11-28 19:40:55Z

13

You can call a stored procedure using the following syntax:

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

edited Nov 28, 2012 at 19:40

Troy Alford

27.3k11 gold badges67 silver badges85 bronze badges

answered Feb 25, 2011 at 5:31

phpadmin

1551 silver badge3 bronze badges

2 Comments

Praesagus Over a year ago

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

Sboniso Marcus Nzimande Over a year ago

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

Dharman · Accepted Answer · 2019-12-14 17:15:59Z

4

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();

edited Dec 14, 2019 at 17:15

Dharman♦

33.9k27 gold badges105 silver badges157 bronze badges

answered Dec 14, 2019 at 10:24

Zahra

2,8783 gold badges28 silver badges53 bronze badges

Comments

Petah · Accepted Answer · 2010-10-20 08:08:07Z

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, 2010 at 8:08

answered Oct 19, 2010 at 9:06

Petah

46.2k30 gold badges161 silver badges216 bronze badges

4 Comments

Pheap Over a year ago

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 Over a year ago

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

Pheap Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

2 Comments

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

4 Comments

Linked

Related