[RESOLVED] UPDATE statement updating only first now?

IntenseMX

I have small form where you can edit the info in the data base but for some reason after i hit send on the edit form, the only info gets updated is the one in first now... help? 😕😕😕😕

Page displaying date, and containing link to Edit form:

<html>
<head>
<title>Edit</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("beyondmotors", $con);

$result = mysql_query("SELECT * FROM vehicles");

echo "<table border='2'>
<tr>
<th>ID</th>
<th>Year</th>
<th>Make</th>
<th>Model</th>
<th>Mileage</th>
<th>EDIT</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['year'] . "</td>";
  echo "<td>" . $row['make'] . "</td>";
  echo "<td>" . $row['model'] . "</td>";
  echo "<td>" . $row['mileage'] . "</td>";
  echo ("<td><a href=\"edit_form.php?id=$row[id]\">Edit</a></td></tr>");
  echo "</tr>";
  }
echo "</table>";


$result = mysql_query("SELECT * FROM vehicles");

echo "<table border='4'>
<tr>
<th>ID</th>
<th>Year</th>
<th>Make</th>
<th>Model</th>
<th>Mileage</th>
<th>EDIT</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['year'] . "</td>";
  echo "<td>" . $row['make'] . "</td>";
  echo "<td>" . $row['model'] . "</td>";
  echo "<td>" . $row['mileage'] . "</td>";
  echo ("<td><a href=\"delete.php?id=$row[id]\">Delete</a></td></tr>");
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
echo " <a href='main.php'>Return to main page</a>";
?>
</body>
</html>

Edit Form ( it echos the arrays correct so up until this point everything is okay):

<?php
$id=$_GET['id']; 
mysql_connect("localhost","root","") or die("Error: ".mysql_error()); //add your DB username and password
mysql_select_db("beyondmotors");//add your dbname

//replace TestTable with the name of your table
$sql = "select * from vehicles where id='$id'";
$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)){

$id = $row['id'];
$year = $row['year'];
$make = $row['make'];
$model = $row['model'];
$mileage = $row['mileage'];

//we will echo these into the proper fields

}
mysql_free_result($query);
?>

<html>
<head>
<title>Edit User Info</title>
</head>

<body>

<form action="edit_data.php" method="post">

id:<br/>
<input type="text" value="<?php echo $id;?>" name="id" disabled/>

<br/>

Year:<br/>
<input type="text" value="<?php echo $year;?>" name="year"/>

<br/>

Make:<br/>
<input type="text" value="<?php echo $make;?>" name="make"/>

<br/>

Model:<br/>
<input type="text" value="<?php echo $model;?>" name="model"/>

</br>

Mileage:<br/>
<input type="text" value="<?php echo $mileage;?>" name="mileage"/>

</br>

<input type="submit" value="submit changes"/>
<?php echo " <a href='main.php'>Return to main page</a>";
?>
</form>
</body>
</html>

The update PHP script. once this goes through on first row in database is updated, all info gets updated correctly but it all goes in first now...I think something is wrong with the WHERE part but i cant first out what... UGH SO frustrating:

<?php
mysql_connect("localhost","root","") or die("Error: ".mysql_error()); //add your DB username and password
mysql_select_db("beyondmotors");//add your dbname

 //get the variables we transmitted from the form
$id = @$_POST['id'];
$year = $_POST['year'];
$make = $_POST['make'];
$model = $_POST['model'];
$mileage = $_POST['mileage'];

//replace TestTable with the name of your table
$sql = "UPDATE vehicles SET year='$year', make='$make', mileage='$mileage', model='$model' 
WHERE ID='id' LIMIT 1";

mysql_query($sql) or die ("Error: ".mysql_error());

echo "<a href='edit.php'>Return to edit info</a>";
echo "<a href='main.php'>Return to main page</a>";
?>

Derokorian

ID='id' should probably be ID='$id' or going by your other query more precisely id='$id' but right now you are looking to update a row with an id of the id as opposed to the number(?) stored in the $id variable.

Also note this code is very subject to SQL Injection and you should first sanitize input before using it in a query.

IntenseMX

ugh i know right it should be ID='id' or id='$id'
when i change it to WHERE id='$id' there is no change at all, it acts exactly the same.

yes i want to change the row with that id not the id of the row itself. ah this is driving me nuts, everything looks correct.

i'm using wamp server would that make a difference?

Derokorian

Maybe I'm missing something but from what you showed you are only editing one vehicle at a time anyway in your form. So why then, would you expect more than one row to be updated? or are you saying that only the row with id of 1 is able to be updated but other id's fail?

IntenseMX

yes i'm editing one row at the time,
once i click edit link, it goes to edit form, where the row info echos from database with the id that was selected at the previous page (hope that makes sense) .

up to that point everything is correct, if i click on first row with id 1 , all the info comes up for it in second form. if i click with id 2 everything also comes up for it and so on.

the problem is when i try to make changes and click submit, it goes to "edit_data.php" (last script is post) where i have the UPDATE statement

than i get no errors everything work. however the only info that gets update is the first row of the database, no mater which id i'm choosing before..

IntenseMX

maybe abit more visual:

check out the picutre:

http://i562.photobucket.com/albums/ss69/intensemx/pic-1.jpg

Derokorian

Ah ok, I understand the problem now - which makes helping that much easier.

The next thing I would tell you to do is to do a var_dump on the $_POST array on your last script and inspect it to see if it matches what you expect to go. You could even post the dump here.

Also try echoing out the $sql variable and paste that here as well.

IntenseMX

ok i'm new to databases never used var_dump. I googled it than added

<?php
var_dump($_POST);
var_dump($sql);
?>

to bottom of edit_data.php

this is what i got:

array
'year' => string '34534' (length=5)
'make' => string '34534' (length=5)
'model' => string '34534' (length=5)
'mileage' => string '0' (length=1)
string 'UPDATE vehicles SET year='34534', make='34534', mileage='0', model='34534'

WHERE id='' LIMIT 1' (length=96)

from what i can tell it doesn't grab ID variable at all? maybe because page before hand, the id field is non editable?

IntenseMX

ah there we go, once I removed the disabled from

<input type="text" value="<?php echo $id;?>" name="id" disabled/>

it started working correctly, but how can I make it non editable? I wouldn't want ppl to change the IDs?

ah thanks alot on the var_dump info very useful to identify the problem, I'm only few days in to php+databases so you have excuse my experience lvl

Derokorian

Did some testing, seems the disabled is the problem. Maybe try just outputting the ID as plain text and using a hidden field:

ID: <?php echo $id; ?><input type="hidden" name="id" value="<?php echo $id; ?>"/><br>

Edit: I didn't know about var_dump either until someone told me. I believe it was someone on this board no less. Just paying it forward so to speak. 😛

bradgrafelman

If you don't want users to be able to edit the 'id' attribute, then don't include it in the form at all.

Whether it's disabled, read only, or a hidden input, the user can still modify that value to whatever he/she likes. As such, if you're going to be including it in the POST'ed data, make sure you have some way of verifying whether or not the value you receive hasn't been altered (which could allow a user to modify a record that he/she shouldn't be able to modify).

Also note that user-supplied data should never be placed directly into a SQL query string, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize the data such as with a function like [man]mysql_real_escape_string/man (for string data) or by using prepared statements. See [man]security.database.sql-injection[/man] for more info.