Weird behavior PHP vs MySQL

Question

This is the SQL:

TRUNCATE TABLE `dc_path`;
INSERT INTO dc_path (coords) VALUES('(40.64406436923055, -8.638539251709062)');
INSERT INTO dc_path (coords) VALUES('(40.62791121610622, -8.615193304443437)');
INSERT INTO dc_path (coords) VALUES('(40.62895347295352, -8.6625718444825)');

If I try to execute that query on phpmyadmin it works just fine, but through php it gives me this 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 dc_path (coords) VALUES('(40.64406436923055, -8.638539251709062)');I' at line 1

I tried many things and I can't work it out!

Thanks in advance.

Edit:

PHP

function insertPath($coords){
    $conn = connectDB();
    $coords = explode(";",$coords);
    $sql = "";

    $sql = "TRUNCATE TABLE `dc_path`; ";

    for($i=0;$i<count($coords)-1;$i++){
        $sql .= "INSERT INTO dc_path (coords) VALUES('".$coords[$i]."');";
    }
    echo $sql;
    $query = mysql_query($sql, $conn) or die(mysql_error());

    closeDB($conn);
    return true;
}

the $coords variable contains something like these values:

(40.638854101691635, -8.6515855163575);(40.629474595277166, -8.63235944213875);

You can only execute one query at a time. `mysql_query() sends a unique query (multiple queries are not supported)`. — Basti, Apr 16 '12 at 22:28
seems odd that values would have '' around them usually coordinates are stored as number aren't they? what's the structure of dc_path? first field a text varchar(255?) or something? — xQbert, Apr 16 '12 at 22:30
Why don't you use the `implode()` function to insert multiple rows with a single INSERT statement? [insert multiple rows via a php array into mysql](http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql). No need to iterate through a loop. — Lion, Apr 16 '12 at 22:37
yes, that's a good one too, but for what I need, the answer I accepted works out for me:) Thanks! — silentw, Apr 16 '12 at 22:39

score 4 · Answer 1 · answered Apr 16 '12 at 22:28

4

You cannot perform several queries in one mysql_query() call.

So split that string to 4 separated queries (without ; in the end) and everything will work

answered Apr 16 '12 at 22:28

zerkms

249,484
69
436
539

score 3 · Answer 2 · answered Apr 16 '12 at 22:28

3

Don't use the old mysql_connect API, use mysqli - which supports multiple statements in one.

Read more about the different PHP - mySQL apis here: http://www.php.net/manual/en/mysqlinfo.api.choosing.php

There it says that the old mysql API is not recommended for new projects, and that long term deprecation has been announced.

answered Apr 16 '12 at 22:28

Simon Forsberg

13,086
10
64
108

1

I'll use it in my next big project, didn't knew about that! Thanks for the info! :) – silentw Apr 16 '12 at 22:35

score 3 · Answer 3 · answered Apr 16 '12 at 22:29

3

What function are you using to run this? If you're using mysql_query then you can only do one query at a time, however you can merge the insert statements into one like

INSERT INTO dc_path (coords) VALUES
('(40.64406436923055, -8.638539251709062)'),
('(40.62791121610622, -8.615193304443437)'),
('(40.62895347295352, -8.6625718444825)');

answered Apr 16 '12 at 22:29

Geekfish

2,173
23
28

I dont think this has anything to do with why the OP's code is not working. – Starx Apr 16 '12 at 22:33
then I suggest you read the php manual on the mysql_query. It supports ONE query at a time. Otherwise it might be unicorns causing it not to work. – Geekfish Apr 16 '12 at 22:35
1

@Starx: but it is still a great advice that even deserves to be checked. – zerkms Apr 16 '12 at 22:36
Additionally when I posted my answer the poster had not included his php code. I made the assumption he used mysql_query, no idea about the code around those queries. – Geekfish Apr 16 '12 at 22:37

score 2 · Accepted Answer · answered Apr 16 '12 at 22:31

function insertPath($coords){
    $conn = connectDB();
    $coords = explode(";",$coords);

    mysql_query("TRUNCATE TABLE `dc_path`", $conn);

    for($i=0;$i<count($coords)-1;$i++){
        mysql_query("INSERT INTO dc_path (coords) VALUES('".$coords[$i]."')", $conn);
    }

    closeDB($conn);
    return true;
}

score 1 · Answer 5 · answered Apr 16 '12 at 22:35

1

You cannot query more than one statement using mysql_query().

Query like this

for($i=0;$i<count($coords)-1;$i++){
    $sql = "INSERT INTO dc_path (coords) VALUES('".$coords[$i]."');";
    $query = mysql_query($sql, $conn) or die(mysql_error());
}

answered Apr 16 '12 at 22:35

Starx

77,474
47
185
261

Weird behavior PHP vs MySQL

5 Answers5