Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Mar 2007
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    php mysql update and delete problem

    Hi all,
    (I'm new to php mysql so bear with me)
    I have created an edit page for my site http://www.katherinewagnerdesigns.co...lford/edit.php

    The php is this:
    PHP Code:
    $query="SELECT * FROM home";
    $result=mysql_query($query);
    $num=mysql_numrows($result); 
    mysql_close();
    $i=0;
    while (
    $i $num) {
    $date=mysql_result($result,$i,"date");
    $subtitle=mysql_result($result,$i,"subtitle");
    $text=mysql_result($result,$i,"text"); 
    and then the code:
    Code:
    <form action="updated.php" method="post">
    <input type="hidden" name="ud_id" value="$id">
    Title: <br /><input type="text" name="ud_subtitle" value="<? echo $subtitle; ?>"><br />
    Date: <br /><input type="text" name="ud_date" value="<? echo $date; ?>"><br />
    Content: <br /><textarea name="ud_text" rows=10 cols=80><? echo $text; ?></textarea>
    <input type="Submit" value="Update">
    </form>
    <form action="deleted.php" method="post">
    <input type="hidden" name="id" value="$id">
    <input type="Submit" value="Delete">
    </form>
    ending with this so that it copies each post out with each form:
    PHP Code:
    <? $i++;
    }
    ?>
    What happens when I click Update, however, is it only updates the first post...so anything I change in the posts below and hit on that Update button, it will still only changes the first post and the others stay the same. Somehow it looks like it is not getting the id number of each successive post. The updated.php code is:
    PHP Code:
    $query="UPDATE home SET date='$ud_date', subtitle='$ud_subtitle', text='$ud_text' WHERE id='$ud_id'";
    @
    mysql_select_db($database) or die( "Unable to select database");
    mysql_query($query);
    echo 
    "Record Updated";
    mysql_close(); 
    And also, the Delete button doesn't do anything at all...deleted.php is:
    PHP Code:
    $query="DELETE FROM contacts WHERE id='$id'";
    mysql_query($query);
    echo 
    "Record Deleted";
    mysql_close(); 
    Why is this stuff happening...I can't seem to find anything wrong in the code...but again, I am just learning this so I must be doing something wrong. Thanks for any help, Matt

    *edit*
    Maybe there is a way to make each forms Update button update every post at once from each form...eliminating the need to detect which id Update button the user clicks on...?
    Last edited by mlmorg; 07-18-2007 at 06:04 AM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    In order to troubleshoot this, echo out the contents of the $_POST array at the top of your updated.php script, like so:

    PHP Code:
    print "<pre>\n";
    print_r($_POST);
    print 
    "</pre>\n"
    This will give you an idea of what data is making it through to updated.php and what data is not. Once you determine if that part works, then you can move on to the UPDATE query.

    How is the $ud_id variable populated? Do you have register_globals turned on? (It is wise to turn register_globals off and use the values in the $_POST array.)

    Also... some nitpicking... you should always name your <form> tags-- it's just good HTML practice. In this case you'd need to include the $ud_id value as part of the form name to keep the name unique. And it's good practice to use mysql_fetch_array() instead of mysql_result() when you are retrieving more than one column from your query (one function call as opposed to several). And ALWAYS ALWAYS ALWAYS check your query to see if it succeeds or fails. While the query "SELECT * FROM home" is unlikely to fail, it is a good habit to get into, so when a more complex query does fail, you don't spend days trying to find out why.

    PHP Code:
    $query "SELECT * FROM home";
    $result mysql_query($query);
    if (!
    $result) {
        die(
    "SQL Error! Query text: $query<br />Error text: ".mysql_error());


  • #3
    Regular Coder
    Join Date
    Mar 2007
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply.

    I echoed out the contents to read this when I hit update on the first post:

    Array
    (
    [ud_id] => $id
    [ud_subtitle] => Movies In The Park
    [ud_date] => Aug. 16th, 2007
    [ud_text] => Free outdoor movies in the Sunken Park...(this continues on)
    )

    If I hit update on the other posts it would echo out that posts information and just $id for [ud_id]. That $id seems to be the problem to me...

    Also just wanted to add that I went into phpmyadmin and noticed that the rows id's started from 0 ->. I thought they were supposed to start at 1 so I changed it to that and then the update wouldn't work at all (as in it wouldn't update but showed the same echoed info). Don't know if that's pertinent.

    The question about how the $ud_id variable is populated...I honestly have no idea...I have been trying to learn from this tutorial: http://www.freewebmasterhelp.com/tutorials/phpmysql/7 It's probably not the best one but was easiest for me to semi understand ha.

    Also I don't know what register_globals are (sorry again...) and I forgot to name the form (because I was just basically copying the tutorial to try and figure out how it worked...) and I will use mysql_fetch_array from now on.

    So anyway, it seems to me that it's getting the information to update on the update page...but it's not getting which id the update is for...so when it updates it is just updating to the default first $id which must be 0 which is obviously the first post I have that keeps getting updated no matter which form and submit button I'm using. But I have no idea why that's not working. I tried adding:

    PHP Code:
    $id=mysql_result($result,$i,"id"); 
    to the edit.php page where all those same lines are...because I thought that that assigned the data to a variable...but that didn't seem to change anything.

    Anyway, thanks for the help, Matt

    *edit*
    I just had an idea as to why it was getting $id in the echo on the update page so I changed the form a bit to show the id number by adding:
    Code:
    <input type="text" name="ud_id" value="<? echo $id; ?>">
    It then showed the ids for each post (0, 1, 2, etc.) and so I tried the update again and lo and behold it worked. The echo showed the id number of the update button I had submitted and it changed that post and not only the first one. I have no idea why this happened though...any ideas? Any better solutions? Oh and I forgot to add that my delete button still doesn't delete posts...
    Last edited by mlmorg; 07-19-2007 at 05:22 AM.

  • #4
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    try this.

    PHP Code:
    $sql=mysql_query("SELECT * FROM `home`") or die(mysql_error());
    echo 
    '<form name="form1" action="updated.php" method="post">'//start the form
    while($row mysql_fetch_array($sql)){
    $date $row['date'];
    $subtitle $row['subtitle'];
    $text $row['text'];  
    echo 
    '<input type="hidden" name="ud_id[]" value="'.$id.'">
    Title: <br /><input type="text" name="ud_subtitle[]" value="'
    .$subtitle.'"><br />
    Date: <br /><input type="text" name="ud_date[]" value="'
    .$date.'"><br />
    Content: <br /><textarea name="ud_text[]" rows=10 cols=80>'
    .$text.'</textarea>';
    echo 
    "<input type=\"button\" value=\"Delete\" onclick=\"location.href='deleted.php?id=$id';\"><hr>";
    }
    echo 
    '<input type="hidden" name="row_count" value="'.mysql_num_rows($sql).'"><input type="submit" value="Update"></form>'
    and updated.php do this
    PHP Code:
    @mysql_select_db($database) or die( "Unable to select database");
    for(
    $i=0;$i<$_POST['row_count'];$i++){
    mysql_query("UPDATE `home` SET `date`='".$_POST['ud_date'][$i]."', `subtitle`='".$_POST['ud_subtitle'][$i]."', `text`='".$_POST['ud_text'][$i]."' WHERE `id`='".$_POST['ud_id'][$i]."'") or die(mysql_error());
    echo 
    "Record Updated";
    }
    mysql_close(); 
    and deleted.php do this
    PHP Code:
    mysql_query("DELETE FROM contacts WHERE id='".$_GET['id']."'") or die(mysql_error());
    echo 
    "Record Deleted";
    mysql_close(); 

  • #5
    Regular Coder
    Join Date
    Mar 2007
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again for the help.

    I tried what you said and this is what echoed out on the update page:

    Array
    (
    [ud_id] => Array
    (
    [0] =>
    [1] =>
    [2] =>
    )

    [ud_subtitle] => Array
    (
    [0] => Movies In The Park
    [1] => Annual Flea Market and Crafts Fair
    [2] => test
    )

    [ud_date] => Array
    (
    [0] => Aug. 16th, 2007
    [1] => Jul. 14th, 2007
    [2] => test
    )

    [ud_text] => Array
    (
    [0] => Free outdoor movies in the Sunken Park...
    [1] => The York Courts of Guilford...
    [2] => test
    )

    [row_count] => 3
    )
    Record UpdatedRecord UpdatedRecord Updated

    This looks better to me...except for maybe the ud_id again. But the problem is the third post updates to the first one and the 2nd and 3rd posts don't update at all.

    And again the delete button doesn't do anything...

    Eghh...problems all over the place ha. Thanks for all the help though! It is much appreciated, Matt

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    If you wanted to go with your original code, you just need to update this line:

    PHP Code:
    <input type="hidden" name="ud_id" value="$id"
    To echo the $id variable, like this:

    PHP Code:
    <input type="hidden" name="ud_id" value="<?php echo $id?>">
    That would probably get things working. Boeing's code would work if you changed this line:

    PHP Code:
    echo '<input type="hidden" name="ud_id[]" value="'.$id."> 
    To use the $row['id'] value, like this:

    PHP Code:
    echo '<input type="hidden" name="ud_id[]" value="' $row['id'] . "> 
    I've never seen a point to copying $row[] values to other variables (such as doing $id = $row['id'] and then using $id-- you can just use $row['id']).

  • #7
    Regular Coder
    Join Date
    Mar 2007
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks fumigator for the clarification of both codes.

    Is there a better code to use? Maybe in terms of better for the database, etc. like you had said earlier about using mysql_fetch_array, etc. Would my original code have to be changed to use mysql_fetch_array or could I just replace the mysql_result line?

    Thanks again for the all the help you two.

    *edit*
    So the delete is now working too...But I noticed that if I delete something and then add something, it adds incrementally without realizing that there was one deleted. So if I delete 3, and then refresh and then add another, it adds it as id=4. Then if I delete that and add a new one it will add as id=5. What's going on?

    Here's the add code:
    Code:
    <font class="h3">Add New</font><br /><br />
    <form action="added.php" method="post">
    Title: <br /><input type="text" name="subtitle"><br />
    Date: <br /><input type="text" name="date"><br />
    Content: <br /><textarea name="text" rows=10 cols=80></textarea><br />
    <input type="Submit" value="Add">
    and then the added.php code:
    PHP Code:
    $query "INSERT INTO home VALUES ('','$date','$subtitle','$text')";
    mysql_query($query);
    echo 
    "Record Added";
    mysql_close(); 
    Last edited by mlmorg; 07-19-2007 at 04:11 PM.

  • #8
    Senior Coder
    Join Date
    Apr 2007
    Location
    Quakertown PA USA
    Posts
    1,028
    Thanks
    1
    Thanked 125 Times in 123 Posts
    Quote Originally Posted by mlmorg View Post
    So the delete is now working too...But I noticed that if I delete something and then add something, it adds incrementally without realizing that there was one deleted. So if I delete 3, and then refresh and then add another, it adds it as id=4. Then if I delete that and add a new one it will add as id=5. What's going on?
    Assuming you're talking about an auto-increment field, it's doing what it's supposed to do - that's how they work.

  • #9
    Regular Coder
    Join Date
    Mar 2007
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh ok, I had thought that the auto increment field would increment past the last used id number so that there wouldn't be any missing id numbers...but I guess it doesn't really matter that much.

    Thanks, Matt

    So is there a coding of this that's better than the other...should I just continue to use my edited version or use boeings version?
    Last edited by mlmorg; 07-19-2007 at 09:04 PM.


  •  

    Posting Permissions

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