2

I am trying to execute next query:

BEGIN;
INSERT INTO `jobs` ( `client` , `client_name` , `type` , `workers` , `date` , `hours` , `comments` , `project` ) VALUES ( '1' , 'one' , '1' , '1,2,5' , '2012-12-13' , '1234f' , '' , '0' ); 
INSERT INTO `jobs` ( `client` , `client_name` , `type` , `workers` , `date` , `hours` , `comments` , `project` ) VALUES ( '5' , 'two' , '3' , '1,2,5' , '2012-12-13' , '12' , '' , '0' ); 
COMMIT;

This returns the next MySQL 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 'INSERT INTO `jobs` ( `client` , `client_name` , `type` , `workers` , `date` , `h' at line 1

At the same time, phpMyAdmin manages to execute this query.

Could someone please point on a possible reason?

Michael Sazonov
  • 1,531
  • 11
  • 21
  • 2
    You need to separate them into multiple queries or use a database connector that supports multiple queries (e.g. mysqli) – Mike Dec 13 '12 at 20:09
  • 1
    PHP's mysql driver does not allow multiple queries in a single ->query() call. This is an anti-sql injection defense mechanism. Split the single query string into multiple individual queries. – Marc B Dec 13 '12 at 20:11
  • Try adding `DELIMITER ;` to the start of this script. – Sammitch Dec 13 '12 at 21:56
  • @Sammitch I guess, the answer I marked as correct is more secured. My logic was wrong from the beginning. Anyway, glad to know there's a simple way to solve the kind of question I wrote. Thank you. – Michael Sazonov Dec 13 '12 at 22:18

2 Answers2

1

You could try:

INSERT INTO `jobs` ( `client` , `client_name` , `type` , `workers` , `date` , `hours` , `comments` , `project` ) 
    VALUES ( '1' , 'one' , '1' , '1,2,5' , '2012-12-13' , '1234f' , '' , '0' )
    ,( '5' , 'two' , '3' , '1,2,5' , '2012-12-13' , '12' , '' , '0' );

That will give you two inserts in one statement.

DiverseAndRemote.com
  • 19,314
  • 10
  • 61
  • 70
0

This is not 'a' query. This are 4 queries. In PHP, you will have to call mysql_query four times.

berty
  • 2,178
  • 11
  • 19
  • You shouldn't recommend `mysql_*` functions. – Mike Dec 13 '12 at 20:13
  • @Mike I guess, you are talking about mysqli_*? Why so? – Michael Sazonov Dec 13 '12 at 20:15
  • I think Mike wanted to say that object-oriented approach is better. It's right, but it was not my purpose. It was an example. – berty Dec 13 '12 at 20:18
  • @MichaelSazonov http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php – Mike Dec 13 '12 at 20:20
  • Damn. The whole sql engine is gone now +) Thank you, guys! – Michael Sazonov Dec 13 '12 at 20:22
  • @aurel.g It's not that an object-oriented approach is better; `mysqli` is either OOP or procedural. See the link above for reasons why it should not be used. As well, it is now [officially deprecated in PHP 5.5](https://wiki.php.net/rfc/mysql_deprecation) so using it will produce a warning with PHP >= 5.5. The question was extension-agnostic, so there's no reason why your answer couldn't be too. – Mike Dec 13 '12 at 20:33