Need a second eye to help debug a query

Hello,

The more I looked at this error and my query the less I see the error. I need some help guys to track down this quote or something.

The error:

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

The query:

$query = "INSERT INTO gga_raw_data(device_id, gga_date,  nmea, gga_time, latitude, north, longitude, east, fixed_quality, no_of_satelites, hdop, altitude, meters, height_of_geoid, metres, check_sum) values";
		$gen_query=$query;
		while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
		{
			$numlines++;

			$myDay=substr($data[1], 0,2);
			$myMonth=substr($data[1], 2,2);
			$myYear=substr($data[1], 4,2);

			$gga_date = convertToNiceDate($myDay, $myMonth, 2000+$myYear);
			$longitude = convertToDegrees($data[6]);
			$latitude  = convertToDegrees($data[4]);

			if (!isset($data[15])) {
				$data[15] = '';
			}

			$gen_query .="('$data[0]','$gga_date','$data[2]','$data[3]','$latitude','$data[5]','$longitude','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]', '$data[15]')',";
			if ($numlines % 100 == 0) {
			 mysql_query(substr($gen_query,0,-1));
			 $gen_query=$query;
			}
				

		}

		if ($gen_query!=$query) mysql_query(substr($gen_query,0,-1));
		echo $gen_query;
		mysql_query($query) or die(mysql_error());


Thank you.

This creates queries in the form

INSERT INTO table (col, col) VALUES (val, val), (val, val), (val, val),

I’m guessing MySQL doesn’t like the comma at the end there

That is the reason I used this

$query = substr($query, 0, -1);

But I have removed the comma but nothing happened, I have removed the brackets nothing happened happened. I have executed this same code for other tables and nothing went wrong. This is frustrating.

here’s a hint

echo the complete query string after all the php shenanigans and paste it here, so that we can see the same query that mysql sees

:slight_smile:

The output is massive so I only took a part of it near where the error showed up.

‘‘1’,‘2010,08,21’,’$GPGGA’,‘082520.000’,‘60.15523’,‘N’,‘24.74475’,‘E’,‘1’,‘04’,‘2.2’,‘6.3’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘082526.000’,‘60.15521’,‘N’,‘24.74478’,‘E’,‘1’,‘04’,‘2.2’,‘5.3’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘082532.000’,‘60.1552’,‘N’,‘24.74478’,‘E’,‘1’,‘04’,‘2.2’,‘4.9’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘082538.000’,‘60.15519’,‘N’,‘24.74478’,‘E’,‘1’,‘05’,‘2.8’,‘4.1’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘082544.000’,‘60.15518’,‘N’,‘24.74479’,‘E’,‘1’,‘04’,‘2.1’,‘4.2’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘082550.000’,‘60.15519’,‘N’,‘24.74479’,‘E’,‘1’,‘06’,‘1.4’,‘5.1’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘082556.000’,‘60.1552’,‘N’,‘24.74478’,‘E’,‘1’,‘05’,‘1.8’,‘5.5’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘082602.000’,‘60.1552’,‘N’,‘24.74478’,‘E’,‘1’,‘05’,‘1.8’,‘5.6’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘082608.000’,‘60.1552’,‘N’,‘24.74475’,‘E’,‘1’,‘04’,‘2.5’,‘5.7’,‘M’,‘35.1’,‘M’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174504.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174510.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174516.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174522.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174525.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174536.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174542.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174548.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174554.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174600.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174606.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174612.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174619.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174625.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174631.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174637.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174643.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,08,21’,’$GPGGA’,‘174649.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161603.000’,‘0’,‘’,‘0’,‘’,‘0’,‘’,‘’,‘’,‘’,‘’,‘’, ‘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 ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161616.000’,‘62.31205’,‘N’,‘27.94895’,‘E’,‘1’,‘07’,‘1’ at line 1

those are supposed to be rows of values? where are the parentheses?

i appreciate that the query might be large, but could you show the front part (up to the VALUES keyword) as well as the last few lines

This is the first part

INSERT INTO gga_raw_data(device_id, gga_date, nmea, gga_time, latitude, north, longitude, east, fixed_quality, no_of_satelites, hdop, altitude, meters, height_of_geoid, metres, check_sum) values’1’,‘2010,06,27’,‘$GPGGA’,‘161616.000’,‘62.31205’,‘N’,‘27.94895’,‘E’,‘1’,‘07’,‘1.1’,‘92.0’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161628.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘06’,‘1.9’,‘92.8’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161640.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘05’,‘1.5’,‘92.7’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161652.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘07’,‘1.4’,‘92.4’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161704.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘05’,‘1.9’,‘92.3’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161716.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘04’,‘2.5’,‘92.2’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161728.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘08’,‘1.1’,‘92.4’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161740.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘05’,‘1.9’,‘92.3’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161752.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘06’,‘1.4’,‘92.0’,‘M’,‘19.4’,‘M’, ‘‘1’,‘2010,06,27’,’$GPGGA’,‘161804.000’,‘62.31207’,‘N’,‘27.94895’,‘E’,‘1’,‘06’,‘1.2’,‘92.1’,‘M’,‘19.4’,‘M’,

And the ending part is the last line of output in my previous post. thanks

yup, you’re missing the parentheses

INSERT INTO gga_raw_data(device_id, gga_date, nmea, gga_time, latitude, north, longitude, east, fixed_quality, no_of_satelites, hdop, altitude, meters, height_of_geoid, metres, check_sum)
VALUES
( ‘1’,‘2010,06,27’,‘$GPGGA’,‘161616.000’,‘62.31205’,‘N’,‘27.94895’,‘E’,‘1’,‘07’,‘1.1’,‘92.0’,‘M’,‘19.4’,‘M’ ) ,
( ‘1’,‘2010,06,27’,‘$GPGGA’,‘161628.000’,‘62.31206’,‘N’,‘27.94895’,‘E’,‘1’,‘06’,‘1.9’,‘92.8’,‘M’,‘19.4’,‘M’ ),
( ‘1’,‘2010,06,27’,‘$GPGGA’,…

But those values are obtained from a text file am importing from and I have the following format for the VALUE part of the query:

$query .="('$data[0]','$gga_date','$data[2]','$data[3]','$latitude','$data[5]','$longitude','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]', '$data[15]'),";
			 $query = substr($query, 0, -1);
		

I dont get it.

the data from the text file is like this:

1,070610,$GPGGA,080323.460,6013.45368,N,02445.28396,E,1,04,4.6,18.3,M,35.0,M,*67
1,070610,$GPGGA,080327.000,6013.44424,N,02445.31214,E,1,05,1.9,30.8,M,35.0,M,*66
1,070610,$GPGGA,080336.000,6013.45085,N,02445.36499,E,1,05,1.5,24.4,M,35.0,M,*69
1,070610,$GPGGA,080339.000,6013.45238,N,02445.38714,E,1,05,2.0,23.4,M,35.0,M,*6B
1,070610,$GPGGA,080345.000,6013.45652,N,02445.43034,E,1,05,2.3,23.4,M,35.0,M,*62
1,070610,$GPGGA,080354.000,6013.46590,N,02445.53164,E,1,05,2.3,20.4,M,35.0,M,*6A

which raises the question, why aren’t you using the simple LOAD DATA INFILE command?

a single command, maybe a half dozen options, and boom goes the dynamite, your data is loaded

but if you want to pursue the php problem, i suggest you ask a moderator to move the thread to the php forum (use the little red flag)

I have some php functions which I use to convert some of the values during the importation. I do not think the LOAD DATA INFILE can handle this. Maybe moving the thread to PHP would suffice

okay, i understand conversion

an alternative to your INSERTs (which can be slow) is to write out a file containing the converted data, and then use LOAD DATA on that

Thanks for your suggestion.

I am trying to automate this imprtation of data on a web interface. I have achieved same with other tables using the same solution. The only change I have made is in the table names.

I initially had some holding tables for the conversion and then tranfering them to the permanent tables. This was a two way process that was burdensome.

Whereas your suggestion is useful, it would still leave me with 2 steps instead of one. I have to keep digging. It is strange.

Problem solved. I just discovered that $query = substr($query, 0,-1); was mistakenly placed in the loop so the last comma which was needed for the query to work was being escaped in the loop. It was my error.