simple php MySql UPDATE ??

[7kemZmani]

I creat this form which contain dynamic table:

<form action="<?php echo $editFormAction;?>" method="post" name="form1">
<table cellspacing="0">
  <tr>
    <th>#</th>
    <th>ORDER DESC</th>
    <th>ORDERING DEPT</th>
    <th>ORDERING SERVICE</th>
    <th>STATUS</th>
    <th>SENDING DATE</th>
    <th>DELIVERING NO</th>
    <th>DELIVERING DATE</th>
    <th>COMMENT</th>
  </tr>
  <?php do { ?>
    <tr>
      <td><?php echo $row_medi['order_no']; ?></td>
      <td><?php echo $row_medi['ORDER_DESC']; ?></td>
      <td><?php echo $row_medi['ORDERING_DEPT']; ?></td>
      <td><?php echo $row_medi['ORDERING_SERVICE']; ?></td>
      <td><select name="mydropdown"><option value="Undelivered">Undelivered</option><option value="Delivered">Delivered</option></select></td>
      <td><?php $sqldate=date('d-m-Y',strtotime($row_medi['SENDING_DATE']));
	  			echo $sqldate; ?></td>
      <td><?php echo $row_medi['DELIVERING_NO']; ?></td>
      <td><?php $sqldate=date('d-m-Y',strtotime($row_medi['DELIVERING_DATE']));
	  			echo $sqldate; ?></td>
      <td><?php echo $row_medi['COMMENT']; ?></td>
    </tr>
    <?php } while ($row_medi = mysql_fetch_assoc($medi)); ?>
</table>
<input type="submit" value="Save" />
<input type="hidden" name="MM_insert" value="form1">
</form>

as you see in this table there is a dropdown menu I want to update the record with selected value and I'm using this code:

$editFormAction = $_SERVER['PHP_SELF'];
if ((isset($_GET["MM_insert"])) && ($_GET["MM_insert"] == "form1")) {
	$choice = mysql_real_escape_string($_REQUEST['mydropdown']);
	$insertSQL = "UPDATE orders SET STATUS = $choice";
mysql_select_db($database_PPS, $PPS);
  $Result1 = mysql_query($insertSQL, $PPS) or die(mysql_error());

  $insertGoTo = "Pharmacy.php?a=$pat_id";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

and this the code that retrieve the data (No Error in this code):

mysql_select_db($database_PPS, $PPS);
$query_medi = "SELECT order_no, ORDER_TYPE, ORDER_DESC, ORDERING_DEPT, ORDERING_SERVICE, SENDING_DATE, DELIVERING_NO, DELIVERING_DATE, PRODUCT_CODE, MEDICINE_DOSES, STATUS, URGENCY_CODE, MEASUREMENT_UNIT, MEDICINE_ROUTE, COMMENT, pat_id FROM orders WHERE orders.pat_id=$pat_id AND orders.ORDER_TYPE='MED'";
$medi = mysql_query($query_medi, $PPS) or die(mysql_error());
$row_medi = mysql_fetch_assoc($medi);
$totalRows_medi = mysql_num_rows($medi);

when I rendering this page it displays the dynamic table fine but when selecting a value from any dropdown list then clicking save it gives this error msg: 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 'AND orders.ORDER_TYPE='MED'' at line 1 any help ??

[7kemZmani]

please help :(

[faroukmuhammad]

the problem is from $pat_id in the last part of the code $query_medi (i.e. line 2). is all your code in one file?

[7kemZmani]

yes the code in one file. I tried warping around pat_id with '' and the error disappear but the selected record doesn't updated ???

[IIM]

You have problem in updation or showing data from tables after updation using joins??
Instead of this:-

$insertSQL = "UPDATE orders SET STATUS = $choice";

Check this:-

$insertSQL = "UPDATE orders SET STATUS = '$choice'";

[7kemZmani]

Same problem :( no error msg and no update performed !!!!

[faroukmuhammad]

Remeber that your form is a Post and no a Get, as such line 2 of the second parcel of your code should be;

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

[datadiary]

it's really good and i got good infomation thanks and keep it up....