PHP doesn't commit mysql query

Question

Apparently the IF statement evaluates false, and I have no idea why. Even when I pull $mysqli->multi_query($dbq) out of the if statement, the database doesn't get queried. When using the three UPDATE statements in phpmyadmin they work just fine. My other script works fine, using multi_query, so I am a bit puzzled why this won't. Any idea why and what I need to change?

$dbq="UDATE ...;UPDATE ...;UPDATE ...;";

$mysqli = new mysqli("localhost", "root","","test");

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

//$result=$mysqli->multi_query($dbq);
if($mysqli->multi_query($dbq))
 { 
    do
    {

        if($result=$mysqli->store_result())
        {
            $up_cnt = $mysqli->affected_rows;
            echo "Affected rows: $up_cnt<br />";
            $result->free();
        }

        if($mysqli->more_results())
        {
            print("-------------------------------<br/>");
        }
        else
        {
            echo '<br/>';
        }
    }while($mysqli->more_results() && $mysqli->next_result());
 }
$mysqli->close();

UPDATE: Here's the query:

$dbq="UPDATE `table1` as t1, `table2` as t2,`table3` as t3 
SET t1.`na`=right(t3.QuestionAnswer,instr(reverse(t3.QuestionAnswer),";")-1), t1.`xa`= CASE right(t3.QuestionAnswer,instr(reverse(t3.QuestionAnswer),";")-1)
    WHEN 'Low' THEN 1
    WHEN 'Medium' THEN 2
    WHEN 'High' THEN 3
    WHEN 'Very High' THEN 4
    ELSE 0
    END
WHERE t3.`sfGroup`='finalAnswer' AND t2.`Status`='Approved' AND t3.`Subprocess`='A' AND t1.`na` IS NULL AND  t1.`sfid`=t2.`ITP` AND t2.`sfid`=t3.`BIA`;
UPDATE `table1` as t1, `table2` as t2,`table3` as t3
SET t1.`ni`=t3.`QuestionAnswer` , t1.`xi`= CASE t3.`QuestionAnswer`
    WHEN 'Low' THEN 1
    WHEN 'Medium' THEN 2
    WHEN 'High' THEN 3
    WHEN 'Very High' THEN 4
    ELSE 0
    END
WHERE t3.`sfGroup`='finalAnswer' AND t2.`Status`='Approved' AND t3.`Subprocess`='I' AND t1.`ni` IS NULL AND  t1.`sfid`=t2.`ITP` AND t2.`sfid`=t3.`BIA`;
UPDATE `table1` as t1, `table2` as t2,`table3` as t3
SET t1.`nc`=t3.`QuestionAnswer` , t1.`xc`= CASE t3.`QuestionAnswer`
    WHEN 'Low' THEN 1
    WHEN 'Medium' THEN 2
    WHEN 'High' THEN 3
    WHEN 'Very High' THEN 4
    ELSE 0
    END
WHERE t3.`sfGroup`='finalAnswer' AND t2.`Status`='Approved' AND t3.`Subprocess`='C' AND t1.`nc` IS NULL AND  t1.`sfid`=t2.`ITP` AND t2.`sfid`=t3.`BIA`;";

UPDATE 2: There's no error message. The screen remains white.

Can you post the query for us? You can redact any sensitive information it may contain, but it would be helpful to see a little more. — Muhammad Abdul-Rahim, May 05 '15 at 12:29
Please, post the entire query you are trying to perform. Note that in your example there's a typo: first `UPDATE` is missing a P: `$dbq="UDATE ...;UPDATE ...;UPDATE ...;";` — Reversal, May 05 '15 at 12:48

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

The problem is in your query: when you use instr you should escape double-quotes this way:

$dbq="UPDATE `table1` as t1, `table2` as t2,`table3` as t3 
SET t1.`na`=right(t3.QuestionAnswer,instr(reverse(t3.QuestionAnswer),\";\")-1), 
[.. cut ..]                                                           ^----
";

Moreover, you get no errors because error reporting is disabled. Check here how to enable it.

PHP doesn't commit mysql query

1 Answers1