SQL statement in PHP behaves differently than SQL statement in phpmyadmin

Question

I have

$form_store_sql = "                                                                                                                    
     INSERT INTO myodyssey_myaccount (`id`, `email`, `username`, `password`) VALUES (NULL, 'email', 'unixmiah.formtest', 'woohoo');         

     SET @last_id_in_myaccount = LAST_INSERT_ID();                                                                                      

     INSERT INTO myodyssey_personal_info (`id`, `myodyssey_myaccount_id`) VALUES (NULL, @last_id_in_myaccount);                             

    SET @last_id_in_personal_info = LAST_INSERT_ID();                                                                                  

    INSERT INTO myodyssey_travel_info (`id`, `myodyssey_personal_info_id`)                                                                 
        VALUES (NULL, @last_id_in_personal_info);                                                                                                      

     SET @last_id_in_travel_info = LAST_INSERT_ID();                                                                                    

     INSERT INTO myodyssey_tour_orders (`id`, `myodyssey_travel_info_id`) VALUES (NULL, @last_id_in_travel_info);";

     if(mysql_query($form_store_sql)){
       echo "done";
     }

It doesn't work; it doesn't store the data. But if I take the SQL statement out of the form_store_variable and paste it into phpmyadmin's sql dialog, it behaves differently, it stores the data. I wonder what I'm doing wrong storing the SQL statement in the form_store_variable.

qa.odysseysafaris.com/my-odyssey-application/form-wizard/… — unixmiah
– unixmiah, Commented Sep 12, 2014 at 20:40

halfer · Accepted Answer · 2014-09-12 21:01:15Z

3

mysql_*() functions do NOT allow multiple statements like that in a single query call. It's a basic defense against some forms of SQL injection attacks.

If you'd used any kind of error handling on your query call, you'd have been informed of the syntax error:

$result = mysql_query($form_store_sql);
if ($result === false) {
   die(mysql_error());
}

You will have to query() each of those individual statements separately.

edited Sep 12, 2014 at 21:01

halfer

20.2k20 gold badges111 silver badges208 bronze badges

answered Sep 12, 2014 at 20:20

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

unixmiah Over a year ago

Thank you. I will try that and avoid code backfires.

Barmar · Accepted Answer · 2014-09-12 20:21:42Z

2

mysql_query() doesn't allow multiple queries in a single call. You need to split up $form_store_sql into separate strings for each query, and call mysql_query() separately for each of them.

$sql = "INSERT INTO myodyssey_myaccount (`id`, `email`, `username`, `password`) 
        VALUES (NULL, 'email', 'unixmiah.formtest', 'woohoo');";
mysql_query($sql);
$sql = "SET @last_id_in_myaccount = LAST_INSERT_ID();";
mysql_query($sql);
...

You can also avoid doing a separate call just to set a variable to LAST_INSERT_ID(), by just using that function in the next insert:

$sql = "INSERT INTO myodyssey_personal_info (`id`, `myodyssey_myaccount_id`)
        VALUES (NULL, LAST_INSERT_ID());"
mysql_query($sql);

answered Sep 12, 2014 at 20:21

Barmar

789k57 gold badges555 silver badges669 bronze badges

2 Comments

unixmiah Over a year ago

Thanks. I didn't know that. I thought whatever query you had in the sql statement from the php side would get processed in the single mysql_query function. Saved me hours worth of figuring out what went wrong.

unixmiah Over a year ago

this makes sense now. thank you. i was under the impression that it would process what you actually had on the php side; doesn't matter if it was broken up or not. it makes sense considering its a security breach.

Collectives™ on Stack Overflow

SQL statement in PHP behaves differently than SQL statement in phpmyadmin

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related