SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    British Columbia, Canada
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't get an integer field to accept data

    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).

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    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

    Code:
    INSERT INTO ckcNumber (my_column_name) VALUES ($judgeCKCnumber)
    or (MySQL only IIRC)

    Code:
    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!
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    British Columbia, Canada
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    British Columbia, Canada
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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());
    }
    }

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    I'd do it like this:

    PHP Code:
    $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.

    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    British Columbia, Canada
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    British Columbia, Canada
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    British Columbia, Canada
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    British Columbia, Canada
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    LOL, we've all been there Helen, no problem
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •