homepage Welcome to WebmasterWorld Guest from 54.205.247.203
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Update single record updates all records
garwil




msg:4325295
 10:09 pm on Jun 12, 2011 (gmt 0)

Hi,

I am hoping someone can spot where i am slipping up.
I am pulling my hair out with this one and no doubt it will be a simple error that for the life of me I cannot pinpoint

I have code that is meant to update a single record. The update page and form gets the record id from the url and although everything is being populated correctly, when I make any changes and hit "Submit" then ALL the records are changed. This despite the fact that I have echoed the id and it displays the correct id is being captured correctly. Have found some otehr items on the MySql forums but they all point ot a misshed "Where" in the update statement No so with my code and now am stumped.. Hope someone can help here..

My Code

<?php
include '../dbfunctions.php';

session_start();

$link = dbConnect();

checkVenueLogin();

$rateid = $_GET['id'];
echo $rateid;
$rates = dbGetRows("rates", "id = '".$rateid."'");
$rate = mysql_fetch_array($rates, MYSQL_ASSOC);

$venues = dbGetRows("venues", "id = '".$_SESSION['venueid']."'");
$venue = mysql_fetch_array($venues, MYSQL_ASSOC);
if( isset($_POST['Submit']) ) {

$query = "UPDATE rates SET `datefrom` = '".$_POST['datefrom']."', `dateto` = '".$_POST['dateto']."', `ratename` = '".$_POST['ratename']."', `single` = '".$_POST['single']."', `double` = '".$_POST['double']."', `child` = '".$_POST['child']."', `infant` = '".$_POST['infant']."', `base` = '".$_POST['base']."' WHERE '".$rate['id']."' = '".$rateid."'";
var_dump($query);
$result = mysql_query($query) or die( "Error: " . mysql_error() );
}



?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Modify Rate NO. <?PHP echo $rateid; ?></title>
<link href="../bb.css" rel="stylesheet" type="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFF4DC"
<?php if(isset($_POST['Submit']) && $result) echo "onLoad=\"window.close(); opener.location.reload(true);\""; ?>>


<table width="100%" border="0" cellspacing="0" cellpadding="0" align="center">
<tr>
<td width="32"><img src="images/admin_03.gif"></td>
<td width="0*" bgcolor="#FFFFFF" background="images/admin_04.gif" style="background-repeat: repeat-x;">&nbsp;</td>
<td width="35"><img src="images/admin_07.gif" width="32" height="33"></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" background="images/admin_15.gif" style="background-repeat: repeat-y;"></td>
<td bgcolor="#FFFFFF"><table width="100%" border="0" cellspacing="0" cellpadding="0" style="margin-left: 10px; margin-right: 10px;">
<tr>
<td>
<font size="3"><b><?php echo $venue['venuename']; ?> - MODIFY RATE</b></font><br>
<br>

<form name="form1" method="post" action="editrates.php">
<table width="449" border="0" cellspacing="0" cellpadding="2">
<tr>

<td width="104"><b>Rate Name</b></td>
<td width="235" valign="top"><input type="text" name="ratename" value="<?php echo $rate['ratename']; ?>">
</td>
</tr>
<tr>
<td valign="top">&nbsp;</td>
<td valign="top">&nbsp;</td>
</tr>
<tr>
<td><strong>From Date</strong></td>
<td><input type="text" name="datefrom" id="datefrom" size="10" readonly="true" value="<?php echo $rate['datefrom']; ?>">
&nbsp;<img src="images/calendar01.gif" class="mxw_cld" onClick="return showCalendar('datefrom', 'yyyy-mm-dd', 'false');" style="cursor:pointer;"></td>
</tr>
<tr>
<td><strong>To Date</strong></td>
<td><input type="text" name="dateto" id="dateto" size="10" readonly="true" value="<?php echo $rate['dateto']; ?>">
&nbsp; <img src="images/calendar01.gif" class="mxw_cld" onClick="return showCalendar('dateto', 'yyyy-mm-dd', 'false');" style="cursor:pointer;"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><b> Adults - Single</b></td>
<td><label>
<input type="text" name="single" value="<?php echo $rate['single']; ?>">
</label></td>
</tr>
<tr>
<td><strong>Adults - Double</strong></td>
<td><label>
<input type="text" name="double" value="<?php echo $rate['double']; ?>">
</label></td>
</tr>
<tr>
<td><strong>Child</strong></td>
<td><label>
<input type="text" name="child" value="<?php echo $rate['child']; ?>">
</label></td>
</tr>
<tr>
<td><b>Infant</b></td>
<td><label>
<input type="text" name="infant" value="<?php echo $rate['infant']; ?>">
</label></td>
</tr>

<td><strong>Base Rate</strong></td>
<td><label></label>
<label>
<input type="text" name="base" value="<?php echo $rate['base']; ?>">
</label></td>
</tr>
<tr>
<td colspan="2"><input type="Submit" name="Submit" value="Submit">
.
<input type="button" name="Cancel" value="Close Window" onClick="window.close();"></td>
</tr>
</table>
</form>
</table></td>
<td bgcolor="#FFFFFF" align="right" background="images/admin_14.gif" style="background-position: right; background-repeat: repeat-y;"></td>
</tr>
<tr>
<td><img src="images/admin_21.gif" width="32" height="33"></td>
<td bgcolor="#FFFFFF" background="images/admin_23.gif" style="background-position: bottom; background-repeat: repeat-x;"></td>
<td><img src="images/admin_20.gif" width="32" height="33"></td>
</tr>
</table>

</body>
</html>

 

rocknbil




msg:4325558
 5:21 pm on Jun 13, 2011 (gmt 0)

Welcome aboard garwil, presuming the first part is your update and your second part is the form that is being processed, there is no "$_GET['id']" that I can see for two reasons. Your form uses the post method and I don't see a query string or even a hidden field named "id" if you changed the method to get.

This leaves rate ID as an empty string.

As to why it's nuking all records - I can only presume that in your database, "rateid" is null/empty (or if it's an integer type as it should be, zero), so when you say

Update table set [values] where rateid=''

it will indeed update all records. If it's an integer field, '' evaluates to zero. If this is the case, remove the quotes around rateid (you don't need to quote integer values.) It will likely give you a mysql error if the previous is true, alerting you to the problem.

[edited by: rocknbil at 5:25 pm (utc) on Jun 13, 2011]

brotherhood of LAN




msg:4325563
 5:25 pm on Jun 13, 2011 (gmt 0)

Also check the WHERE clause in your var dump... if you have something like "WHERE 1 = 1" that is going to match all rows.

I'd assume that since you're updating the one table called 'rates' it'd make sense to hard code the column that matches the WHERE clause if you're always referencing that column.

garwil




msg:4325576
 5:41 pm on Jun 13, 2011 (gmt 0)

hi rocknbill and brotherhood of lan... thanks for the welcome and replies..
rocknbill... I have this at the top of my page where it captures the record id from a URL. I thought I would echo it to confirm the id is correct and it did return the right id so I thought I was on the right track..
$rateid = $_GET['id'];
echo $rateid;

What you say is making sense to me now. Will try your suggestion re the quotes and see how far I get.

brotherhood of LAN...You are correct.. one table called rates... I have no idea how to hard code the columns... can you give me an example?

I really appreciate your time spent helping...

brotherhood of LAN




msg:4325585
 5:55 pm on Jun 13, 2011 (gmt 0)

hard code the columns


UPDATE table_name SET column1 = 123,column2 = 456 WHERE myid = 1

If you are always updating the database by referring to a row's 'uniqueid' then put that in there so there's no ambiguation.

$query = "UPDATE rates SET `datefrom` = '".$_POST['datefrom']."', `dateto` = '".$_POST['dateto']."', `ratename` = '".$_POST['ratename']."', `single` = '".$_POST['single']."', `double` = '".$_POST['double']."', `child` = '".$_POST['child']."', `infant` = '".$_POST['infant']."', `base` = '".$_POST['base']."' WHERE myid = '".$rateid."'";


So you would want to replace 'myid' with your own column name that you want to reference in the table.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved