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.