Inserting Integer value into mysql int using INSERT

Question

I'm trying to insert a new record in a MySQL database from PHP, which I've done a million times before, but for some reason, I can't get it to work this time, and it really bugs me.

Inserting strings into all the varchar collumns are going great, but when I get to inserting a value into the int column, I get an error telling me that I have a syntax error.

Basically, the first query works just fine, but the second one returns the error, and as you can see, I've made damn sure it really is an integer I'm trying to insert.

I hope somebody can help. I'm really starting to develop a headache over this :/

$groupId2 = 5;
$groupId = (int)$groupId2;
if(!mysqli_query($link, "INSERT INTO contestants (firstName, lastname, email) VALUES ('$firstName', '$lastName', '$email')"))
  echo "First: " . mysqli_error($link);
if(!mysqli_query($link, "INSERT INTO contestants (firstName, lastname, email, group) VALUES ('$firstName', '$lastName', '$email', '$groupId')"))
  echo "Second: " . mysqli_error($link);

If I echo it, I get this: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 'group) VALUES ('', '', '', 5)' at line 2 — Fireworm
– Fireworm, Commented Jul 6, 2012 at 8:27

Musa · Accepted Answer · 2012-07-06 15:28:51Z

8

group is a mysql keyword use back quotes around it

"INSERT INTO contestants (firstName, lastname, email, `group`) 
 VALUES ('$firstName', '$lastName', '$email', '$groupId')"

answered Jul 6, 2012 at 15:28

Musa

97.9k17 gold badges123 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fireworm Over a year ago

Damn, now I feel stupid for not thinking about that. - That you very much for catching it! :)

Fahim Parkar · Accepted Answer · 2012-07-05 22:43:05Z

3

The error is because you surrounded your int with ' ', you need to get rid of your apostrophes and it will work just fine.

if(!mysqli_query($link, 
   "INSERT INTO contestants
   (firstName, lastname, email, group) VALUES
   ('$firstName', '$lastName', '$email', $groupId)"))
                                         ^^^^^^^^^

To clarify, when inserting numerical fields you do not need them.

According to pst this is wrong, although, the fact you do not need single quotes is still correct.

edited Jul 5, 2012 at 22:43

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

answered Jul 5, 2012 at 22:27

Jon Taylor

7,9255 gold badges32 silver badges55 bronze badges

7 Comments

Jon Taylor Over a year ago

@pst if you would like to talk about sql injection then you are free to do so in a comment of your own. However my answer answers the question asked. There is absolutley no reason to downvote the answer.

Jon Taylor Over a year ago

@pst I am completely in favour of teaching those that do not know, however how on earth do you know whether he has dealt with the problem or not previously in his code. For that matter, how do you even know that any of this data is coming from any sort of untrusted source? If he had a $_GET['groupNumber'] or similair in the code, then I probably would mention something, however this is not the case.

user166390 Over a year ago

Also, this answer is wrong. If '1' is treated as a SQL string then it will be coerced to 1. So having 'intValue' will break this no more or less than 'stringValue', of where there are at least 3.

Jon Taylor Over a year ago

Ok so please feel free to answer the question yourself rather than having a go at people like me.

Jon Taylor Over a year ago

Fair enough for downvoting if I got the question wrong but downvoting because I did not mention sql injection is pathetic, especially considering Fireworm has replied saying he dealt with the issue. Also @pst if you are so bothered about it why didn't you end up posting an answer or atleast a comment explaining the issues?

|

Collectives™ on Stack Overflow

Inserting Integer value into mysql int using INSERT

2 Answers 2

1 Comment

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related