View Full Version : php mysql update and delete problem

07-18-2007, 06:58 AM
Hi all,
(I'm new to php mysql so bear with me)
I have created an edit page for my site http://www.katherinewagnerdesigns.com/test/guilford/edit.php

The php is this:

$query="SELECT * FROM home";
while ($i < $num) {

and then the 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 action="deleted.php" method="post">
<input type="hidden" name="id" value="$id">
<input type="Submit" value="Delete">

ending with this so that it copies each post out with each form:

<? $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:

$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");
echo "Record Updated";

And also, the Delete button doesn't do anything at all...deleted.php is:

$query="DELETE FROM contacts WHERE id='$id'";
echo "Record Deleted";

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

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...?

07-18-2007, 08:53 PM
In order to troubleshoot this, echo out the contents of the $_POST array at the top of your updated.php script, like so:

print "<pre>\n";
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.

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

07-19-2007, 05:58 AM
Thanks for the reply.

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

[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:


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

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:

<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...

07-19-2007, 06:22 AM
try this.

$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

@mysql_select_db($database) or die( "Unable to select database");
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";

and deleted.php do this

mysql_query("DELETE FROM contacts WHERE id='".$_GET['id']."'") or die(mysql_error());
echo "Record Deleted";

07-19-2007, 02:33 PM
Thanks again for the help.

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

[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

07-19-2007, 03:40 PM
If you wanted to go with your original code, you just need to update this line:

<input type="hidden" name="ud_id" value="$id">

To echo the $id variable, like this:

<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:

echo '<input type="hidden" name="ud_id[]" value="'.$id.'">

To use the $row['id'] value, like this:

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']).

07-19-2007, 03:59 PM
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.

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:

<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:

$query = "INSERT INTO home VALUES ('','$date','$subtitle','$text')";
echo "Record Added";

07-19-2007, 05:48 PM
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.

07-19-2007, 06:36 PM
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?