PHP Page Loads Mid-Query (MySQL)

Question

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

We can't help you without seeing your code. Please read [ask]. — ChrisGPT was on strike, Mar 19 '17 at 12:12
Also two notes regarding your code: 1. You should be using prepared statements to carry out your queries. 2. HTTP_REFERER is not reliable and it's better not to use it — Your Common Sense, Mar 19 '17 at 12:20
I've not seen a multi-query block like that before. Is it returning mid-way because the the multi-query is failing somehow mid-way? Perhaps compare to this http://stackoverflow.com/questions/14715889/strict-standards-mysqli-next-result-error-with-mysqli-multi-query/22469722#22469722 — mickmackusa, Mar 19 '17 at 12:35
Please unmark YourCommonSense's answer because it will mislead future readers to believe that `multi_query` is to blame and is inherently faulty. As you acknowledged your comments, it was an uncaught error not the function itself. Furthermore, because your issue was a typo or slight logical mistake, this question/answer will not be valuable to future readers -- for this reason please consider deleting your question, or at the very least submit a new answer that shows how you corrected your issue. As is, this page is promoting an incorrect assertion. Please fix this for future readers. — mickmackusa, Mar 20 '17 at 03:14
@mickmackusa - Technically speaking, it does solve my original issue stated above. My problem was that I was returning to the previous form page before MySQL data was updated. The answer he provided solved my actual problem because I now run my queries one by one and it's no longer loading until it's finished, which is what I wanted. So it is the answer I was looking for. If you don't agree with that, let me know why you don't think it answers my question. Maybe I didn't actually ask my original question clearly. — HereToFigureThisOut, Mar 20 '17 at 11:16
@HereToFigureThisOut I've had enough interactions with YourCommonSense to know that he has an impressive understanding of mysqli and pdo processes (and that he will not be moved on his opinions). What I wonder is, if you used a multi-query statement block similar to the one I linked earlier, would your code process all of the queries as intended before running the header() line? This would be educational for me as well. — mickmackusa, Mar 20 '17 at 11:32
@mickmackusa - I gave the link you posted a quick look originally and kind of brushed it off. Sorry! lol I just implemented that code and it actually does prevent the page from loading until all of the queries have finished as intended. I'm not sure that I'll keep using that method though, we'll see after I implement the use of prepared statements. Because he was definitely right that I should be using prepared statements. Confirmed though, it works as intended. — HereToFigureThisOut, Mar 20 '17 at 11:57
Yes, I too have moved to prepared statement, but I don't want to scare people off of completely valid functions (`mysqli_multi_query`). The thing that most people aren't ready for with this function is that it short circuits on the first error. Again, this page is sending an incorrect assertion and I don't want people to be misguided. Using individual queries allowed you to find the true problem. `mysqli_multi_query` was actually designed for a block of queries like yours. — mickmackusa, Mar 20 '17 at 12:03
p.s. I also don't like to see unresolved questions on SO, so how would you like to proceed with laying this question to rest. Would you like me to post a multi_query code block? or would you like to do it? Since this question isn't going to earn a closure by moderators, IMO it should either have a different accepted answer or be deleted so that it doesn't mislead future SO readers. — mickmackusa, Mar 20 '17 at 12:16
I'll handle it in a moment. I'm working on a multi_query function for prepared statements right now. I'll post the accepted answer in a few. Orrr you can do it if you'd like. — HereToFigureThisOut, Mar 20 '17 at 12:57
@mickmackusa - I posted the proper solution below. It won't let me accept my own post as the answer for 22 hours lol So in 22 hours we'll settle this question. — HereToFigureThisOut, Mar 20 '17 at 14:02

score 1 · Accepted Answer · edited May 23 '17 at 12:32

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.

score 0 · Answer 2 · answered Mar 19 '17 at 12:16

0

You should avoid mysqli::multi_query. Run your queries separately one by one instead. It will prevent your page from loading until it can obtain the updated information.

answered Mar 19 '17 at 12:16

Your Common Sense

156,878
40
214
345

Well here lies a new problem. I tried earlier to use a few `mysqli::query` statements and it was only executing the first one I called. That's why I started using `mysqli::multi_query`. I don't have that code from earlier to show you, as I replaced it. – HereToFigureThisOut Mar 19 '17 at 12:33
What I said isn't nonsense. It's a problem. Why it only executed the first one I called is what's nonsense. lol – HereToFigureThisOut Mar 19 '17 at 12:39
Everything that works with multi-query, works for the standard separate query as well. If, by chance, you made a mistake in one of your queries, you have to check for the error and then fix it. It's simple. – Your Common Sense Mar 19 '17 at 12:40
So anyway, using my current code I should be able to use the following: `$queryArray = explode(';', $sql); for($i = 0; $i > count($queryArray); $i++){ $MySQLi->query($queryArray[$i]); }` – HereToFigureThisOut Mar 19 '17 at 12:42
1. Only if there are no semicolons in the input. 2. there is no error handling in this code while there should be. 3. Given there are no semicolons and no errors ,this code should work. – Your Common Sense Mar 19 '17 at 12:44
Perhaps that's my issue then. I have semicolons trailing all of these queries. – HereToFigureThisOut Mar 19 '17 at 12:47
You're not in casino. This is programming, not gambling. There are no things like "perhaps". Either your query runs or it returns an error. An error you can read and get the certain bloody idea what's going on, without any "perhaps" – Your Common Sense Mar 19 '17 at 12:50
Using the above snippet I added `$MySQLi->query($queryArray[$i]) or trigger_error('Error: ' . $MySQLi->error)` and receive no errors, but it does nothing to the database. – HereToFigureThisOut Mar 19 '17 at 12:52
As you can tell, that's impossible. Is your mysql standalone? Do you use master/slave config or cloud stuff? – Your Common Sense Mar 19 '17 at 12:57
Sorry for the delay in reply. My PHP and MySQL are both installed using XAMPP and I'm running all of this only on localhost. – HereToFigureThisOut Mar 19 '17 at 14:25
Found my error that time. Where `$i > count($queryArray)` should've been `$i < count($queryArray)`. So that works as intended now! Of course all of these problems were caused by me, I acknowledge that. lol Thanks for the help! – HereToFigureThisOut Mar 19 '17 at 14:40

PHP Page Loads Mid-Query (MySQL)

2 Answers2