Updating info from database

[kjdix]

Im wanting to submit modified info into the database to update it. So I have some code that produces text boxes and is populated by the current info in the database. I then want an admin to make any necessary changes, hit the submit button and update the entries in the database.
The code for the text boxes to enter info is

echo '<table width="1500"><tr><td>'; 
$query="select * from meals where WD='$DayWeek' "; 
    $result=mysql_query($query); 
    echo mysql_error(); 
if ($result!="") { 
    while ($row=mysql_fetch_array($result)) { 
echo '<p><h1>Change the '.$DayWeek.' menu</h1>'; 
echo '<form action="postrequest.php" method="post">'; 
echo '<p>Breakfast Title: <input type="text" name="btitle" value="'.$row['btitle'].'">'; 
echo '<p>Breakfast Option 1: <input type="text" name="bopt1" value="'.$row['bopt1'].'">'; 
echo '<p>Breakfast Option 2: <input type="text" name="bopt2" value="'.$row['bopt2'].'">'; 
echo '<p>Breakfast Option 3: <input type="text" name="bopt3" value="'.$row['bopt3'].'">'; 
echo '<p>Breakfast Option 4: <input type="text" name="bopt4" value="'.$row['bopt4'].'">'; 
echo '<p>Breakfast Option 5: <input type="text" name="bopt5" value="'.$row['bopt5'].'">'; 
echo '<p>Breakfast Desert: <input type="text" name="bdesert" value="'.$row['bdesert'].'">'; 
echo '<p>Lunch Title: <input type="text" name="ltitle" value="'.$row['ltitle'].'">'; 
echo '<p>Lunch Option 1: <input type="text" name="lopt1" value="'.$row['lopt1'].'">'; 
echo '<p>Lunch Option 2: <input type="text" name="lopt2" value="'.$row['lopt2'].'">'; 
echo '<p>Lunch Option 3: <input type="text" name="lopt3" value="'.$row['lopt3'].'">'; 
echo '<p>Lunch Option 4: <input type="text" name="lopt4" value="'.$row['lopt4'].'">'; 
echo '<p>Lunch Option 5: <input type="text" name="lopt5" value="'.$row['lopt5'].'">'; 
echo '<p>Lunch Option 6: <input type="text" name="lopt6" value="'.$row['lopt6'].'">'; 
echo '<p>Lunch Desert: <input type="text" name="ldesert" value="'.$row['ldesert'].'">'; 
echo '<p>Supper Title: <input type="text" name="stitle" value="'.$row['stitle'].'">'; 
echo '<p>Supper Option 1: <input type="text" name="sopt1" value="'.$row['sopt1'].'">'; 
echo '<p>Supper Option 2: <input type="text" name="sopt2" value="'.$row['sopt2'].'">'; 
echo '<p>Supper Option 3: <input type="text" name="sopt3" value="'.$row['sopt3'].'">'; 
echo '<p>Supper Option 4: <input type="text" name="sopt4" value="'.$row['sopt4'].'">'; 
echo '<p>Supper Option 5: <input type="text" name="sopt5" value="'.$row['sopt5'].'">'; 
echo '<p>Supper Option 6: <input type="text" name="sopt6" value="'.$row['sopt6'].'">'; 
echo '<p>Supper Desert: <input type="text" name="sdesert" value="'.$row['sdesert'].'">'; 
echo '<p><input type="submit" value="Submit">'; 
echo '</td>'; 
} 
} 

and then the postrequest is as follows

$server=mysql_connect($dbhostname,$dbusername,$dbpassword) or die ("Couldnt connect to server"); 
$db=mysql_select_db('menu',$server) or die ("Couldnt select DB"); 

$time=date("Y-m-d H:i:s"); 
$day=($_POST[day]); 
$week=($_POST[week]); 
$btitle=($_POST[btitle]); 
$bopt1=($_POST[bopt1]); 
$bopt2=($_POST[bopt2]); 
$bopt3=($_POST[bopt3]); 
$bopt4=($_POST[bopt4]); 
$bopt5=($_POST[bopt5]); 
$bdesert=($_POST[bdesert]); 
$ltitle=($_POST[ltitle]); 
$lopt1=($_POST[lopt1]); 
$lopt2=($_POST[lopt2]); 
$lopt3=($_POST[lopt3]); 
$lopt4=($_POST[lopt4]); 
$lopt5=($_POST[lopt5]); 
$lopt6=($_POST[lopt6]); 
$ldesert=($_POST[ldesert]); 
$stitle=($_POST[stitle]); 
$sopt1=($_POST[sopt1]); 
$sopt2=($_POST[sopt2]); 
$sopt3=($_POST[sopt3]); 
$sopt4=($_POST[sopt4]); 
$sopt5=($_POST[sopt5]); 
$sopt6=($_POST[sopt6]); 
$sdesert=($_POST[sdesert]); 
$query="UPDATE meals SET btitle='$btitle', bopt1='$bopt1', bopt2='$bopt2', bopt3='$bopt3', bopt4='$bopt4', bopt5='$bopt5', bdesert='$bdesert', ltitle='$ltitle', lopt1='$lopt1', lopt2='$lopt2', lopt3='$lopt3', lopt4='$lopt4', lopt5='$lopt5', lopt6='$lopt6', ldesert'$ldesert', stitle='$stitle', sopt1='$sopt1', sopt2='$sopt2', sopt3='$sopt3', sopt4='$sopt4', sopt5='$sopt5', sopt6='$sopt6', sdesert='$sdesert' WHERE WD='$DayWeek'"; 

$result=mysql_query($query); 
echo mysql_error(); 

The error I recieve is as follows

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Freshly made Muffins, Warm Bakers Basket', stitle='ENGLISH NIGHT', sopt1='SWEDE' at line 1

Any ideas?

[dagon]

$sdesert=($POST[sdesert]);

should be
$sdesert=mysql_real_escape_string($POST['sdesert']);

do that to all your vars

[kjdix]

Ok I found the problem I was missing an equals sign. The request now completes but ive just discovered that it doesnt update any of the database entries.
ANyone got any ideas why? I can repost my code if necessary.

[djjjozsi]

and how many rows you expect from the database?

using while assums that you will hae several rows where WD='$DayWeek'

In postrequest file $DayWeek is undefined! You did not passed with the form using a hidden field.

before you run a query, print it, and see if all the fields in it.

one suggestion: store the field names and labels in an array, and use forech to print the form, and use that array to build the UPDATE SQL query.

hello, jjozsi.

[kjdix]

Ok im not sure about everything you have said but I changed my postrequest code to define $DayWeek

$time=date("Y-m-d H:i:s");
$DayWeek=mysql_real_escape_string($_POST['DayWeek']);
$day=mysql_real_escape_string($_POST['day']);
$week=mysql_real_escape_string($_POST['week']);
$btitle=mysql_real_escape_string($_POST['btitle']);
$bopt1=mysql_real_escape_string($_POST['bopt1']);
$bopt2=mysql_real_escape_string($_POST['bopt2']);
$bopt3=mysql_real_escape_string($_POST['bopt3']);
$bopt4=mysql_real_escape_string($_POST['bopt4']);
$bopt5=mysql_real_escape_string($_POST['bopt5']);
$bdesert=mysql_real_escape_string($_POST['bdesert']);
$ltitle=mysql_real_escape_string($_POST['ltitle']);
$lopt1=mysql_real_escape_string($_POST['lopt1']);
$lopt2=mysql_real_escape_string($_POST['lopt2']);
$lopt3=mysql_real_escape_string($_POST['lopt3']);
$lopt4=mysql_real_escape_string($_POST['lopt4']);
$lopt5=mysql_real_escape_string($_POST['lopt5']);
$lopt6=mysql_real_escape_string($_POST['lopt6']);
$ldesert=mysql_real_escape_string($_POST['ldesert']);
$stitle=mysql_real_escape_string($_POST['stitle']);
$sopt1=mysql_real_escape_string($_POST['sopt1']);
$sopt2=mysql_real_escape_string($_POST['sopt2']);
$sopt3=mysql_real_escape_string($_POST['sopt3']);
$sopt4=mysql_real_escape_string($_POST['sopt4']);
$sopt5=mysql_real_escape_string($_POST['sopt5']);
$sopt6=mysql_real_escape_string($_POST['sopt6']);
$sdesert=mysql_real_escape_string($_POST['sdesert']);
$query="UPDATE meals SET btitle='$btitle', bopt1='$bopt1', bopt2='$bopt2', bopt3='$bopt3', bopt4='$bopt4', bopt5='$bopt5', bdesert='$bdesert', ltitle='$ltitle', lopt1='$lopt1', lopt2='$lopt2', lopt3='$lopt3', lopt4='$lopt4', lopt5='$lopt5', lopt6='$lopt6', ldesert='$ldesert', stitle='$stitle', sopt1='$sopt1', sopt2='$sopt2', sopt3='$sopt3', sopt4='$sopt4', sopt5='$sopt5', sopt6='$sopt6', sdesert='$sdesert' WHERE WD='$DayWeek'";

$result=mysql_query($query);
echo mysql_error();

Is that right? It still didnt update.
It should only return 1 row and modify all or some of the fields within that row.
Thankyou for your help.

[djjjozsi]

but dayweek did not set in your original FORM.

define that like:

echo "<input type=\"hidden\" value=\"$DayWeek\">";

[kjdix]

I have put in but still doesnt work.
So my original form looks like this

if ($DayWeek=="") {
	echo "<table><tr><td><h1>Menu Administration</h1>";
	echo "<p>Please choose a Week and Day: ";
	echo "<form action=menuadmin2.php><select name=DayWeek>
  <option>Sunday 1<option>Monday 1
  <option>Tuesday 1<option>Wednesday 1
  <option>Thursday 1<option>Friday 1
  <option>Saturday 1<option>Sunday 2<option>Monday 2
  <option>Tuesday 2<option>Wednesday 2
  <option>Thursday 2<option>Friday 2
  <option>Saturday 2<option>Sunday 3
  <option>Monday 3<option>Tuesday 3
  <option>Wednesday 3<option>Thursday 3
  <option>Friday 3<option>Saturday 3
  </select><input type=submit value=Go>";
	die();
} else {

echo '<table width="1500"><tr><td>';
$query="select * from meals where WD='$DayWeek' ";
	$result=mysql_query($query);
	echo mysql_error();
if ($result!="") {
	while ($row=mysql_fetch_array($result)) {
echo '<p><h1>Change the '.$DayWeek.' menu</h1>';
echo '<form action="postrequest.php" method="post">';
echo '<p>Breakfast Title: <input type="text" name="btitle" value="'.$row['btitle'].'">';
echo '<p>Breakfast Option 1: <input type="text" name="bopt1" value="'.$row['bopt1'].'">';
echo '<p>Breakfast Option 2: <input type="text" name="bopt2" value="'.$row['bopt2'].'">';
echo '<p>Breakfast Option 3: <input type="text" name="bopt3" value="'.$row['bopt3'].'">';
echo '<p>Breakfast Option 4: <input type="text" name="bopt4" value="'.$row['bopt4'].'">';
echo '<p>Breakfast Option 5: <input type="text" name="bopt5" value="'.$row['bopt5'].'">';
echo '<p>Breakfast Desert: <input type="text" name="bdesert" value="'.$row['bdesert'].'">';
echo '<p>Lunch Title: <input type="text" name="ltitle" value="'.$row['ltitle'].'">';
echo '<p>Lunch Option 1: <input type="text" name="lopt1" value="'.$row['lopt1'].'">';
echo '<p>Lunch Option 2: <input type="text" name="lopt2" value="'.$row['lopt2'].'">';
echo '<p>Lunch Option 3: <input type="text" name="lopt3" value="'.$row['lopt3'].'">';
echo '<p>Lunch Option 4: <input type="text" name="lopt4" value="'.$row['lopt4'].'">';
echo '<p>Lunch Option 5: <input type="text" name="lopt5" value="'.$row['lopt5'].'">';
echo '<p>Lunch Option 6: <input type="text" name="lopt6" value="'.$row['lopt6'].'">';
echo '<p>Lunch Desert: <input type="text" name="ldesert" value="'.$row['ldesert'].'">';
echo '<p>Supper Title: <input type="text" name="stitle" value="'.$row['stitle'].'">';
echo '<p>Supper Option 1: <input type="text" name="sopt1" value="'.$row['sopt1'].'">';
echo '<p>Supper Option 2: <input type="text" name="sopt2" value="'.$row['sopt2'].'">';
echo '<p>Supper Option 3: <input type="text" name="sopt3" value="'.$row['sopt3'].'">';
echo '<p>Supper Option 4: <input type="text" name="sopt4" value="'.$row['sopt4'].'">';
echo '<p>Supper Option 5: <input type="text" name="sopt5" value="'.$row['sopt5'].'">';
echo '<p>Supper Option 6: <input type="text" name="sopt6" value="'.$row['sopt6'].'">';
echo '<p>Supper Desert: <input type="text" name="sdesert" value="'.$row['sdesert'].'">';
echo '<p><input type="submit" value="Submit">';
echo '</td>';
echo '<input type=\"hidden\" value=\"$DayWeek\">';
}
}

and my postrequest looks like this

$time=date("Y-m-d H:i:s");
$DayWeek=mysql_real_escape_string($_POST['DayWeek']);
$day=mysql_real_escape_string($_POST['day']);
$week=mysql_real_escape_string($_POST['week']);
$btitle=mysql_real_escape_string($_POST['btitle']);
$bopt1=mysql_real_escape_string($_POST['bopt1']);
$bopt2=mysql_real_escape_string($_POST['bopt2']);
$bopt3=mysql_real_escape_string($_POST['bopt3']);
$bopt4=mysql_real_escape_string($_POST['bopt4']);
$bopt5=mysql_real_escape_string($_POST['bopt5']);
$bdesert=mysql_real_escape_string($_POST['bdesert']);
$ltitle=mysql_real_escape_string($_POST['ltitle']);
$lopt1=mysql_real_escape_string($_POST['lopt1']);
$lopt2=mysql_real_escape_string($_POST['lopt2']);
$lopt3=mysql_real_escape_string($_POST['lopt3']);
$lopt4=mysql_real_escape_string($_POST['lopt4']);
$lopt5=mysql_real_escape_string($_POST['lopt5']);
$lopt6=mysql_real_escape_string($_POST['lopt6']);
$ldesert=mysql_real_escape_string($_POST['ldesert']);
$stitle=mysql_real_escape_string($_POST['stitle']);
$sopt1=mysql_real_escape_string($_POST['sopt1']);
$sopt2=mysql_real_escape_string($_POST['sopt2']);
$sopt3=mysql_real_escape_string($_POST['sopt3']);
$sopt4=mysql_real_escape_string($_POST['sopt4']);
$sopt5=mysql_real_escape_string($_POST['sopt5']);
$sopt6=mysql_real_escape_string($_POST['sopt6']);
$sdesert=mysql_real_escape_string($_POST['sdesert']);
$query="UPDATE meals SET btitle='$btitle', bopt1='$bopt1', bopt2='$bopt2', bopt3='$bopt3', bopt4='$bopt4', bopt5='$bopt5', bdesert='$bdesert', ltitle='$ltitle', lopt1='$lopt1', lopt2='$lopt2', lopt3='$lopt3', lopt4='$lopt4', lopt5='$lopt5', lopt6='$lopt6', ldesert='$ldesert', stitle='$stitle', sopt1='$sopt1', sopt2='$sopt2', sopt3='$sopt3', sopt4='$sopt4', sopt5='$sopt5', sopt6='$sopt6', sdesert='$sdesert' WHERE WD='$DayWeek'";

$result=mysql_query($query);
echo mysql_error();

What am i missing?

[djjjozsi]

hmm.

i forget to mentioned the name tag in the hidden form:

echo '<input type=\"hidden\" name=\"DayWeek\" value=\"$DayWeek\">';

And a dropdown list looks like this:

  <select name="select_name">
    <option value="a">a</option>
    <option value="b">b</option>
  </select>

[kjdix]

Thats done the trick.
This prob is now resolved.
Thankyou very much for your help.

[djjjozsi]

and i seuggested to build an array to build the form, like this:

<?php

function field_echo($label , $var , &$row){
	echo "<p> $label :\t<input type=\"text\" name=\"$var\" value=\"";
	echo (isset($row[$var]))? htmlspecialchars($row[$var]):"" ;

	echo "\"></p>\r";

}

$DayWeek=1;  /* JUST FOR TEST ONLY*/
$row["btitle"]="  btitle is    t\" \" \" \" value!!!";  /* JUST FOR TEST ONLY*/

$c[]=array("Breakfast Title","btitle");
$c[]=array("Breakfast Option 1","bopt1");
$c[]=array("Breakfast Option 2","bopt2");
/* And build the others with the same logic...*/

echo "<form method=\"POST\" action=\"form.php\">";

	foreach($c AS $r)
	{
	   	field_echo($r[0] , $r[1] , $row );
	}

echo "<input type=\"hidden\" value=\"$DayWeek\">";
echo "<input type=\"submit\" name=\"submit\"value=\"Submit\">";

echo "</form>";
?>

And how to build the UPDATE query with a better way:

<?php
/* include the built array */


function add_q(&$sets , $var){
	$value=(isset($_POST[$var])? mysql_real_escape_string($_POST[$var]) : NULL  );
	return " SET {$var}='{$value}' ,";
}

/*Create the update!*/
$qu="UPDATE meals ";
$sets="";

	foreach($c AS $r)
	{
	$sets.=   	add_q($sets , $r[1] );
	}
$DayWeek=mysql_real_escape_string($DayWeek);
$query=$qu . $sets . "WHERE WD='$DayWeek'";
$query=str_replace(",WHERE"," WHERE",$query);  /* remove the last comma */
print "<br />Query IS: <br />$query";


?>

jjozsi.