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.
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)