SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot kevinritt's Avatar
    Join Date
    Nov 2002
    Location
    Lynn, Massachusetts-United States
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy PHP to insert data into MySQL database

    Hi,
    I looked through these forums and I followed the recommendations and advice given about inserting data into a MySQL database from a PHP form. However when I tried it, I couldn't get it to work.
    Here's the script:
    Form.php:
    <form action="process.php" method="post" name="maillist">
    <input type="text" name="firstname">
    <input type="text" name="lastname">
    <input type="text" name="email">
    <input type="submit" name="Submit" value="Join">
    <input type="reset" name="reset" value="Clear">
    </form>

    Process.php
    <?php

    $firstname=$_POST['firstname'];
    $lastname=$_POST['lastname'];
    $email=$_POST['email'];

    $dbh=mysql_connect ("localhost", "****", "****") or die ('Cannot connect to Database - error: ' . mysql_error () );
    mysql_select_db("****",$dbh);
    $sql = "INSERT INTO `list` ('id', 'firstname', 'lastname', 'email') VALUES ('', '$firstname', '$lastname', '$email');";
    mysql_query($sql);
    ?>

    "id" is used as an increment

    When the user clicks the "Join" button the data does not go into the database. What am I doing wrong?
    Kevin

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I always found that particular INSERT query structure more difficult to read. I'm not sure if this will help your problem but personally I prefer something like this:
    Code:
     mysql_query("INSERT INTO `list` SET `firstname`='$firstname', `lastname`='$lastname', `email`='$lastname'");
    btw you don't need to worry about the ID at all if it is an auto_increment field.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the problem with the INSERT SET syntax is that you have to unlearn it (and convert your sql) if you ever work with any other database

    on the other hand, INSERT VALUES syntax is standard sql

    your error was that your column names should not be in quotes

    INSERT INTO `list` ( firstname, lastname, email )
    VALUES ( '$firstname', '$lastname', '$email' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot kevinritt's Avatar
    Join Date
    Nov 2002
    Location
    Lynn, Massachusetts-United States
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there something wrong with this syntax?

    $sql = "INSERT INTO `list` ( firstname, lastname, email ) VALUES ( '$firstname', '$lastname', '$email' )";

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, that syntax is most excellent

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot kevinritt's Avatar
    Join Date
    Nov 2002
    Location
    Lynn, Massachusetts-United States
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this and still no input into database

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try running that statement in phpmyadmin or whatever front end program you might have

    i'd be interested in knowing if it produces an error or performs the update

    if it performs the update, then your problem is in the php code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot kevinritt's Avatar
    Join Date
    Nov 2002
    Location
    Lynn, Massachusetts-United States
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll try it and get back to you.
    Thanks

  9. #9
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the problem with the INSERT SET syntax is that you have to unlearn it (and convert your sql) if you ever work with any other database
    Thanks, I wasn't aware that was a MySQL only feature.

  10. #10
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try replacing these 2 lines
    PHP Code:
      $sql "INSERT INTO `list` ('id', 'firstname', 'lastname', 'email') VALUES ('', '$firstname', '$lastname', '$email');"
      
    mysql_query($sql); 
    with

    PHP Code:
      $sql "INSERT INTO `list` ('id', 'firstname', 'lastname', 'email') VALUES ('', '$firstname', '$lastname', '$email');"
     
    echi $sql;
     
    mysql_query($sql) or die('Failed to execute ' $sql ' due to ' mysql_error(); 
    Post the results.

    This will print the actual SQL command and also any MySQL error messages.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    don't put single quotes around the column names

    and for the sake of your own sanity, don't insert an empty string into a numeric column (the auto_increment) because this data type mismatch will backfire and give you a syntax error if you ever try it in any other database

    mysql is far too loose with its syntax for your own good
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kevinritt
    Hi,
    I looked through these forums and I followed the recommendations and advice given about inserting data into a MySQL database from a PHP form. However when I tried it, I couldn't get it to work.
    ...
    When the user clicks the "Join" button the data does not go into the database. What am I doing wrong?
    Kevin
    Hi Kevin -

    Earlier today I was having the exact same problem with my form and database but I just got it all working. Here's the format of the code I am using in my PHP script:


    Code:
    // send Data To A DATABASE
    // Make a MySQL Connection
    
    mysql_connect("db_server_path", "user", "pasword") or die(mysql_error());
    mysql_select_db("databasename") or die(mysql_error());
    
    // Insert a row of information into the table "tablename"
    
    mysql_query("INSERT INTO tablename (name, last_name, street, street2, city, state, zip) VALUES('$name', '$last_name', '$street', '$street2', 
    '$city', '$state', '$zip')") or die(mysql_error());
    This code is part of a PHP script, called from a web form (action) on submit... the script also sends a confirmation email to the user and a notification email to the site administrator... if you would like to see the entire script just let me know.

    Now...

    Can anyone show me how to add PHP VALIDATION CODE to my [b]FORM[b] that will check the text entered into the USERNAME field against the USERNAMEs already in the database and notfiy the user that the name selected is already taken (and refocus to that form field so that they can try again)?

    - Michael

  13. #13
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kevinritt
    Process.php
    <?php

    Code:
    $firstname=$_POST['firstname'];
    $lastname=$_POST['lastname'];
    $email=$_POST['email'];

    I just noticed that Kevin is using $_POST to set the variables, but he's using an external script. POST would work from within the form itself, but will it work in an external script?

    I'm running a nearly identical external script to his but I use:

    Code:
    $varibale = $_REQUEST['variable'] ;
    and it works. Does that make any difference?

    I also just noticed that there are no spaces between Kevin's $string=$_POST... I don't know if that matters or not either.

    - michael


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
  •