www.webdeveloper.com
Results 1 to 12 of 12

Thread: Inserting ID with data from form

  1. #1
    Join Date
    Jan 2006
    Location
    Stafford, England
    Posts
    565

    Inserting ID with data from form

    Hi all

    I have a web form which submits data to a database and works great, but I also want each record to have an id.

    I tried adding an auto increment, id to the database through PHP admin but of course when I then try submitting data using the form it dont work nomore. Not sure what I am doing wrong works fine till I try it with the id part added to both the database and form script.

    Need some advise on how to go about adding an id to each entery made to the database.

    I tried adding the id to the database using this method when creating the table.

    Code:
    CREATE TABLE data (
    id int NOT NULL AUTO_INCREMENT,
    bike VARCHAR( 30 ) ,
    price VARCHAR( 30 ) ,
    location VARCHAR( 30 ) ,
    details VARCHAR( 90 ) ,
    database_filename VARCHAR( 90 ) ,
    PRIMARY KEY (id)
    )
    I also added the id to the Insert part of my form script like below.
    PHP Code:
    mysql_select_db("johnmev_test") or die(mysql_error()); 
    mysql_query("INSERT INTO `data` (id, bike, price, location, database_filename) VALUES ('NULL', '$bike', '$price', '$location', '$details', '$database_filename')"); 

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,388
    In the insert query, just remove the id from the column list and the 'NULL' from the values list. (I think it would also work if you removed the quotes around 'NULL', since you want to use the keyword and not the literal string, but why bother?)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jan 2006
    Location
    Stafford, England
    Posts
    565
    Mmmmmm I just tried both of them ideas but still did not want to add anything to the database on either attempts.

    I dont get it at all, everything I am reading about this sounds like I was doing it right but yet it wont work wenever I have the AUTO INCREMENT id Field for some reason, so I should not have to even mention the id in the web form script?
    Last edited by johneva; 05-20-2009 at 11:40 AM.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,388
    Have you tried checking to see if the result of your mysql_query() call is false, and if so echoing out mysql_error() to see what it has to say?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Jan 2006
    Location
    Stafford, England
    Posts
    565
    Erm not even sure what you asking me there, I am like big time noob at PHP and SQL an know vertually nothing.

    All I know is wen I make a table with the id included it dont work wen I make the table with the id it works fine. I tried it with the extra code in the insert part of the form and without, both of which dont work.

  6. #6
    Join Date
    Nov 2008
    Posts
    2,477
    Quote Originally Posted by johneva View Post
    ...so I should not have to even mention the id in the web form script?
    No, you don't need to. Whenever you INSERT data, any fields which you don't specify values for will get assigned their default value (or NULL if no default is specified). In the case of an AUTO_INCREMENT field, the default value is always the next number in the sequence, so you never need to specify it when INSERTing.

    As an example, if I had a table called 'users' with the following fields:

    Code:
    id INT(10) AUTO_INCREMENT
    name VARCHAR(100)
    dob INT(11)
    and I wanted to add a new user, my query would just look like:

    Code:
    INSERT INTO `users` ('name', 'dob') VALUES ('Mindzai', 1102933822);
    The id field will take care of itself.

    If you are doing what I describe already, then the next thing to do is try what NogDog suggested and check for MySQL errors.

  7. #7
    Join Date
    Nov 2008
    Posts
    2,477
    Quote Originally Posted by johneva View Post
    Erm not even sure what you asking me there, I am like big time noob at PHP and SQL an know vertually nothing.

    All I know is wen I make a table with the id included it dont work wen I make the table with the id it works fine. I tried it with the extra code in the insert part of the form and without, both of which dont work.
    Add the following and let us know if anything is output:

    PHP Code:
    $result mysql_query("INSERT INTO `data` (bike, price, location, database_filename) VALUES ('$bike', '$price', '$location',  '$database_filename')"); 
    if (!
    $result) {
        echo 
    "ERROR: " mysql_error();
    } else {
        echo 
    "IT WORKED!";

    Also note in your original SQL you specify 5 field names but 6 values - this is invalid. You must provide exactly 1 value per field as in the example above.

  8. #8
    Join Date
    Jan 2006
    Location
    Stafford, England
    Posts
    565
    Ah, well erm not sure what I have done but it seems to be working now. I must have made a mistake somewhere an managed to fix it somehow not really sure how but ahwell.

    Cheers guys for your time.

  9. #9
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,388
    No, no, no!

    You're supposed to say, "Thanks for the debugging tips. I found the problem (a simple typo) and fixed it."

    (From my book, You Can Be a BS Artist, Too)

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  10. #10
    Join Date
    Jan 2006
    Location
    Stafford, England
    Posts
    565
    Erm thanks for the debugging tips. Still not got a clue where I balls up but its working now lol.

  11. #11
    Join Date
    May 2009
    Location
    East Indies
    Posts
    11
    Quote Originally Posted by johneva View Post
    Hi all

    I have a web form which submits data to a database and works great, but I also want each record to have an id.

    I tried adding an auto increment, id to the database through PHP admin but of course when I then try submitting data using the form it dont work nomore. Not sure what I am doing wrong works fine till I try it with the id part added to both the database and form script.

    Need some advise on how to go about adding an id to each entery made to the database.

    I tried adding the id to the database using this method when creating the table.

    Code:
    CREATE TABLE data (
    id int NOT NULL AUTO_INCREMENT,
    bike VARCHAR( 30 ) ,
    price VARCHAR( 30 ) ,
    location VARCHAR( 30 ) ,
    details VARCHAR( 90 ) ,
    database_filename VARCHAR( 90 ) ,
    PRIMARY KEY (id)
    )
    I also added the id to the Insert part of my form script like below.
    PHP Code:
    mysql_select_db("johnmev_test") or die(mysql_error()); 
    mysql_query("INSERT INTO `data` (id, bike, price, location, database_filename) VALUES ('NULL', '$bike', '$price', '$location', '$details', '$database_filename')"); 
    Remove word NULL then leave them as blank. Another remove apostrophe in table name because it won't insert the data from frond end to back end.

    Try this code:

    PHP Code:
    mysql_select_db("johnmev_test") or die(mysql_error()); 
    mysql_query("INSERT INTO [B]data[/B] (id, bike, price, location, database_filename) VALUES ([B]' '[/B], '$bike', '$price', '$location', '$details', '$database_filename')"); 
    [/QUOTE]

  12. #12
    Join Date
    Nov 2008
    Posts
    2,477
    You can't insert a space character (string) into an INT field. Also your example has the smae problem as the original post in that it is inserting into 5 fields but providing 6 items of data.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles