SQL error only occurs in PHP script [duplicate]

Question

I'm at odds. My SQL query will only send via PhpMyAdmin. If I attempt to send this specific query via PHP, I get an error. When I copy that exact query into PhpMyAdmin, it goes through without a problem.

INSERT INTO posts (id, content, poster, timestamp, tags) VALUES ('12056242', 'OMG I just got a #toyota', 'Clunker5', '09/12/14 08:43:36', 'toyota');INSERT INTO `tags` (tag, posts) VALUES ('toyota', 1) ON DUPLICATE KEY UPDATE posts=posts+1; UPDATE `tags` SET posts=posts+1 WHERE tag IN ('toyota');

This is the PHP code relevant to the issue

//Ups one post for all tags entered by the user
    if(!empty($tags)){
        $tags1 = explode(",", $tags);
        $tags_submit = join("','", $tags1);
        $tags_insert = join("', 1), ('", $tags1);
        $sql = "INSERT INTO posts (id, content, poster, timestamp, tags) VALUES ('$d', '$b', '".$_SESSION['username']."', '$c', '$tags');"
                . "INSERT INTO `tags` (tag, posts) VALUES ('".$tags_insert."', 1)
                        ON DUPLICATE KEY UPDATE posts=posts+1;
                        UPDATE `tags` SET posts=posts+1 WHERE tag IN ('".$tags_submit."');";

    $result = mysql_query($sql);
    }else{
        $sql = "INSERT INTO posts (id, content, poster, timestamp, tags) VALUES ('$d', '$b', '".$_SESSION['username']."', '$c', '$tags');";

    $result = mysql_query($sql);
    }
    $error = mysql_error();
   if($result){
       echo "1";
   }else{
       echo error($sql, $error, "Tags: ".$tags, "Post: ".$b, "ID: ".$d);
   }

The error is

SQL Response: 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 `tags` (tag, posts) VALUES ('toyota', 1), ('ohmygoodness', 1) ' at line 1.

EDIT: Now that I know that I cannot do a multi-query, how can i do this query?

INSERT INTO `tags` (tag, posts) VALUES ('".$tags_insert."', 1)
                        ON DUPLICATE KEY UPDATE posts=posts+1;
                        UPDATE `tags` SET posts=posts+1 WHERE tag IN ('".$tags_submit."');

What error do you get? Are you checking mysql_error()? Did you make sure your query is correct after the variables are interpolated? — John Conde
– John Conde, Commented Sep 13, 2014 at 0:47
INSERT INTO tags (tag, posts) VALUES ('toyota', 1), ('ohmygoodness', 1) - I've never seen a VALUES clause like that, how would that even work? — David
– David, Commented Sep 13, 2014 at 0:56
If you want to insert multiple rows at one time, you do it like so, INSERT INTO table (var1, var2) VALUES (row1_1, row1_2), (row2_1, row2_2) Try it, it works — ericrabil
– ericrabil, Commented Sep 13, 2014 at 0:57
That query is valid, the problem is you're feeding multiple queries, take the advice of Bill, just query each queries in separate invocations of mysql_query, well you shouldn't use mysql_* anyway, why not use the mysqli_. much much better api or PDO — Kevin
– Kevin, Commented Sep 13, 2014 at 0:59

Bill Karwin · Accepted Answer · 2014-09-13 00:48:25Z

2

You're trying to do multiple statements in one invocation of that API function, but mysql_query() doesn't support multi-query.

You shouldn't use multi-query anyway. You might expose yourself to a whole class of SQL injection vulnerabilties.

You should execute each SQL statement individually, in separate invocations of mysql_query().

Also, the comment from @JohnConde is appropriate: you should always check the return value from mysql_query() because it returns false if there's an error. If that happens, log or report mysql_error() to find out more about what went wrong.

answered Sep 13, 2014 at 0:48

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

Comments

james · Accepted Answer · 2014-09-13 00:47:54Z

1

From the mysql_query documentation:

Multiple queries are not supported.

So, you can't send multiple statements delimited by ;.

answered Sep 13, 2014 at 0:47

james

13.4k9 gold badges50 silver badges74 bronze badges

Comments

unixmiah · Accepted Answer · 2014-09-13 01:01:07Z

I had the same issue today, I had 2 great answers. Every time you have a SQL statement you need to use mysql_query; this is a better clarification for example, if you had

 "INSERT INTO query";
 "INSERT INTO 2nd query";
 mysql_query(process both query "INSERT INTO query";
 this isn't going to work, it'll only work in phpmyadmin or 
 in straight mysql command line sql. not in a php script.

 you'll need to:

 "INSERT INTO query";
 mysql_query(process one query); //you should also use mysql_error() to see the response
 "INSERT INTO 2nd query";
 mysql_query(process 2nd query);

it will need to be formed in 2 separate mysql query. its a security feature on the php side to prevent sql injections. for whatever reason that's what it is.

Collectives™ on Stack Overflow

SQL error only occurs in PHP script [duplicate]

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related