apacheguy Posted December 1, 2013 Share Posted December 1, 2013 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted December 1, 2013 Share Posted December 1, 2013 (edited) the date variable needs to be in single quotes ( a string value) ... VALUES ('$date', $miles, $battery_level, 1) Edited December 1, 2013 by Barand Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted December 1, 2013 Share Posted December 1, 2013 Your query values need to be in quotes mysqli_query($con,"INSERT INTO battery (Date, Range, Percent, Sleep) VALUES ('$date', '$miles', $battery_level, 1)"); Quote Link to comment Share on other sites More sharing options...
Barand Posted December 1, 2013 Share Posted December 1, 2013 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) Quote Link to comment Share on other sites More sharing options...
apacheguy Posted December 1, 2013 Author Share Posted December 1, 2013 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 Quote Link to comment Share on other sites More sharing options...
Solution Ch0cu3r Posted December 1, 2013 Solution Share Posted December 1, 2013 Try adding the Date and Range column in backticks `Date`, `Range` These are reserved keywords. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 1, 2013 Share Posted December 1, 2013 "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 Quote Link to comment Share on other sites More sharing options...
aysiu Posted December 1, 2013 Share Posted December 1, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted December 1, 2013 Share Posted December 1, 2013 ...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 Quote Link to comment Share on other sites More sharing options...
aysiu Posted December 1, 2013 Share Posted December 1, 2013 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 1, 2013 Share Posted December 1, 2013 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. Quote Link to comment Share on other sites More sharing options...
apacheguy Posted December 1, 2013 Author Share Posted December 1, 2013 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.