No data in Mysql db

[Xallow]

Hi guys and gals

I am working on a little project and have run into a snag... All my tables are working fine except 1, I simply cannot get any data into it via my script....

$Nickname = $_REQUEST["Nickname"];
$Realname = $_REQUEST["Realname"];
$Email = $_REQUEST["Email"];
$ICQ = $_REQUEST["ICQ"];
$AIM = $_REQUEST["AIM"];
$Skype = $_REQUEST["Skype"];
$Notes = $_REQUEST["Notes"];
$Force = $_REQUEST["Force"];

mysql_connect("localhost","XXXXX","XXXXX") or die(mysql_error());
mysql_select_db("webmasters") or die(mysql_error());

mysql_query("INSERT INTO Webms(Nick,Real,Email,ICQ,AIM,Skype,Notes,Force)
values('$Nickname','$Realname','$Email','$ICQ','$AIM','$Skype','$Notes','$Force')");


I have tested that I actually do get the data from the form, so that isn't the problem. I have double checked that the table name (and DB name) are correct and they are... The column names are correct aswell... There is 1 more column in the table called ID which is INT and auto_increment. All the other columns are just plain text.

If anybody can tell me what the #¤/&#¤ is wrong here I will be a very happy camper.

Thanks
Xallow

[cd34]

after the insert do:

print mysql_error();

If there is an error on the insert, this will at least tell you what it is.

Based on your code, if your data contains an apostrophe in the data ('), it will also crash. If your code isn't doing any validation of the values, your application will be susceptible to SQL injection. Doing it on this insert might be somewhat benign, but, if they hit the right combination, you could suffer some data loss or worse, data that should be private will be exposed.

[Xallow]

Here is the error message I get... Notice that only 7 columns are shown but 8 values to insert... The first column should be Nick... I tried removing the Nick and the value from the mysql but it didn't fix it.

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 'Real, Email, ICQ, AIM, Skype, Notes, Force) values(1,2,3,4,5,6,7,No)' at line 1

[MadCat]

Oh... wait...

Try using `Real` instead of Real.

REAL is a MySQL datatype, hence a reserved word, so it gets seriously peevy when you try and use it as a column name. Sticking backticks around it should fix that issue.

[Xallow]

Apparently Force is a reserved word too because when I changed Real it got a bit further and then when I changed Force also it worked.... Thanks for the help guys, much appreciated.

[MadCat]

One word of advice I can give as far as database schema design goes, try picking column names that can't be mistaken for a reserved word.

Avoid the nonsensical 'strName' or 'intId' shit that gets taught in some schools, people teaching that sort of naming practices are retarded.

An example for a webmaster-style table would be:

CREATE TABLE webmaster (
  webmaster_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nickname VARCHAR(64) NOT NULL,
  email VARCHAR(128) NOT NULL,
  icq VARCHAR(32) NOT NULL,
  aim VARCHAR(32) NOT NULL,
  skype varchar(32) NOT NULL,
  notes TEXT NOT NULL,
  forcing BOOLEAN NOT NULL,
  UNIQUE(email),
);

Using "not null" everywhere does mean that if someone doesn't enter an ICQ address that you must replace it with something such as "no icq" before doing the insert. Avoiding NULL in your tables is a good thing(tm).

Also for columns that hold some sort of flag that indicates an action (I guess in this case you are forcing something on that webmaster), try using it as I did, don't say "force", say "forcing", or heck, "is_forcing", or "is_forcing_trade".

The extra readability you get v.s. the amount of extra typing you have to do is worth it though