MySQL error

Yay! Im back with another mysql error… this is probaly a silly mistake but heres my error (the results table after my function)



1:
                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 'Description, 100, 1 )' at line 1
                errorid =       1064
                status  =       false
                time    =       0.18346509324548


Here my code yes all the vars are active and not nil…



		db:Query("INSERT INTO shipments ( seller, count, weapon, model, description, price, pending ) VALUES ( "..ply:SteamID64()..", "..count..", "..class..", "..model..", "..desc..", "..price..", 1 )", function(result)
			PrintTable(result)
		end)


if i type this:



		print("INSERT INTO shipments ( seller, count, weapon, model, description, price, pending ) VALUES ( "..ply:SteamID64()..", "..count..", "..class..", "..model..", "..desc..", "..price..", 1 )")


it returns this:



INSERT INTO shipments ( seller, count, weapon, model, description, price, pending ) VALUES ( 76561198141863800, 10, weapon_ak472, models/weapons/w_rif_ak47.mdl, Short Description, 100, 1 )


im using tmysql

Don’t you need to quote your string fields? You’re trying to insert strings without quoting them.

You could do something with string.format like the following:



local query = ("INSERT INTO shipments ( seller, count, weapon, model, description, price, pending ) VALUES ( %q, %d, %q, %q, %q, %d, 1 )"):format( ply:SteamID64(), count, class, model, desc, price )

db:Query( query, function(result)
	PrintTable(result)
end)


Basically %q automatically quotes the replacement text, and %d is for your integers (assuming you’re not going to be using floats). It really helps with readability rather than doing “”…var…""…var…""…var, etc, and handles quoting your strings for you.
I’m assuming the weapon, model, and description are all treated as VARCHARs. If seller is a BIGINT, I’m fairly sure mysql will automatically convert the quoted 64-bit value into a BIGINT (never done it myself).

Simpler example:


> lua_run print( ("Integer: %d   Quoted text: %q"):format( 123, "Hey look at me!" ) )
Integer: 123   Quoted text: "Hey look at me!"


Everything after values need to have ’ ’ around them or at least that’s what fixed this error for me.



db:Query("INSERT INTO shipments ( seller, count, weapon, model, description, price, pending ) VALUES ( '"..ply:SteamID64().."', '"..count.."', '"..class.."', '"..model.."', '"..desc.."', '"..price.."', '1' )", function(result)
			PrintTable(result)
		end)


Why would you put quotes around numeric fields? You only need them around string fields (VARCHAR, CHAR, TEXT, etc).

Hey Mexican - only strings need to have quotes around them, so you got most of your query right except you need to remove the quotes around the number entries. I don’t know if MySQL assumes you want them as numbers if inserting a number string into a int field, but even if it does it’s good practice to structure it correctly.

If its anumber, you can still insert with quotes, it wont harm it.
INSERT INTO test (id, string, number, number_string) VALUES (NULL, ‘strings’, ‘1’, ‘5’);
and
INSERT INTO test (id, string, number, number_string) VALUES (NULL, ‘strings’, 1, ‘5’);
works without issue. Even if the column is int.
Try it on a mysql database

Ye, I thought MySQL would handle it OK. But for general good practice it’s best not quote integers.

I know that phpmyadmin always does it with ints. Not sure why they chose it but they probs have a good reason

Laziness?

That’s a valid reason :vs:

Sorry, what do you mean they do it with ints? The changing of a string int to a int int would be MySQL - as PHPMyAdmin is just a interface for working with MySQL.

Probably the reason why MySQL do it is because they realised that a lot of people were making that mistake :stuck_out_tongue:

Even if there’s support for it however you should always use the correct syntax, otherwise if you wanted cross-database support, the other one may not support it ( it’s bad enough with the slight differences with queries anyway! ). As well as WANTING to write well and correctly structured queries!

everything is varchars BUT the id and thats auto inc…

this is now my code and what it means (i printed the string so you know what the vars return)



CODE: 

		db:Query("INSERT INTO shipments ( seller, count, weapon, model, description, price, pending ) VALUES ( "..ply:SteamID64()..", "..count..", "..class..", "..model..", "..desc..", "..price..", 1 )", function(result)
			PrintTable(result)
			print("INSERT INTO shipments ( 'id', 'seller', 'count', 'weapon', 'model', 'description', 'price', 'pending') VALUES ( 'NULL', '"..ply:SteamID64().."', '"..count.."', '"..class.."', '"..model.."', '"..desc.."', '"..price.."', '1' )")
		end)

RETURNS:

1:
                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 'Description, 100, 1 )' at line 1
                errorid =       1064
                status  =       false
                time    =       0.16581077331896
INSERT INTO shipments ( 'id', 'seller', 'count', 'weapon', 'model', 'description', 'price', 'pending') VALUES ( 'NULL', '76561198141863800', '10', 'weapon_ak472', 'models/weapons/w_rif_ak47.mdl', 'Short Description', '100', '1' )



Thanks for the help!

[editline]25th April 2016[/editline]

Ah i solved it i used this code:



		local query = ("INSERT INTO shipments ( seller, count, weapon, model, description, price, pending ) VALUES ( %q, %d, %q, %q, %q, %d, 1 )"):format( ply:SteamID64(), count, class, model, desc, price )

		db:Query( query, function(result)
			PrintTable(result)
		end)


Thanks to Mista Tea! Thanks everyone for the help!