Rows in the query can be one or many (up to a 100 rows), the update query is working fine if the query output has only one row of data, but when the output has many rows of data, only the very last of the selected rows will be updated, what am I missing?
Some background: Employee needs to be moved from one location to another, the insert query writes the data in the database, but leaving the RECEIPT date blank. Many employees can be released from one location to another... This happens on a different form and is working perfectly.
Now the recipient must accept these employees, the update query runs in the background after a submit form and all it does, is enter today's date ($d) in the $move_receipt_date field. But as I mentioned earlier, only the very last row is effected in my update query...
Here is the select query that gets the data and the update query, pretty straight forward:
<?php
include("../../xxx.xxx");
$cxn = mysqli_connect($host,$user,$password,$dbname)
or die ("Couldn't connect to server.");
$query = "SELECT `move_id`,`empl_no`,`empl_jc_code` AS old_jc_code,`new_jc_code`,`move_date`,`move_receipt_date`,`move_reason`
FROM `empl_movement`
WHERE `new_jc_code` = '$empl_jc_code' AND `move_receipt_date` = 0";
$result = mysqli_query($cxn,$query)
or die ("Couldn't execute query. "
.mysqli_error($cxn));
echo "<table><br>
<tr>
<th>Move ID</th>
<th>Employee No</th>
<th>Old JC Code</th>
<th>New JC Code</th>
<th>Release Date</th>
<th>Receipt Date</th>
<th>Reason for Move</th>
</tr>";
while($row = mysqli_fetch_assoc($result))
{
extract($row);
echo "<tr>
<td>$move_id</td>
<td>$empl_idno</td>
<td>$old_jc_code</td>
<td>$new_jc_code</td>
<td>$move_date</td>
<td>$move_receipt_date</td>
//this is the field that will be updated
<td>$move_reason</td>
</tr>
";
}
echo "</table><br>"; ?>
<?php
include("../../xxx.xxx");
$cxn = mysqli_connect($host,$user,$password,$dbname)
or die ("Couldn't connect to server.");
$query = "UPDATE `empl_movement` SET `move_receipt_date` = '$d'
WHERE `move_id` = '$move_id'";
$result = mysqli_query($cxn,$query)
or die ("Couldn't execute query. "
.mysqli_error($cxn));
?>