UPDATE Syntax in mysql 5.7

Question

Im trying to run a simple UPDATE query:

UPDATE students SET enterTime = '".$enterTime."' WHERE s_id = '".$s_id."'; UPDATE timeLimit  SET listed = listed + 1  WHERE enterTime = '".$enterTime."' AND building = '".$building."';"

It works fine in MySql version 5.1 , my server has been upgraded to MySql version 5.7 and I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE students SET enterTime = '09:00' WHERE ' at line 1 BEGIN; UPDATE students SET enterTime = '09:00' WHERE s_id = '312433931' ; UPDATE timeLimit SET listed = listed + 1 WHERE enterTime = '09:00' AND building = '2'; UPDATE timeLimit SET listed = listed - 1 WHERE enterTime = '08:00' AND building = '2'; COMMIT;

PHP code:

function update_time_for_student($student,$enterTime,$oldTime){
    $s_id = $student['s_id'];
    $building = $student['building'];
    $query = "BEGIN; UPDATE students 
                SET enterTime = '".$enterTime."' 
                WHERE s_id = '".$s_id."' ;              
               UPDATE timeLimit 
                SET listed = listed + 1 
                WHERE enterTime = '".$enterTime."' AND building = '".$building."';
              UPDATE timeLimit 
                SET listed = listed - 1 
                WHERE enterTime = '".$oldTime."' AND building = '".$building."'; COMMIT;";
    if (mysql_query($query)){
        $fullName = $student['fname']." ".$student['lname'];
        send_email($student['email'],$enterTime,$fullName,$s_id);
        header("Location:/index.php?d=1"); 
    } else {
        echo mysql_error();
        echo "<br/>";
        echo $query;
        //header("Location:/index.php?d=3");
    }  
}

Thank you very much for your help !

From the error, it seems like you're looping the query, try check your looping — MuthaFury, Aug 02 '16 at 09:31
I run through PHP, unfortunately I can not upload here the code for character limit. — user3617337, Aug 02 '16 at 09:35
The code is here: https://docs.google.com/document/d/1sMh9H_Phnf_Qvp38cPc6jJ9gkAPXHdEs1sc9rgCiUpQ/edit?pref=2&pli=1 — user3617337, Aug 02 '16 at 09:40

score 1 · Answer 1 · edited May 23 '17 at 10:28

1

Option-1

Try breaking the lines into multiple php statements:

$query = "BEGIN";
mysql_query($query) or die (mysql_error());

$query = "UPDATE students SET enterTime = '".$enterTime."' WHERE s_id = '".$s_id."';";
mysql_query($query) or die (mysql_error());

$query = "UPDATE timeLimit  SET listed = listed + 1  WHERE enterTime = '".$enterTime."' AND building = '".$building."';";
mysql_query($query) or die (mysql_error());

$query = "COMMIT";
mysql_query($query) or die (mysql_error());

Option-2

you need to use multi_query instead. Docs here

Note: mysql_* commands are deprecated and removed in PhP7. Instead use mysqli or PDO. Refer the accepted answer here

edited May 23 '17 at 10:28

Community

1
1

answered Aug 02 '16 at 09:48

Tamil

1,193
9
24

My code looks like this : https://docs.google.com/document/d/14sIJUfwbGGzLQAVxOf5JCU6FO4zpPhMBu2sB_h4TiuM/edit?usp=sharing I get the following error on the line 53 ( highlighted in red in the code ) **Parse error: syntax error, unexpected T_VARIABLE** – user3617337 Aug 05 '16 at 12:02
You are missing `semi-colon` for previous line. `mysql_query($query) or die (mysql_error())` -> `mysql_query($query) or die (mysql_error());`. I have edited my answer too!! – Tamil Aug 05 '16 at 12:48

UPDATE Syntax in mysql 5.7

1 Answers1