Hi I have the following code:
$sql_cadets = "SELECT cadet_unique FROM cadets";
$query_cadets = mysqli_query($db,$sql_cadets);
$sql_assets = "SELECT * FROM assets";
$query_assets = mysqli_query($db,$sql_assets);
while ($row_assets = mysqli_fetch_array($query_assets)) {
$assigned = false;
while ($row_cadets = mysqli_fetch_array($query_cadets)) {
if (($row_assets['asset_assigned_to'] == '') && ($assigned == false)) {
$assigned = true;
$query = $connection->prepare("INSERT INTO `assignations` (`asset_unique`, `assigned_to`, `assigned_for`, `date_assigned`,
`date_expected`) VALUES (?, ?, ?, NOW(), ?)");
$query->bind_param("ssss",$row_assets['asset_unique'],$row_cadets['cadet_unique'],$_GET['evtid'],$_GET['dateexpected']);
$query->execute();
$query->close();
$query = $connection->prepare("UPDATE `assets` SET `asset_assigned_to` = ?, `asset_assigned_for` = ?, `asset_assigned_date` = NOW()
WHERE `asset_unique` = ?");
$query->bind_param("sss", $row_cadets['cadet_unique'], $_GET['evtid'],$row_assets['asset_unique']);
$query->execute();
$query->close();
}
}
}
?>
It is intended to loop through each asset in a database, then for each asset loop through each cadet and assign that cadet that asset if it has not already been assigned. For some reason, the PHP page does insert into the assignations table, but with the same asset and two different cadets. I looked this up on google and stack overflow, and I found a common problem which is that mysqli_fetch_array should be looping and not there only once, but I already have that fixed and its still failing. When it puts these into the table, what will happen is that after that it should update the assets table. I have three assets that are named TSK10L, TSK11L and TSK13L as their asset_unique fields, but the system only assigns TSK10L to both defined cadets in the assignations table, but only does one cadet for one asset. I have looked extensively for answers, but no-one seems to be having a problem like mine. I'm trying to design an intranet solution for the local Naval Cadet Unit who is in desperate need of one.
