Using Stored Procedures in Msyql with PHP

This will take an input and output a single value. Must define input and output data types.


$db = mysqli_connect("127.0.0.1", "dash_rw", "password", "database");
$db->query("DROP PROCEDURE IF EXISTS p");

// take input and output single value. Output does not have to match table field length.
$db->query("CREATE PROCEDURE p(IN id_val INT, OUT pname varchar(100)) BEGIN SELECT name FROM panel WHERE id= id_val INTO pname; END;");
$db->query("CALL p(2,@pname)");
$res = $db->query("SELECT @pname as p_out");
$row = $res->fetch_assoc();
echo $row['p_out'];

This will return a single result set. Can also return multiple result sets


$db = mysqli_connect("127.0.0.1", "dash_rw", "password", "database");
$db->query("DROP PROCEDURE IF EXISTS p");

$db->query("CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT name FROM panel; END;");
// needs to be multi_query() even if returning just one result set
$db->multi_query("CALL p()");
// Store the first result set
$res = $db->store_result();

while($row=$res->fetch_assoc()) {
    echo 'name: ' . $row['name'] . '<br>';
}

$res->free();

// Get next result set
$db->next_result();
$res = $db->store_result();
...
$res->free();

Leave a comment

4 Comments.

  1. Hello mates, its wonderful paragraph concerning educationand completely explained, keep it up all the
    time.

  2. How to implement that code in PHP ?

  3. Great Article it its really informative and innovative keep us posted with new updates. its was reallyvaluable. thanks a lot.Nike Shoes very good.