Reading .txt file into mysql stopped working! Please help

PHP
1

Looking for some help with this please.
The code below worked fine until the website was moved to a new web server.
The file structure is set up exactly the same but the version of php is higher on the new server. The code below is set up so that it reads data from a .txt file into a mysql table. Proble is its now not working and keeps returning an error saying "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 "

Really needing some help with this as I don’t know whats wrong with it. As always any help greatly appreciated, thanks in advance.


$myFile = "data.txt";
$fh = fopen($myFile, 'r');

while(!feof($fh))
{
    $data = fgets($fh);
    list($data1,$data2) = explode('"',$data,2);
    $data2 = str_replace(array("'", '*', '£', 'é'),array('#', '', '£', 'é'),$data2);//replaces comma's with hash mark to insert into db correctly
	//$data2 = str_replace(array("'",'"'),array('*',''),$data2);//replaces apostrophe with star to insert into db correctly
    $data = explode('","',$data1.$data2);

    print_r ($data);
	
//foreach($data as $row){
		 $query = "INSERT INTO Used_Stock ('Feed_Id', 'Vehicle_Id', 'Full_Registration', 'Colour', 'Fuel_Type', 'Year', 'Mileage', 'Body_Type', 'Doors', 'Make', 'Model', 'Variant', 'Engine_Size', 'Price', 'Transmission', 'Picture_Refs', 'Service_History', 'Previous_Owner', 'Category', 'Four_Wheel_Drive', 'Options', 'Comments', 'New', 'Used', 'Site', 'Origin', 'V5', 'Condition', 'ExDemo', 'Franchise_Approved', 'Trade_Price', 'Trade_Price_Extra', 'Service_History_Text', 'Cap_Id', 'Type') VALUES ('".$data[0]."', '".$data[1]."', '".$data[2]."' , '".$data[3]."', '".$data[4]."', '".$data[5]."', '".$data[6]."', '".$data[7]."', '".$data[8]."', '".$data[9]."', '".$data[10]."', '".$data[11]."', '".$data[12]."', '".$data[13]."', '".$data[14]."', '".$data[15]."', '".$data[16]."', '".$data[17]."', '".$data[18]."', '".$data[19]."', '".$data[20]."', '".$data[21]."', '".$data[22]."', '".$data[23]."', '".$data[24]."', '".$data[25]."', '".$data[26]."', '".$data[27]."', '".$data[28]."', '".$data[29]."', '".$data[30]."', '".$data[31]."', '".$data[32]."', '".$data[33]."','2')";
		 mysql_query($query) or die(mysql_error());
 print_r ($row);
//} 
	//echo 'What a Success';
	}
2

Do an echo of $query to see what the query looks like.

And out of curiousity, what is the result of that print_r($data) ?

3

print_r($data) outputs the following:

Array ( [0] => Feed_ID [1] => Vehicle_ID [2] => FullRegistration [3] => Colour [4] => FuelType [5] => Year [6] => Mileage [7] => Bodytype [8] => Doors [9] => Make [10] => Model [11] => Variant [12] => EngineSize [13] => Price [14] => Transmission [15] => PictureRefs [16] => ServiceHistory [17] => PreviousOwners [18] => Description [19] => FourWheelDrive [20] => Options [21] => Comments [22] => New [23] => Used [24] => Internal1 [25] => Internal2 [26] => v5 [27] => Internal3 [28] => ExDemo [29] => FranchiseApproved [30] => Spare1 [31] => Spare2 [32] => Spare3 [33] => BMWApproved" ) 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 '‘Feed_Id’, ‘Vehicle_Id’, ‘Full_Registration’, ‘Colour’, ‘Fuel_Type’, ‘Year’, ‘Mi’ at line 1

I’ve tried to echo $query and all I get is this:

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 '‘Feed_Id’, ‘Vehicle_Id’, ‘Full_Registration’, ‘Colour’, ‘Fuel_Type’, ‘Year’, ‘Mi’ at line 1

any ideas?

thanks for the help with this.

4

Here’s a sample of the data which is in the .txt file that I’m tryingt o import into the db.

“Feed_ID”,“Vehicle_ID”,“FullRegistration”,“Colour”,“FuelType”,“Year”,“Mileage”,“Bodytype”,“Doors”,“Make”,“Model”,“Variant”,“EngineSize”,“Price”,“Transmission”,“PictureRefs”,“ServiceHistory”,“PreviousOwners”,“Description”,“FourWheelDrive”,“Options”,“Comments”,“New”,“Used”,“Internal1”,“Internal2”,“v5”,“Internal3”,“ExDemo”,“FranchiseApproved”,“Spare1”,“Spare2”,“Spare3”,“BMWApproved”
“11102”,“WBABZ12060LZ66625”,“SE08OVN”,“Bright Red”,“Petrol”,“2008”,“28940”,“Roadster”,“2”,“BMW”,“Z4 2.0i”,“Sport Roadster”,“1995”,“11500”,“Manual”,“SE08OVN_1.jpg,SE08OVN_2.jpg,SE08OVN_3.jpg”,“”,“”,“”,“N”,“Brushed Aluminium trim, Composite Star Spoke 108 8/8/1/2 x 18, Leather interior, Nationwide delivery available, Seat heating. front, Remote Control Alarm, ISOFIX Child Seat System, Automatic Air Conditioning, M-Sports Suspension, M Steering Wheel, M Sport Seats”,“”,“N”,“Y”,“”,“”,“”,“”,“”,“Y”,“”,“”,“”,“AUC”
“11102”,“WBADV52040E437304”,“SN11GVU”,“Titanium Silver”,“Petrol”,“2011”,“9700”,“Convertible”,“2”,“BMW”,“320i”,“M Sport Convertible”,“1995”,“29000”,“Manual”,“SN11GVU_1.jpg,SN11GVU_2.jpg,SN11GVU_3.jpg”,“”,“”,“”,“N”,“19’’ light alloy Double-spoke style 313M, Anthracite Bamboo wood. high-gloss, Bluetooth phone prep + telematics, BMW Assist, BMW Assist online portal, BMW pre-paid servicing until March 2013, Metallic paint, Nationwide delivery available, Navigation System Business, Seat heating. front, USB audio interface, Alarm system (Thatcham 1), First Aid Kit & Triangle, Rear-view mirror. auto dimming, ISOFIX Child Seat System, Park Distance Control (PDC). rear, Automatic Air Conditioning, Cruise control, CD changer preparation”,“”,“N”,“Y”,“”,“”,“”,“”,“”,“Y”,“”,“”,“”,“AUC”

5

Remove the single quotes from your field names:

[COLOR=#000000][COLOR=#0000BB]$query [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"INSERT INTO Used_Stock ('Feed_Id', 'Vehicle_Id', 'Full_Registration', 'Colour', 'Fuel_Type', 'Year', 'Mileage', 'Body_Type', 'Doors', 'Make', 'Model', 'Variant', 'Engine_Size', 'Price', 'Transmission', 'Picture_Refs', 'Service_History', 'Previous_Owner', 'Category', 'Four_Wheel_Drive', 'Options', 'Comments', 'New', 'Used', 'Site', 'Origin', 'V5', 'Condition', 'ExDemo', 'Franchise_Approved', 'Trade_Price', 'Trade_Price_Extra', 'Service_History_Text', 'Cap_Id', 'Type')[/COLOR][/COLOR]

$query = "INSERT INTO Used_Stock (Feed_Id, Vehicle_Id, Full_Registration, Colour, Fuel_Type, Year, Mileage, Body_Type, Doors, Make, Model, Variant, Engine_Size, Price, Transmission, Picture_Refs, Service_History, Previous_Owner, Category, Four_Wheel_Drive, Options, Comments, New, Used, Site, Origin, V5, Condition, ExDemo, Franchise_Approved, Trade_Price, Trade_Price_Extra, Service_History_Text, Cap_Id, Type)
6

I’ve removed the single quotes form the field names like frasernm suggested but still getting the error in syntax :frowning: any other suggestions?

7

Is the error in syntax at the same place in the query?

8

Add backticks around the field name Condition - it’s a reserved word in MySQL.

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

9

I get the impression it is trying to store the csv header row (the titles) into the database.

Tell it to ignore row[0] maybe?

10

Yes, you have to echo $query before you execute the query, or add it to the ‘or die()’.

I think you found the solution.

11

Thanks for all the help with this guys, all sorted now! Thanks again!