[RESOLVED] SQL DATA TYPES - I MESSED UP

davidmorley

I have a problem with MySQL data types, somehow.

I created a dbt using

$query = "CREATE TABLE IF NOT EXISTS fiklebut (
id SMALLINT NOT NULL AUTO_INCREMENT,
date DATE NOT NULL,
time TIME NOT NULL,
amount VARCHAR(32) NOT NULL,
yourid VARCHAR(32) NOT NULL,
extrainfo VARCHAR(32) NOT NULL,
chargeid VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
)";

Then I tried to write a line of data

$date="2017-12-17";
$time="10:15:34";
$amount="514.27";
$yourid="newton motors";
$extrainfo="cylinder liners";
$chargeid="qwertyuiokl";

$query = "INSERT INTO runner VALUES(NULL, $date, $time, $amount, $yourid, $extrainfo, $chargeid )";

But it says I have got my syntax wrong :

Database access failed: 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 ':15:34, 514.27, newton motors,
cylinder liners, qwertyuiokl )' at line 1

I am not sure why

davidmorley

In the above the last line should read $query = INSERT INTO fiklebut ... Sorry about that.

dalecosp

Strings that contain whitespace should be quoted:

$query = "INSERT INTO runner VALUES(NULL, $date, $time, $amount, '$yourid', '$extrainfo', '$chargeid' )";

May also be issues with the $date and $time strings.

NogDog

Anything that is to be a string literal in the SQL will need single quotes around it, including date/time strings. The only values that won't need quotes, therefore, will be numeric (float or integer) values, or actual SQL keywords/functions, like NULL or NOW().

Of course, if you use PDO with prepared statements and bound parameters, you can use named place-holders in the SQL string, and then let the binding process take care of what needs to be quoted (and escaped!) for you automatically.

davidmorley

Ok This is now resolved. All columns as VARCHAR and, as you say, single quotes round the $amount etc. Thank you again.

Weedpacket

davidmorley wrote:
All columns as VARCHAR and, as you say, single quotes round the $amount etc

amount VARCHAR(32) NOT NULL,

Well, that sounds like a mistake. Isn't the amount some kind of numeric quantity? a DECIMAL one, perhaps?