www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: PHP MySQL insert statement not working.

  1. #1
    Join Date
    Oct 2013
    Posts
    26

    PHP MySQL insert statement not working.

    ok here is the problem code
    the output here is the error message i have programmed for when the query doesn't work, i know all of my fields are getting posted and collected by the variables but it doesn't insert the fields, i can't seem to track down the problem here.

    PHP Code:
    if (isset($_POST['first_name'])
        && isset(
    $_POST['last_Name'])
        && isset(
    $_POST['user_email'])
        && isset(
    $_POST['mailing_address'])
        && isset(
    $_POST['user_name']) 
        && isset(
    $_POST['user_pass']) 
        
    ) {
        
    $first_name get_post('first_name');
        
    $last_Name get_post('last_Name');
        
    $user_name get_post('user_name');
        
    $user_pass get_post('user_pass');
        
    $user_email get_post('user_email');
        
    $mailing_address get_post('mailing_address');

        
    $query "INSERT INTO users VALUES" "('','$first_name', '$last_Name', '$user_email', '$mailing_address', '$user_name', '$user_pass')";

        if (!
    mysql_query("INSERT INTO users VALUES" "('','$first_name', '$last_Name', '$user_email', '$mailing_address', '$user_name', '$user_pass')"$db))
            echo 
    "INSERT failed: $query<br />" mysql_error() . "<br /><br />";


  2. #2
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    868
    Hmmm.....

    You assigned a query statement to a variable, but then you executed a call to MySQL_query with an inline query statement. Odd.

    Anyway - what message did MySQL_error give you in that echo line?

    BTW- You do know that MySQL_* is all deprecated and you should begin migrating to either mysqli or pdo for your db work.

  3. #3
    Join Date
    Oct 2013
    Posts
    26
    i assigned the query to a variable to begin with but i read online that the mysql_query statement sometimes does not work when passing a variable rather than the inline statement, i changed it to see if that would fix my issue.

    the error message i get is the message i created here without any specific SQL error appended to the end of it.

    maybe replacing the deprecated functions will make this work i will try that now thanks.

  4. #4
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    868
    I'm curious where you saw anything about an inline query working better than a variable. Crazy!

    I don't understand your second sentence at all.

    Turn on error checking. Add this:
    PHP Code:
        error_reporting(E_ALL E_STRICT E_NOTICE);
        
    ini_set('display_errors''1'); 

    Plus - you shouldn't use $query in your error statement when you didn't actually run that query. Change back to using the variable in your query.

  5. #5
    Join Date
    Oct 2013
    Posts
    26
    ok i have never used error checking before where do i add this?

  6. #6
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    868
    At the beginning of every script that you are still developing. You don't want to display errors once you go into production.

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    If using the (deprecated) mysql PHP extension, you should sanitize all non-numeric inputs via mysql_real_escape_string().
    PHP Code:
    $query sprintf(
        
    "INSERT INTO users VALUES" "('', '%s', '%s', '%s', '%s', '%s', '%s')"
        
    mysql_real_escape_string($first_name),
        
    mysql_real_escape_string($last_Name),
        
    mysql_real_escape_string($user_email),
        
    mysql_real_escape_string($mailing_address),
        
    mysql_real_escape_string($user_name),
        
    mysql_real_escape_string($user_pass)
    );
    if(
    mysql_query($query) == false) {
        throw new 
    Exception(mysql_error().PHP_EOL.$query);

    Also, my suspicion is that the first value should be NULL instead of '', assuming it's an auto-numbered primary key? (Actually, if that's the case, you could provide a list of columns that does not include that field, then you don't have to have it in your VALUES() clause; besides which using an explicit field list avoids nasty bugs when your database table is altered in any way that changes the default field order.
    "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

  8. #8
    Join Date
    Oct 2013
    Posts
    26
    OK i have it now working, but its all using the now deprecated mysql_* is there a tutorial or something anyone can share that will help with converting over to PDO? i tried using mysqli_ but for some reason my site was not allowed access to the site while using the improved version. giving me this message.

    Database access failed: Access denied for user ''@'localhost' (using password: NO)

    ive looked all over the internet for a fix to this but all i get is people that had a similar problem and couldn't find a fix.

  9. #9
    Join Date
    Oct 2013
    Posts
    26
    can i get the same effect writing it like this?

    PHP Code:
    require_once 'connect.php';
    $result mysql_query("SELECT * FROM users");
    $rows mysql_num_rows($result);
    if (!
    $result) die ("Database access failed: " mysql_error());


    if (isset(
    $_POST['first_name'])
        && isset(
    $_POST['last_Name'])
        && isset(
    $_POST['user_email'])
        && isset(
    $_POST['mailing_address'])
        && isset(
    $_POST['user_name']) 
        && isset(
    $_POST['user_pass']) 
        && isset(
    $_POST['user_pass_check'])
    ) {
        
    $first_name mysql_real_escape_string(get_post('first_name'));
        
    $last_Name mysql_real_escape_string(get_post('last_Name'));
        
    $user_name mysql_real_escape_string(get_post('user_name'));
        
    $user_pass mysql_real_escape_string(get_post('user_pass'));
        
    $user_pass_check mysql_real_escape_string(get_post('user_pass_check'));
        
    $user_email mysql_real_escape_string(get_post('user_email'));
        
    $mailing_address mysql_real_escape_string(get_post('mailing_address'));
        
    $user_ID $rows+1;

        
    $query "INSERT INTO users VALUES" "('$user_ID','$first_name', '$last_Name', '$user_email', '$mailing_address', '$user_name', '$user_pass')";

        if (!
    mysql_query($query$db))
            echo 
    "INSERT failed: $query<br />" mysql_error() . "<br /><br />";


  10. #10
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    868
    Does your host support the mysqli extension? You might have to use PDO, as I do since my host doesn't have mysqli.

    As for your code. You wrote it too quickly to read what you wrote.
    PHP Code:
    require_once 'connect.php'
    $result mysql_query("SELECT * FROM users"); 
    $rows mysql_num_rows($result); 
    if (!
    $result) die ("Database access failed: " mysql_error()); 
    1 - Why are you doing a query for all records when the whole point is to do an insert? Are you actually using an id that represents the number of rows at a given time? No Good. What happens if you delete a row?
    2 - You must check the results of the query before trying to use the results!! Your check for num rows will fail if $result is not valid because the query failed, so swap them.
    I don't see the error checking code I provided.

  11. #11
    Join Date
    Oct 2013
    Posts
    26
    Thanks I'm sure that will be immensely helpful.

  12. #12
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,604
    Quote Originally Posted by WyattVW View Post
    OK i have it now working, but its all using the now deprecated mysql_* is there a tutorial or something anyone can share that will help with converting over to PDO? i tried using mysqli_ but for some reason my site was not allowed access to the site while using the improved version. giving me this message.

    Database access failed: Access denied for user ''@'localhost' (using password: NO)

    ive looked all over the internet for a fix to this but all i get is people that had a similar problem and couldn't find a fix.
    Here's something to chew on PDO-wise:
    PHP Code:
    <?php
    $host 
    'localhost';
    $dbName 'my_database';
    $user 'secret';
    $pass 'topSecret';
    $pdo = new PDO("mysql:host=$host;dbname=$dbName"$user$pass);
    $sql "
    INSERT INTO users (
        first_name,
        last_Name,
        user_email,
        mailing_address,
        user_name,
        user_pass
    ) VALUES (
        :first_name,
        :last_Name,
        :user_email,
        :mailing_address,
        :user_name,
        :user_pass
    )
    "
    ;
    if((
    $stmt $pdo->prepare($sql)) == false) {
        throw new 
    Exception(print_r($pdo->errorInfo(),1).PHP_EOL.$sql);
    }
    if(
    $stmt->execute(array(
        
    ':first_name'      => $first_name,
        
    ':last_Name'       => $last_Name,
        
    ':user_email'      => $first_name,
        
    ':mailing_address' => $user_email,
        
    ':user_name'       => $user_name,
        
    ':user_pass'       => $user_pass
    )) == false) {
        throw new 
    Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
    "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

  13. #13
    Join Date
    Oct 2013
    Posts
    26
    1 - Why are you doing a query for all records when the whole point is to do an insert? Are you actually using an id that represents the number of rows at a given time? No Good. What happens if you delete a row?
    2 - You must check the results of the query before trying to use the results!! Your check for num rows will fail if $result is not valid because the query failed, so swap them.
    I don't see the error checking code I provided.
    the $result variable is for checking for the updated fields, they display further down on the page, i just declared it at the top so i did not have to write 2 variables to get the number of rows.

    if this is a bad way of inserting User ID how do you insert a value into a row with an auto increment ID field?

  14. #14
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    868
    You misunderstood what I was saying.

  15. #15
    Join Date
    Oct 2013
    Posts
    26
    Quote Originally Posted by WyattVW View Post
    if this is a bad way of inserting User ID how do you insert a value into a row with an auto increment ID field?
    well i managed to get it auto incrementing the ID field on its own by changing my query statement to this.

    "INSERT INTO users(first_name, last_Name, user_email, mailing_address, user_name, user_pass) VALUES" . "('$first_name', '$last_Name', '$user_email', '$mailing_address', '$user_name', '$user_pass')"

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