Can't get an integer field to accept data

[Helen W Lee]

Does anyone have any idea why I can't get an integer field to accept numbers? I can go into the person's record in the db and type the numbers and they stay but if I try to insert a record into the db using a form, the field stays at 0. I've read tutorials until I'm almost blind but can't get it to work.

In the insert.php file I set a variable: $judgeCKCnumber = (int)$_POST['judgeCKCnumber'];

Then I INSERT into ckcNumber VALUE $judgeCKCnumber

Should that not work? I've tried also putting the (int) in front of the value, I've tried putting intval() around the value, both where I set the variable and in the VALUE part but NO WAY will it insert numbers into the db. I can't remember now but I think I even put the int and/or intval() on the variable when I set it. It's driving me crazy. The only solution I can think of is to change the field from INT to VARCHAR but then I'd have to go in and manually re-insert the #s into 298 records (I'm converting an html file with 298 people into a php/mysql file).

[ScallioXTX]

The query syntax is wrong; you need to reference the column that needs to get that value (even if the table only has one column).

So it would be

INSERT INTO ckcNumber (my_column_name) VALUES ($judgeCKCnumber)

or (MySQL only IIRC)

INSERT INTO ckcNumber SET my_column_name=$judgeCKCnumber

where my_column_name is the name of column you want to store the value in.

Also, make sure to escape that value to prevent SQL injection!

[Helen W Lee]

Thanks ScallioXTX. I forgot to mention that I'm pretty much a newbie at this stuff (in case you couldn't figure that out on your own <LOL>), so the logic escapes me sometimes. The original coding I had for the ckcNumber variable was:

$judgeCKCnumber = mysql_real_escape_string($_POST['judgeCKCnumber']);

but of course that's for strings only. When it dawned on me that I had to change it, I wasn't sure exactly how.

In the database, the actual field name (or I guess column name - I'm an old FMP user) is ckcNumber, so in your example above I would just repeat the field name: INSERT INTO ckcNumber SET ckcNumber=$judgeCKCnumber - is that correct?

Then of course there's the problem that I'm so new I'm not sure how I would escape the value. :>) I do realize that the mysql_real_escape_string does it for strings but what would I use for integers?

[Helen W Lee]

Here's the whole chunk of code I'm working with (minus a bunch of fields to make it shorter) - perhaps this will explain why I'm having trouble figuring out where to put the suggested coding. Note that originally I had the mysql_real_escape_string included with the 3 judgeXXXnumber fields, before it dawned on me that they're not strings:

{
$judgeFirstName = mysql_real_escape_string($_POST['judgeFirstName']);
$judgeLastName = mysql_real_escape_string($_POST['judgeLastName']);
$judgeCountry = mysql_real_escape_string($_POST['judgeCountry']);
$judgeEmail = mysql_real_escape_string($_POST['judgeEmail']);
$judgeCKCnumber = $_POST['judgeCKCnumber'];
$judgeCDJAnumber = $_POST['judgeCDJAnumber'];
$judgeAKCnumber = $_POST['judgeAKCnumber'];

$sql="
INSERT INTO
judgegallery (
firstName
, lastName
, country
, emailaddy
, ckcNumber
, akcNumber
, cdjaNumber
) VALUES (
'". $judgeFirstName ."'
,'". $judgeLastName ."'
,'". $judgeCountry ."'
,'". $judgeEmail ."'
,'". $judgeCKCnumber ."'
,'". $judgeCDJAnumber ."'
,'". $judgeAKCnumber ."'
)";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
}

[ScallioXTX]

I'd do it like this:

$judgeFirstName = mysql_real_escape_string($_POST['judgeFirstName']);
$judgeLastName = mysql_real_escape_string($_POST['judgeLastName']);
$judgeCountry = mysql_real_escape_string($_POST['judgeCountry']);
$judgeEmail = mysql_real_escape_string($_POST['judgeEmail']);
$judgeCKCnumber = mysql_real_escape_string($_POST['judgeCKCnumber']);
$judgeCDJAnumber = mysql_real_escape_string($_POST['judgeCDJAnumber']);
$judgeAKCnumber = mysql_real_escape_string($_POST['judgeAKCnumber']);

$sql=sprintf('
INSERT INTO judgegallery (
    firstName
  , lastName
  , country
  , emailaddy
  , ckcNumber
  , akcNumber
  , cdjaNumber
) VALUES (
  "%s", "%s", "%s", "%s", "%s", "%s", "%s"
)',
  $judgeFirstName,
  $judgeLastName,
  $judgeCountry,
  $judgeEmail,
  $judgeCKCnumber,
  $judgeCDJAnumber,
  $judgeAKCnumber
);

if (!mysql_query($sql,$con))
{
  die('Error: ' . mysql_error());
} 

You also need to apply mysql_real_escape_string on int's. It's to prevent SQL injection, and than can just as easily happen with ints as with strings.

[Helen W Lee]

Thanks ScallioXTX. Including the escape string in the top part puts that bit back to what it was originally, so it looks like the only difference is the line of "%s" in the bottom part. What do those symbols do? Will they make the database accept the integer input from the form? That was my original problem - I type 12345 into the form integer field (along with the name, etc in the other fields), submit the form and when I look at the db the value in the integer field is 0. The field is not accepting the input from the form, even though I can type the 12345 directly into the field in the db. Will the "%s" solve that problem?

[Helen W Lee]

Sorry!! I'm not really awake yet. I've just realized that the whole bottom area is coded differently (without all the quote marks, etc.) I'll give that a shot and see what happens.

[Helen W Lee]

Sigh. That didn't work either. I've been reading the php online manual and other tutorials all morning, have tried a bunch of suggestions (settype, (int), intval, ereg_replace and any number of others) but NONE of them result in anything but 0 in the database, except settype, which puts the boolean 1.

I guess I'm going to have to bite the bullet, make 3 new text fields and copy the data over from the INT fields to the VARCHAR fields and continue on using VARCHAR. I probably don't *need* the fields set as INT since I won't be doing any calculations with them but I figured that since all they would contain would be numbers, INT would be the way to go.

I've spent so much time reading about this that I probably could have replaced the fields 2 or 3 times over by now.

I'm assuming, by the way, that if I simply change the field definition from INT to VARCHAR I'll lose the data in the fields. That's what happens in a regular db, at least.

[Helen W Lee]

I figured out the problem and I'm so fricking ticked at myself I could spit. The problem wasn't in the insert.php file at all. When I decided to bite the bullet and make new fields as VARCHAR, I had to of course go into the form file to enter the new field name(s). That's when I spotted a bloody gd typo in the 3 INT field names!!!! All the time I've spent fighting with the insert.php and it was fine the whole time. Grrrr. Thanks for the help everyone and I do apologize for the unnecessary list noise.

[ScallioXTX]

LOL, we've all been there Helen, no problem