Page 1 of 1

Trying to update 1 record BUT all records are updated

Posted: Mon Mar 08, 2004 2:10 pm
by boom4x
Hello im a newbie and need some help. The only problem I have is when I update 1 record all my records in mysql are being updated with the same info... and that erases all records.

Here is the code i am using
---------------
badlist.php - main script that list all records
---------------

Code: Select all

<? include('includes/badlist_header.inc.php'); ?> 
    <tr> 
          <td width=99 align=center bgcolor=#333333><b> 
          <font color=#FFFFFF>Name</font></b></td> 
          <td width=100 align=center bgcolor=#333333><b> 
          <font color=#FFFFFF>Phone #</font></b></td> 
          <td width=460 align=center bgcolor=#333333><b> 
          <font color=#FFFFFF>Notes</font></b></td> 
          <td width=103 align=center bgcolor=#333333><b> 
          <font color=#FFFFFF>Last Updated</font></b></td> 
    </tr> 
<? 
include('includes/settings.inc.php'); 
$result = mysql_query("SELECT * FROM badlist",$db); 
while ($info = mysql_fetch_array($result)) 
{ 
?> 
<tr> 
<td width=99 valign=top>&nbsp;<a href=badlist_addedit.php?id=<? echo "{$info['id']}"; ?>><? echo "{$info['name']}"; ?></a> 

&nbsp;<a href=badlist_delete.php?id=<? echo "{$info['id']}"; ?>><font size=1 color=red><b>D</b></font></a> 

</td> 
<td width=100 valign=top><center><? echo "{$info['phone']}"; ?></center></td> 
<td width=460 valign=top>&nbsp;<? echo "{$info['notes']}"; ?></td> 
<td width=103 valign=top><center><? echo "{$info['last_update']}"; ?></center></td> 
</tr> 
<? 
} 
include('includes/badlist_footer.inc.php'); 
?> 
<center><font size=1><? echo $copyright; ?></font></center>
----------
badlist_addedit.php - add new entry
-----------

Code: Select all

<? 
include('includes/settings.inc.php'); 
if($submit) 
    { 
    $sql = "INSERT INTO badlist (name, phone, notes, last_update) VALUES ('$name','$phone','$notes','$last_update')"; 
    $result = mysql_query($sql); 
    echo "Added to database.\n"; 
    } 
else if($update) 
    { 
    $sql = "UPDATE badlist SET name='$name',phone='$phone',notes='$notes',last_update='$last_update'"; 
    $result = mysql_query($sql); 
    echo "Updated database.\n"; 
    } 
else if($id) 
    { 
    $result = mysql_query("SELECT * FROM badlist WHERE id=$id",$db); 
    $info = mysql_fetch_array($result); 
?> 
<? include('includes/badlist_header.inc.php'); ?> 
<form method=post action=<? echo $PHP_SELF?>> 
<input type=hidden name=id value=<? echo $info["id"]?>> 
Name: <input type=text name=name value=<? echo "{$info['name']}"?>><br> 
Phone: <input type=text name=phone value=<? echo "{$info['phone']}"?>><br> 
Notes:<br> 
<textarea cols=40 rows=4 name=notes><? echo "{$info['notes']}"?></textarea> 
<br> 
Last Updated: <? echo "{$info['last_update']}"?> 
<input type=hidden name=last_update value=<? include('includes/date.inc.php'); ?><br> 
<input type=submit name=update value="Update Listing"></form> 
<? include('includes/badlist_footer.inc.php'); ?> 
<center><? echo $copyright; ?></center> 
<? 
    } 

else 
    { 
?> 
<? include('includes/badlist_header.inc.php'); ?> 
<form method=post action=<? echo $PHP_SELF?>> 
Name: <input type=text name=name><br> 
Phone #: <input type=text name=phone><br> 
Notes:<br> 
<textarea cols=40 rows=4 name=notes></textarea><br> 
<input type=hidden name=last_update value=<? include('includes/date.inc.php'); ?>> 
<input type=submit name=submit value="Add information"> 
</form> 
<? include('includes/badlist_footer.inc.php'); ?> 
<center><font size=1><? echo $copyright; ?></font></center> 
<? 
} 
?>
--------------------------------------------------------

If one of you guys can help me figure out what wrong with this script, I would greatly appriciated it.

Thank you in advance!

Posted: Mon Mar 08, 2004 3:18 pm
by sulen
This is because u have not specified a WHERE clause in ur update statement for mysql to understand where to make the UPDATE. I think this shud be able to help u out.

How did implement delete?

Posted: Tue Mar 09, 2004 12:13 am
by boom4x
Ok thanks a lot sulen! I really appriciate your advice.. It took me a lil time to figure it out but I GOT IT!

Please scroll down to the bottom... Im trying to implement the DELETE function but it not really working out for me, if anyone can help I would be very greatful.

This is the code which i fixed up with your help...

Code: Select all

<?
include('includes/settings.inc.php');
if($submit)
	{
    $sql = "INSERT INTO badlist (name, phone, notes, last_update) VALUES ('$name','$phone','$notes','$last_update')";
    $result = mysql_query($sql); 
    echo "Added to database.\n"; 
	}
else if($update)
	{
    $sql_update = "UPDATE badlist SET name = '$name' , phone = '$phone' , notes = '$notes' , last_update = '$last_update' WHERE id = '$id'";
    $result = mysql_query($sql_update);
    echo "Updated database.\n";
	}
else if($id)
	{
	$result = mysql_query("SELECT * FROM badlist WHERE id=$id",$db);
	$info = mysql_fetch_array($result);
?>
<? include('includes/badlist_header.inc.php'); ?>
<form method="post" action="<? echo $PHP_SELF?>">
<input type="hidden" name="id" value="<? echo $info["id"]?>">
Name: <input type="text" name="name" value="<? echo "{$info['name']}"?>"><br>
Phone: <input type="text" name="phone" value="<? echo "{$info['phone']}"?>"><br>
Notes:<br>
<textarea cols="40" rows="4" name="notes"><? echo "{$info['notes']}"?></textarea>
<br>
Last Updated: <? echo "{$info['last_update']}"?>
<input type="hidden" name="last_update" value="<? include('includes/date.inc.php'); ?>"><br>
<input type="submit" name="update" value="Update Listing">
</form>
<? include('includes/badlist_footer.inc.php'); ?>
<center><? echo $copyright; ?></center>
<?
	}

else
	{
?>
<? include('includes/badlist_header.inc.php'); ?>
<form method="post" action="<? echo $PHP_SELF?>">
Name: <input type="text" name="name"><br>
Phone #: <input type="text" name="phone"><br>
Notes:<br>
<textarea cols="40" rows="4" name="notes"></textarea><br>
<input type="hidden" name="last_update" value="<? include('includes/date.inc.php'); ?>">
<input type="submit" name="submit" value="Add information">
</form>
<? include('includes/badlist_footer.inc.php'); ?>
<center><font size=1><? echo $copyright; ?></font></center>
<?
}
?>

?>
This is the code im trying to use with the DELETE function.

Code: Select all

<?
include('includes/settings.inc.php');
if($submit)
	{
    $sql = "INSERT INTO badlist (name, phone, notes, last_update) VALUES ('$name','$phone','$notes','$last_update')";
    $result = mysql_query($sql); 
    echo "Added to database.\n"; 
	}
else if($update)
	{
    $sql_update = "UPDATE badlist SET name = '$name' , phone = '$phone' , notes = '$notes' , last_update = '$last_update' WHERE id 

= '$id'";
    $result = mysql_query($sql_update);
    echo "Updated database.\n";
	}
else if($delete)
	{
    $sql_delete = "DELETE FROM badlist WHERE = '$id' , $db";
    $result = mysql_query($sql_delete);
    echo "Deleted $id; database\n";
	}
else if($id)
	{
	$result = mysql_query("SELECT * FROM badlist WHERE id=$id",$db);
	$info = mysql_fetch_array($result);
?>
<? include('includes/badlist_header.inc.php'); ?>
<form method="post" action="<? echo $PHP_SELF?>">
<input type="hidden" name="id" value="<? echo $info["id"]?>">
Name: <input type="text" name="name" value="<? echo "{$info['name']}"?>"><br>
Phone: <input type="text" name="phone" value="<? echo "{$info['phone']}"?>"><br>
Notes:<br>
<textarea cols="40" rows="4" name="notes"><? echo "{$info['notes']}"?></textarea>
<br>
Last Updated: <? echo "{$info['last_update']}"?>
<input type="hidden" name="last_update" value="<? include('includes/date.inc.php'); ?>"><br>
<input type="submit" name="update" value="Update Listing">
<input type="submit" name="delete" value="Delete Listing">
</form>
<? include('includes/badlist_footer.inc.php'); ?>
<center><? echo $copyright; ?></center>
<?
	}

else
	{
?>
<? include('includes/badlist_header.inc.php'); ?>
<form method="post" action="<? echo $PHP_SELF?>">
Name: <input type="text" name="name"><br>
Phone #: <input type="text" name="phone"><br>
Notes:<br>
<textarea cols="40" rows="4" name="notes"></textarea><br>
<input type="hidden" name="last_update" value="<? include('includes/date.inc.php'); ?>">
<input type="submit" name="submit" value="Add information">
</form>
<? include('includes/badlist_footer.inc.php'); ?>
<center><font size=1><? echo $copyright; ?></font></center>
<?
}
?>
Any ideas? how to fix the DELETE function on update page???

Posted: Tue Mar 09, 2004 8:57 am
by Bill H
The db name should not be part of the sql statement:

Code: Select all

<?php
$sql_delete = "DELETE FROM badlist WHERE = '$id' , $db";
$result = mysql_query($sql_delete);
// should be
$sql_delete = "DELETE FROM badlist WHERE id='$id'";
?>
Also, if id is an integer you do not need the single quotes around it in the query statement. If it is a string you do need them.

Posted: Tue Mar 09, 2004 12:58 pm
by boom4x
Thanks... ok... but it still doesn't solve my problem with the delete button on the update page.