Jump to content

MySQL Syntax Error - INSERT INTO


apacheguy
Go to solution Solved by Ch0cu3r,

Recommended Posts

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?

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.