mysqli & nested queries - maybe I'm rusty...
| From: | Jay Blanchard | Date: | Mon, 01 Apr 2013 16:33:27 +0000 |
| Subject: | mysqli & nested queries - maybe I'm rusty... | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-320724@lists.php.net to get a copy of this message | ||
I am putting together an application where we are almost exclusively using stored procedures in MySQL because most of the heavy lifting (as it should be) is done in the database.
I have exactly one situation where I need to execute a stored procedure and while returning the results if I run a across a particular value in those results I need to execute another SP to handle. Because I have forgotten which combination or store_result, use_result, fetch_row (not to mention that fetch row doesn't have the column names in the array, just the index) to use I am banging my head against the wall while getting the dastardly "2014 Commands out of sync;" error. Can someone provide some insight please?
Assume that $mysqli is the connection -
$sql = "CALL sp_ONE(" . $product . "," . $level . ")";
if(!$result = $mysqli->multi_query($sql)) {
echo "Error: (" . $mysqli->errno . ") " . $mysqli->error . " on Query " . $sql;
} else {
do {
if($side = $mysqli->store_result()) {
/* output the list */
echo '<ul>';
while($row = $side->fetch_row()) {
/* careful, column names aren't carried over */
echo '<li data-page="' . $row[2] . '">' . $row[1] . '</li>';
/* do we need to do a another stored procedure? */
if(isset($row[3])) {
/* get the info */
$sql = "CALL sp_TWO(" . $product . "," . $row[0] . ")";
if(!$subResult = $mysqli->multi_query($sql)) {
echo "Error: (" . $mysqli->errno . ") " . $mysqli->error . " on Query " . $sql;
}
/* output the extra info */
echo '<ul>';
while($sub = mysqli_fetch_array($subResult)) {
echo '<li data-page="' . $sub[1] . '">' . $sub[0] . '</li>';
}
echo '</ul>';
}
}
echo '</ul>';
$side->close();
}
} while($mysqli->next_result());
}