I've got a PHP page with an HTML form on it for the purpose of changing some information in the MySQL database. The form submits to another PHP page that makes about 7-10 queries based on the information received from the form. It's very important that these queries be in a specific order. I'm using mysqli::multi_query
to carry out these queries. At the end after I carry out my query I use header("Location: " . $_SERVER['HTTP_REFERER']);
to return the user to the page with the form. My problem arises when the user is returned to this previous page. The page loads at a point in time that appears mid-query, not with the finished product. If I then hit refresh it loads with the updated information. How can I prevent my page from loading until it can obtain the updated information from the database instead of loading mid-query?
Edit to add code (even though I don't think it's relevant to the underlying question):
$sql = "SELECT * FROM tool_categories";
if($result = $MySQLi->query($sql)){
$toolCategories = array();
while($row = $result->fetch_assoc()){
$toolCategories[] = $row;
}
$result->free();
}
if(isset($_POST['editCategory'])){ // Editing category
if(!in_array($_POST['categoryName'], $toolCategories)){ // Make sure it doesn't exist already
$sql = "UPDATE tool_categories SET categoryName='" . $_POST['categoryName'] . "' WHERE categoryID=" . $_POST['categoryID'];
if($_POST['placement'] != 0){
if(!in_array($_POST['placement'], array_column($toolCategories, 'categoryID'))){ // Check if it exists
$sql .= "; UPDATE tool_categories SET categoryID=" . $_POST['placement'];
}else{
// Welp, gotta make some changes to categoryID's to make this fit!
$sql = "UPDATE tool_categories SET categoryID=0 WHERE categoryID=" . intval($_POST['categoryID']) . ";";
$sql .= "UPDATE tool_categories SET categoryID=categoryID-1 WHERE categoryID >= " . intval($_POST['categoryID']) . ";";
$sql .= "UPDATE tools SET categoryID=categoryID-1 WHERE categoryID >= " . intval($_POST['categoryID']) . ";";
$sql .= "ALTER TABLE tool_categories DROP INDEX categoryID;";
$sql .= "ALTER TABLE tool_categories DROP PRIMARY KEY;";
$sql .= "UPDATE tool_categories SET categoryID=categoryID+1 WHERE categoryID >= " . intval($_POST['placement']) . ";";
$sql .= "UPDATE tools SET categoryID=categoryID+1 WHERE categoryID >= " . intval($_POST['placement']) . ";";
$sql .= "ALTER TABLE tool_categories ADD INDEX categoryID (categoryID);";
$sql .= "ALTER TABLE tool_categories ADD PRIMARY KEY(categoryID);";
$sql .= "UPDATE tool_categories SET categoryID=" . intval($_POST['placement']) . ", categoryName='" . $_POST['categoryName'] . "' WHERE categoryID=0";
}
}
}
}
$startQuery = microtime(true);
$numberOfQueries = count(explode(';', $sql));
if(!$MySQLi->multi_query($sql)){
die(db_error());
for($i = 2; $i < $numberOfQueries+1; $i++){
if(!$MySQLi->next_result()){
die(db_error());
}
}
}
$endQuery = microtime(true);
$queryTime = $endQuery - $startQuery;
header("Location: " . $_SERVER['HTTP_REFERER'] . "&queryTime=" . $queryTime . "&queries=" . $numberOfQueries);
The below code is referenced from the following post provided by @mickmackusa in an above comment. Strict Standards: mysqli_next_result() error with mysqli_multi_query
if($MySQLi->multi_query($sql)){
do{} while($MySQLi->more_results() && $MySQLi->next_result());
}
if($error_mess = $MySQLi->error){ die("Error: " . $error_mess); }
This code managed to prevent my next page from loading until all queries were completed as intended.