current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
The mysql_* family of functions are deprecated as of PHP 5.50. You should use the new mysqli_* family of functions or PDO. –  hyde Aug 28 '13 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 Aug 28 '13 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 Aug 28 '13 at 23:12
    
near '' at line 1 looks highly suspiscous. Appearently no SQL is executed at all .. –  davidkonrad Aug 28 '13 at 23:15
    
@davidkonrad - stackoverflow.com/questions/13944956/… –  hyde Aug 29 '13 at 0:01

1 Answer 1

up vote 2 down vote accepted

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.