[RESOLVED] UPDATE records based on current records

[mtputer_com]

I have a form that displays restaurant menu 'specials' prices for the week:

There are only seven records: Mon-Sun.

$query="SELECT * FROM specials ORDER BY menu_id";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><hr /><br>";

$i=0;
while ($i < $num) {

$menu_day=mysql_result($result,$i,"menu_day");
$b_item=mysql_result($result,$i,"b_item");
$l_item=mysql_result($result,$i,"l_item");
$l2_item=mysql_result($result,$i,"l2_item");
$d_item=mysql_result($result,$i,"d_item");
$d2_item=mysql_result($result,$i,"d2_item");
$s_item=mysql_result($result,$i,"s_item");
$b_price=mysql_result($result,$i,"b_price");
$l_price=mysql_result($result,$i,"l_price");
$l2_price=mysql_result($result,$i,"l2_price");
$d_price=mysql_result($result,$i,"d_price");
$d2_price=mysql_result($result,$i,"d2_price");
$s_price=mysql_result($result,$i,"s_price");



echo "<h1>$menu_day</h1><br />Breakfast:&nbsp&nbsp;<input type=\"text\" size=\"40\" name=\"b_item\" value=\"$b_item\"> - <input type=\"text\" size=\"5\" name=\"b_price\" value=\"$b_price\"><br />Lunch #1:&nbsp&nbsp<input type=\"text\" size=\"40\" name=\"l_item\" value=\"$l_item\"> - <input type=\"text\" size=\"5\" name=\"l_price\" value=\"$l_price\"><br />Lunch #2:&nbsp&nbsp<input type=\"text\" size=\"40\" name=\"l2_item\" value=\"$l2_item\"> - <input type=\"text\" size=\"5\" name=\"l2_price\" value=\"$l2_price\"><br />Dinner #1:&nbsp<input type=\"text\" size=\"40\" name=\"d_item\" value=\"$d_item\"> - <input type=\"text\" size=\"5\" name=\"d_price\" value=\"$d_price\"><br />Dinner #2:&nbsp<input type=\"text\" size=\"40\" name=\"d2_item\" value=\"$d2_item\"> - <input type=\"text\" size=\"5\" name=\"d2_price\" value=\"$d2_price\"><br />Soup:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp<input type=\"text\" size=\"40\" name=\"s_item\" value=\"$s_item\"> - <input type=\"text\" size=\"5\" name=\"s_price\" value=\"$s_price\"><hr><br />";


$i++;
}

The form displays into editable text boxes.

I would like to be able to edit the content of these text boxes for any of the days or for all of them, click a submit button and have it update the MySQL database with the new information.

Is this possible?

I tried the following with no success:

if ($_SERVER['REQUEST_METHOD'] == "POST")  

    { 

$menu_date = $_POST["menu_date"]; 
$menu_day = $_POST["menu_day"]; 
$b_item = $_POST["b_item"]; 
$l_item = $_POST["l_item"];
$l2_item = $_POST["l2_item"];
$d_item = $_POST["d_item"];
$d2_item = $_POST["d2_item"];
$s_item = $_POST["s_item"];
$b_price = $_POST["b_price"];
$l_price = $_POST["l_price"];
$l2_price = $_POST["l2_price"];
$d_price = $_POST["d_price"];
$d2_price = $_POST["d2_price"];
$s_price = $_POST["s_price"];

$sql  = "UPDATE specials SET menu_day = '$menu_day', b_item = '$b_item', l_item = '$l_item', l2_item = '$l2_item', d_item = '$d_item', d2_item = '$d2_item', s_item = '$s_item', b_price = '$b_price', l_price = '$l_price', l2_price = '$l2_price', d_price = '$d_price', d2_price = '$d2_price', s_price = '$s_price'"; 

$result = mysql_query($sql)
	or die(mysql_error());

Thanks in advance for any help.

Jeff

[mtputer_com]

OK, so I have spent numerous hours researching this and found a little cleaner code and a few sites that say this project is possible, but I am still having problems. The menu items show up in the input fields, so I am connecting to the database just fine. My problem must be in the $sql1 statement (near the bottom)with the UPDATE command. I am able to click the submit button and it refreshes the page, but the data reverts back to the data prior to my updates on the form and nothing changes in PHPMyAdmin. I'm sure it is something simple, but I am lost. I am still rather new to databases and PHP. If one of you guru's wants to scream and yell at me for being such a dumb $%&, I will accept that if I could get a little help. Again, any help is appreciated.

Here is my new code:

<?PHP

$host='localhost';
$username='*';
$password='*';
$db_name='*';
$tbl_name='specials';

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB"); 

$sql="SELECT * FROM $tbl_name ORDER BY menu_id";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

echo "<b><center>Database Output</center></b><br><hr /><br>";

?>
<table border="0px" cellspacing="1px" cellpadding="0px">
<form name="form1" method="POST" action="changeSpecials.php">
<tr>
<td>
<table border="0px" cellspacing="1px" cellpadding="0px">
<tr>
<td align="left"><font color="#750b3c"><strong>Day</strong></font></td>

<td align="center"><font color="#750b3c"><strong>Breakfast</strong></font></td>
<td align="center"><font color="#750b3c"><strong><--$</strong></font></td>
<td align="center"><font color="#750b3c"><strong>Lunch1</strong></font></td>
<td align="center"><font color="#750b3c"><strong><--$</strong></font></td>
<td align="center"><font color="#750b3c"><strong>Lunch2</strong></font></td>
<td align="center"><font color="#750b3c"><strong><--$</strong></font></td>
<td align="center"><font color="#750b3c"><strong>Dinner1</strong></font></td>
<td align="center"><font color="#750b3c"><strong><--$</strong></font></td>
<td align="center"><font color="#750b3c"><strong>Dinner2</strong></font></td>
<td align="center"><font color="#750b3c"><strong><--$</strong></font></td>
<td align="center"><font color="#750b3c"><strong>Soup</strong></font></td>

</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>


<tr>

<td align="left"><? $menu_dayNew[]=$rows['menu_day']; ?><strong><? echo $rows['menu_day']; ?></strong></td>
<td align="center"><input name="menu_dayNew[]" type="text" id="menu_dayNew" value="<? echo $rows['menu_day']; ?>" size="10"></td>
<td align="center"><input name="breakfast[]" type="text" id="breakfast" value="<? echo $rows['b_item']; ?>" size="30"></td>
<td align="center"><input name="breakfast_price[]" type="text" id="breakfast_price" value="<? echo $rows['b_price']; ?>" size="3"></td>
<td align="center"><input name="lunch1[]" type="text" id="lunch1" value="<? echo $rows['l_item']; ?>" size="30"></td>
<td align="center"><input name="lunch1_price[]" type="text" id="lunch1_price" value="<? echo $rows['l_price']; ?>" size="3"></td>
<td align="center"><input name="lunch2[]" type="text" id="lunch2" value="<? echo $rows['l2_item']; ?>" size="30"></td>
<td align="center"><input name="lunch2_price[]" type="text" id="lunch2_price" value="<? echo $rows['l2_price']; ?>" size="3"></td>
<td align="center"><input name="dinner1[]" type="text" id="dinner1" value="<? echo $rows['d_item']; ?>" size="30"></td>
<td align="center"><input name="dinner1_price[]" type="text" id="dinner1_price" value="<? echo $rows['d_price']; ?>" size="3"></td>
<td align="center"><input name="dinner2[]" type="text" id="dinner2" value="<? echo $rows['d2_item']; ?>" size="30"></td>
<td align="center"><input name="dinner2_price[]" type="text" id="dinner2_price" value="<? echo $rows['d2_price']; ?>" size="3"></td>
<td align="center"><input name="soup[]" type="text" id="soup" value="<? echo $rows['s_item']; ?>" size="30"></td>
</tr>

<?php
}
?>

<tr>

<td colspan="12" align="left"><br /><br /><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>

<?php
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET breakfast='$b_item[$i]', breakfast_price='$b_price[$i]', lunch1='$l_item[$i]', lunch1_price='$l_price[$i]', lunch2='$l2_item[$i]', lunch2_price='$l2_price[$i]', dinner1='$d_item[$i]', dinner1_price='$d_price[$i]', dinner2='$d2_item[$i]', dinner2_price='$d2_price[$i]', soup='$s_item[$i]' WHERE menu_dayNew='$menu_day[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:changeSpecials.php");
}
mysql_close();
?>

[mtputer_com]

So, more than 24 hours later of straight coding, I finally came up with the solution for the problem and it works just fine. No help to anyone on this forum, though. I belong to a lot of forums and try to help everyone I can, but I couldn't even get someone to speak to me on this one. I guess this will be my last post on this one.