How to deal with array of data?

Dvorak

Lets say i create a table 'choices' in mysql.it contains 3 columns which are id,food,price. now what my code do is display all the data in the table sort by id ascending.when it display the data,i also want it to be editable. i provide update button to do this.For example,i have this data:-
id | food | price |
1 |coke | 2.50 |
2 |cake | 4.00 |
3 |pringles | 7.20 |

problem is,when i change a single data,lets say i change the price for pringles to 3.00,all the three row will be replaced by pringles and 3.00.how do i change a single data without affecting the others?pls help..i've been working on it all nite,i know it might be simple,but i dont have any idea how...

here's my code

<html>
<head>
<title>Untitled Document</title>
</head>

<body>
<?php
// Connects to your Database 
mysql_connect("localhost", "root", "") or die(mysql_error()); 
mysql_select_db("choice") or die(mysql_error()); 

$food = $_GET[food];
$price = $_GET[price];

if(isset($_GET['update']))
{
$sql="UPDATE choices SET food='$food',price='$price' where id='$a'";
die('<center><br /><br />Information updated.<a href=parameter.php>Click Here to go to previous page</a></center>');
$result=mysql_query($sql);
$sql = "SELECT * FROM choices"; 

$result = mysql_query($sql); 

$myrow=mysql_fetch_array($result); 

}
else 
{
$sql = "SELECT * FROM choices"; 

$result=mysql_query($sql) or die (mysql_error());
?>
<form method="get" action="<?php echo $PHP_SELF?>">

<p>Parameter for Choices;</p>
<table width="350" border="1">
  <tr>
    <td width="185">Food</td>
    <td width="149">Price</td>
  </tr>
<?
while($myrow=mysql_fetch_array($result))
{
$a = $myrow["id"];
?>
  <tr>
    <td>&nbsp;<input type="text" name="food" value="<? echo $myrow["food"]?>"/></td>
    <td>&nbsp;<input type="text" name="price" value="<? echo $myrow["price"]?>"/></td></td>
  </tr>
   <?
}
}
?>
</table>

<p>
  <input name="update" type="submit" id="update" value="Update" />
</p>
</form>
</body>

</html>

bradgrafelman

I would say do something this:

while($myrow=mysql_fetch_array($result)) 
{ 
$a = $myrow["id"]; 
?> 
  <tr> 
    <td>&nbsp;<input type="text" name="food[<?php echo $a ?>]" value="<?php echo $myrow["food"]?>"/></td> 
    <td>&nbsp;<input type="text" name="price[<?php echo $a ?>]" value="<?php echo $myrow["price"]?>"/></td></td> 
  </tr> 
   <?php 
}

Now, you'll get back two arrays, with the items' id numbers being the keys and the new value being the array value.

Then, you could loop through the arrays and update the DB:

for($i=0; isset($_POST['food'][$i]) && isset($_POST['price'][$i]); $i++) {
    $query = 'UPDATE `choices` SET `food`=\'' . $_POST['food'][$i] . '\', `price`=\'' . $_POST['price'][$i] . '\' WHERE `id`=' . $i . ' LIMIT 1';
    $exec = mysql_query($query);
}

Since you didn't do it in your code, I didn't in my example snippet, but I do hope you realize that you aren't validating/sanitizing any of the user-provided data, which means you are most likely leaving yourself vulnerable to a SQL injection attack. Google the term (or search the boards) for more info - you'll want to use [man]mysql_real_escape_string/man to clean up any user-provided data ($GET, $POST, etc.) before you use it in a query.

Dvorak

<html> 
<head> 
<title>Untitled Document</title> 
</head> 

<body> 
<?php 
// Connects to your Database 
mysql_connect("localhost", "root", "") or die(mysql_error()); 
mysql_select_db("choice") or die(mysql_error()); 

$food = $_GET[food]; 
$price = $_GET[price]; 

if(isset($_GET['update'])) 
{ 
$sql="UPDATE choices SET food='$food',price='$price' where id='$a'"; 
die('<center><br /><br />Information updated.<a href=parameter.php>Click Here to go to previous page</a></center>'); 
$result=mysql_query($sql); 
$sql = "SELECT * FROM choices"; 

$result = mysql_query($sql); 

$myrow=mysql_fetch_array($result); 

} 
else 
{ 
$sql = "SELECT * FROM choices"; 

$result=mysql_query($sql) or die (mysql_error()); 
?> 
<form method="get" action="<?php echo $PHP_SELF?>"> 

<p>Parameter for Choices;</p> 
<table width="350" border="1"> 
  <tr> 
    <td width="185">Food</td> 
    <td width="149">Price</td> 
  </tr> 
<? 
while($myrow=mysql_fetch_array($result)) 
{ 
$a = $myrow["id"]; 
?> 
  <tr> 
    <td>&nbsp;<input type="text" name="food[<?php echo $a ?>]" value="<?php echo $myrow["food"]?>"/></td> 
    <td>&nbsp;<input type="text" name="price[<?php echo $a ?>]" value="<?php echo $myrow["price"]?>"/></td></td> 
  </tr> 
   <?php 
} 
} 
} 

for($i=0; isset($_POST['food'][$i]) && isset($_POST['price'][$i]); $i++) { 
    $query = 'UPDATE `choices` SET `food`=\'' . $_POST['food'][$i] . '\', `price`=\'' . $_POST['price'][$i] . '\' WHERE `id`=' . $i . ' LIMIT 1'; 
    $exec = mysql_query($query); 
} 

?> 
</table> 

<p> 
  <input name="update" type="submit" id="update" value="Update" /> 
</p> 
</form>

am i putting the code at the right place?it didn't work,so i thought i didnt place it properly.i'm a beginner...as u can see...thank u for helping..

bradgrafelman

Few things:

I would recommend changing your form to POST information, instead of using GET. Don't forget to change the $_GET['update'] reference in your if() statement as well.
Get rid of the two lines where you defined $food and $price - the for() loop will handle getting all of the values.
Notice how my second code snippet had a for() loop and ran multiple UPDATE queries? Where do you think that would go? Notice someone else in your script that is currently trying to run an UPDATE query (but not working) ? Get rid of the part of the script that's trying to update the database and move the code from my second code snippet up there.