How to call a MySQL stored procedure from within PHP code?
I now found solution by using mysqli
instead of mysql
.
<?php // enable error reportingmysqli_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 setwhile ($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
.
You can call a stored procedure using the following syntax:
$result = mysql_query('CALL getNodeChildren(2)');
This is my solution with prepared statements and stored procedure is returning several rows not only one value.
<?phprequire '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();