MySQL syntax error inserting php strings into table

Question

I'm parsing a website's table with QueryPath and trying to put my results into a MySQL database. The table looks like this:

mysql_query("CREATE TABLE Airplanes (
    flightID VARCHAR( 50 ) PRIMARY KEY NOT NULL, 
    flightLink TEXT( 20000 ) NOT NULL,
    orig TEXT( 20 )  NOT  NULL,
    dest VARCHAR( 20 )  NOT  NULL ,
    time VARCHAR( 5 )  NOT  NULL
);
");

I was trying to save airplanes using their flight numbers as IDs.

This is how I extract the table and the echos for shoving the variables' contents.

        $flightData = $row->find('td');
        // $flightID = str_replace(" ", "", $flightData->eq(1)->text());
        $flightID = mysql_real_escape_string( trim( $flightData->eq(1)->text() ) );
        $flightLink = mysql_real_escape_string( $flightData->eq(1)->html() );
        $orig = mysql_real_escape_string( "ROME (FCO)" );
        $dest = mysql_real_escape_string( trim( $flightData->eq(2)->text() ) );
        $time = mysql_real_escape_string( trim( $flightData->eq(4)->text() ) );

        echo '$flightID: ';
        echo var_dump($flightID)."<br>";
        echo '$orig: ';
        echo var_dump($orig)."<br>";
        echo '$dest: ';
        echo var_dump($dest)."<br>";
        echo '$time: ';
        echo var_dump($time)."<br>";

Didn't ask to echo $flightLink, that would have been pretty long. This is the output on the variables:

$flightID: string(7) "JN 5215"
$orig: string(10) "ROME (FCO)"
$dest: string(14) "TEL AVIV (TLV)"
$time: string(5) "23:45"

This is my SQL-query:

        $insertQuery = mysql_query("INSERT INTO Airplanes (flightID, flightLink, orig, dest, time) VALUES( '$flightID', '$flightLink', '$orig', '$dest', '$time' ) ON DUPLICATE KEY UPDATE;");
        if($insertQuery == false) die("Problem inserting flight data into table. ".mysql_error($connection));

And this is the error message I get on the input query:

Problem inserting flight data into table. 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 '' at line 1

I've seen loads of other guys having trouble feeding MySQL with strings, almost all of them failed on quotation marks, so I bet it's gonna be something about that. Still couldn't find it though. Also grateful for feedback on improving the MySQL-table, just getting into this and not too sure about the data types.

The mysql_* family of functions are deprecated as of PHP 5.50. You should use the new mysqli_* family of functions or PDO. — hyde
– hyde, Commented Aug 28, 2013 at 23:04
Have you tried looking at the correct syntax for your query? dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — hyde
– hyde, Commented Aug 28, 2013 at 23:08
@NullGeo - No he "should" not! He may eventually in the future consider refactoring his code. Perhaps he will be forced to it, if or perhaps when mysql_* eventually is removed completely from PHP. — davidkonrad
– davidkonrad, Commented Aug 28, 2013 at 23:12
near '' at line 1 looks highly suspiscous. Appearently no SQL is executed at all .. — davidkonrad
– davidkonrad, Commented Aug 28, 2013 at 23:15

peterm · Accepted Answer · 2013-08-29 03:09:27Z

1

Your INSERT ... ON DUPLICATE KEY UPDATE syntax is invalid because you have to tell what columns you want to update when a duplicate is encountered

INSERT INTO Airplanes (flightID, flightLink, orig, dest, time) 
VALUES( '$flightID', '$flightLink', '$orig', '$dest', '$time' ) 
ON DUPLICATE KEY UPDATE
                       ^^^^ missing part of ON DUPLICATE clause

It should be something like

INSERT INTO Airplanes (flightID, flightLink, orig, dest, time) 
VALUES( '$flightID', '$flightLink', '$orig', '$dest', '$time' ) 
ON DUPLICATE KEY UPDATE flightLink = VALUES(flightLink), 
                              orig = VALUES(orig),
                              dest = VALUES(dest),
                              time = VALUES(time)

edited Aug 29, 2013 at 3:09

answered Aug 28, 2013 at 23:21

peterm

93k16 gold badges157 silver badges162 bronze badges

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

Collectives™ on Stack Overflow

MySQL syntax error inserting php strings into table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related