MySQL Syntax Error - INSERT INTO

[apacheguy]

I've banged my head enough trying to figure out this problem and have given up.

 

I've inserted data into an SQL DB before without any problems.  Not sure what's going on here.  This is my code:

	$charge = function_read("vehicles/" . $status[0]['id'] . "/command/charge_state");
	$miles = mysqli_real_escape_string($con, $charge['battery_range']);
	$battery_level = mysqli_real_escape_string($con, $charge['battery_level']);
	mysqli_query($con,"INSERT INTO battery (Date, Range, Percent, Sleep) VALUES ($date, $miles, $battery_level, 1)");
	echo $con->error;

This is the error message (completely worthless, IMHO).  Could it be any less descriptive?

 

 

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 'Range, Percent, Sleep) VALUES (2013-12-01 10:15:07, 189.18, 82, 1)' at line 1

 

MySQL table is configured as follows:

 

 

Date

timestamp

CURRENT_TIMESTAMP

 

Range

decimal(5,2)

 

Percent

tinyint(3)

 

Sleep

tinyint(1)

0

 

Any ideas?

[Barand]

the date variable needs to be in single quotes ( a string value)

... VALUES ('$date', $miles, $battery_level, 1)

Edited December 1, 2013 by Barand

[Ch0cu3r]

Your query values need to be in quotes

mysqli_query($con,"INSERT INTO battery (Date, Range, Percent, Sleep) VALUES ('$date', '$miles', $battery_level, 1)");

[Barand]

Forget my last post, and Ch0cUr's for that matter (numeric values should not be quoted)

 

I just noticed your date field is a timstamp so it will automatically set to the current time on insert. No need to include it in your query

 

 

 mysqli_query($con,"INSERT INTO battery ( Range, Percent, Sleep) VALUES ($miles, $battery_level, 1)");

(However, when using date values they should be quoted)

[apacheguy]

Thanks for those replies.  I removed $date from my query since it is already the default value for that column.  No luck.  Same 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 'Range, Percent, Sleep) VALUES (188.85, 81, 1)' at line 1

 

Putting quotes around the values does not help either.  Same error.  Ugh

[Ch0cu3r]

Try adding the Date and Range column in backticks

 

`Date`, `Range`

 

These are reserved keywords.

[Barand]

"Range" is a MySQL reserved word. Date is permitted.

 

 

MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

• ACTION

• BIT

• DATE

• ENUM

• NO

• TEXT

• TIME

• TIMESTAMP

[aysiu]

Does your insert query now look like this?

mysqli_query($con,"INSERT INTO `battery`
(`Range`, `Percent`, `Sleep`)
VALUES
('$miles', '$battery_level', '1')");

Edited December 1, 2013 by aysiu

[Barand]

...because it should now look like

mysqli_query($con,"INSERT INTO battery (`Range`, Percent, Sleep) VALUES ($miles, $battery_level, 1) "); 

`Range` being the only one requiring backticks and numerals not requiring quotes

[aysiu]

Maybe I'm silly, but I kind of like putting in the backticks for all of them, as well as the quotation marks for consistency. Does that hamper the performance of the query? Just curious.

[Barand]

Using backticks for all identifiers isn't wrong, just unnecessary (unless the identifier is a reserved word or contains spaces or other special characters), and IMO makes the query cluttered and not as readable. But I guess that's personal taste.

 

On the other hand, MySQL is very lenient when it comes to structure rules and allows several things that other dialects of SQL would not. The use of field selection and GROUP BY clauses is one of then and the strict typing of variables, writing a string value to a numeric column, is another. It will let you quote numeric valuables but strict SQL syntax says they should be unquoted.

[apacheguy]

Yep, the range column was the problem all along. Thanks!

 

Shouldn't the error message engine be smart enough to have pointed out that I was using a reserved keyword? The lack of description in MySQL errors is frustrating, as there was no hint to what was causing the problem.