CODEWITHSUNDEEP
phpmysqlsql
Harley Fuagras
Harley Fuagras

Reputation: 499

How can I solve syntax error on multistatement mysql query near semicolon

I am trying to do a multistatement query with mysql but I am getting a syntax 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 vinas_new ( img, new_order, publi' at line 2"

The weird thing is that if I echoe the string of the query and I try to execute it on Navicat it works without problem.

The string of the query I am trying to use is this one.

'BEGIN;
    INSERT INTO vinas_new (
        img,                                
        new_order,
        publicationDate,
        active,
        cover
    )VALUES(
        "'.$data['img'].'",                             
        "'.$data['new_order'].'",
        "'.$data['publicationDate'].'",
        "'.$data['active'].'",
        "'.$data['cover'].'"
    );                          
    INSERT INTO vinas_new_content (
        newId,
        lang,
        title,
        text
    )VALUES(    
        LAST_INSERT_ID(),
        "'.mysql_real_escape_string($data['lang']).'",
        "'.mysql_real_escape_string($data['title']).'",
        "'.mysql_real_escape_string($data['text']).'"
    );
COMMIT;';

I imagine it has something to do with the semicolon mark and I've tried to use delimiters to avoid this problem, but without any luck.

Any suggestions?

Thank you for your time.

Upvotes: 0

Views: 510

Answers (1)

Vyktor
Vyktor

Reputation: 20997

The easiest way to debug this would be to just show us result of echo $sql (or whatever is the name of variable you store query in).

However you seem to use obsolete mysql_ API which doesn't support multiple queries:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier

Try switching to mysqli (or PDO) and use prepared statement (or 3 of them) so you wouldn't have to escape data by your self (and if you want to stuck with mysql_ you have to split it to 3 queries anyway; or create stored procedure for the task).

Or if you insist on doing it all at once (I don't see any reason why you would) you may use mysqli::multi_query().

Upvotes: 1

Related Questions