www.webdeveloper.com
Results 1 to 13 of 13

Thread: [RESOLVED] PHP keeps inserting twice too many rows in MYSQL database

  1. #1
    Join Date
    Jul 2010
    Posts
    35

    resolved [RESOLVED] PHP keeps inserting twice too many rows in MYSQL database

    Hi, guys.

    I'm working on a section of code that inserts values from an HTML form into a MYSQL database. However, each time I run the code, two rows of identical data are inserted into the database.

    Can anybody help me resolve this problem?

    Here is the code mentioned above. Even running this code by itself, without using the html form first, has the same problem.

    PHP Code:
    <?php

    //Define Variables

    $username="dot_dot";
    $password="q(@Q7SP4L((D";
    $database="dot_thesiteguru";

    $title=$_POST['title'];
    $meta=$_POST['meta'];
    $header_1=$_POST['header_1'];
    $paragraph_1=$_POST['paragraph_1'];
    $header_2=$_POST['header_2'];
    $paragraph_2=$_POST['paragraph_2'];
    $author=$_POST['author'];


    //Connect To the Database Server

    mysql_connect(localhost,$username,$password);


    //Select the Database

    @mysql_select_db($database) or die("Unable to select database");


    //Command the Server to Insert Values

    $query="INSERT INTO webpage VALUES ('','$title','$meta','$header_1','$paragraph_1','$header_2','$paragraph_2','$author')";

    mysql_query($query);

    $id mysql_insert_id();


    //Check if Insertation of Values Was Successful

    if(mysql_query($query)) {
    echo 
    "You have successfully saved a webpage with author $author and title $title.  The ID for this webpage is $id.";
    } else {
    echo 
    "There was a problem processing the information. Please contact us at support[@]thesiteguru.com.";
    }


    //Close the Connection

    mysql_close();

    ?>

  2. #2
    Join Date
    Jan 2009
    Posts
    3,346
    The problem is you are running the query twice:
    if(mysql_query($query)) {
    Use something like:
    Code:
    $result = mysql_query($query);
    
    ...
    
    if(!$result){

  3. #3
    Join Date
    Mar 2010
    Posts
    2,803
    You are running the query in this line

    mysql_query($query);

    and again in this line

    if(mysql_query($query))

    Now, INSERT commands do not return a result set, so I normally check
    how many affected rows there were (mysql_affected_rows()) - for example:



    PHP Code:
    @mysql_query($query$conn);
     
    if(
    mysql_affected_rows($conn) == 1)  {
     
         
    //insert was successful
     
    } else {
     
       
    //insert failed
      


  4. #4
    Join Date
    Nov 2008
    Posts
    2,477
    Quote Originally Posted by tirna View Post
    Now, INSERT commands do not return a result set, so I normally check
    how many affected rows there were (mysql_affected_rows()) - for example:

    PHP Code:
    @mysql_query($query$conn);
     
    if(
    mysql_affected_rows($conn) == 1)  {
     
         
    //insert was successful
     
    } else {
     
       
    //insert failed
      

    Whether INSERT queries generate a resultset or not doesn't matter, because mysql_query does not return a resultset, it returns a resource identifier or false, and a resource identifier will always evaluate true. Criterion's code is perfectly valid (not that there's anything wrong with checking affected_rows either, but I just wanted to clarify as the reasoning for doing so seemed to be confused).
    The first rule of Tautology Club is the first rule of Tautology Club.

  5. #5
    Join Date
    Jul 2010
    Posts
    35

    Ah... Thanks

    Thanks guys.

    I didn't expect to get three comprehensive solutions and explanations in 12 hours!

    criterion9, I shall probably use your excellent solution. I see that instead of saying, "IF query is true," you assign the result of the query to a variable and then ask if THAT is true.

    I didn't realize that a query would run if you put it inside an IF statement!

    tirna and Mindzai, thanks for the information on INSERT and mysql_affected_rows(). That last function is a great tool to know.

    I'm quite new to PHP, (as you've probably noticed by now) so you'll probably be seeing more of me!

    Mr. Baggins

  6. #6
    Join Date
    Jul 2010
    Posts
    35
    (By the way, criterion9, can I give the $result variable its value at the beginning of the entire page of code, or do I need to wait to give it its value until after I run the query?)

  7. #7
    Join Date
    Jan 2009
    Posts
    3,346
    That really depends on your logical flow in the script...give it a shot and post back if you get stuck. I'm sure someone will be able to help you sort it out.

  8. #8
    Join Date
    Jul 2010
    Posts
    35

    Weird. I thought I had it fixed, but...

    I thought that I'd be able to fix it with the information I got. But when I tried it again after fixing it, it still generates two identical rows of data every time I run this code!

    Any more ideas?

    PHP Code:
    <?php

    //Define Variables

    $username="dot_dot";
    $password="q(@Q7SP4L((D";
    $database="dot_thesiteguru";

    $title=$_POST['title'];
    $meta=$_POST['meta'];
    $header_1=$_POST['header_1'];
    $paragraph_1=$_POST['paragraph_1'];
    $header_2=$_POST['header_2'];
    $paragraph_2=$_POST['paragraph_2'];
    $author=$_POST['author'];


    //Connect To the Database Server

    mysql_connect(localhost,$username,$password);


    //Select the Database

    @mysql_select_db($database) or die("Unable to select database");


    //Command the Server to Insert Values

    $query="INSERT INTO webpage VALUES ('','$title','$meta','$header_1','$paragraph_1','$header_2','$paragraph_2','$author')";

    mysql_query($query);

    $id mysql_insert_id();


    //Check if Insertation of Values Was Successful

    $result mysql_query($query);

    if(
    $result) {
    echo 
    "You have successfully saved a webpage with author $author and title $title.  The ID for this webpage is $id.";
    } else {
    echo 
    "There was a problem processing the information. Please contact us at support[@]thesiteguru.com.";
    }


    //Close the Connection

    mysql_close();

    ?>
    Mr. Baggins

  9. #9
    Join Date
    Jan 2009
    Posts
    3,346
    You are still executing the query twice...
    mysql_query($query);

    $id = mysql_insert_id();


    //Check if Insertation of Values Was Successful

    $result = mysql_query($query);

  10. #10
    Join Date
    Jul 2010
    Posts
    35
    Forgive me for my novice stance!

    So assigning the function to a variable actually runs the function?

    If that's the case, what do you think I should do?

  11. #11
    Join Date
    Jan 2009
    Posts
    3,346
    Yes assigning the result of a function runs the function to get the result.

    Code:
    $result = mysql_query($query);
    
    $id = mysql_insert_id(); 
    
    
    //Check if Insertation of Values Was Successful

  12. #12
    Join Date
    Jul 2010
    Posts
    35
    Wow, thanks a lot for that, criterion9 (and for your patience!).

    IT WORKS!

    Mr. Baggins

  13. #13
    Join Date
    Jan 2009
    Posts
    3,346
    Glad I could help. Coincidentally I would move the $id assignment to a block where you are sure the query didn't fail.

    Code:
    $result = mysql_query($sql);
    if(!$result){
    //result fail
    } else {
    $id = mysql_insert_id();
    //other stuff here
    }

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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