I have a page that shows inventory. When inventory becomes defective (or any other status) I would like to select that option from a dropdown, and update the database accordingly without a refresh of the page. I have been playing around with .ajax and jQuery and I think I am close I just cant get it to quite work properly.
First, here is the code that populates the dropdown. There is nothing wrong with this code (used in other places in my site). Just though I would include for name and id purposes
$myQuery2 = "SELECT InventoryItemStatus.InventoryItemStatusID, InventoryItemStatus.InventoryItemStatusDescription FROM InventoryItemStatus";
$myConnection = mysql_connect($myServer,$myUsername,$myPassword);
if (!$myConnection){
die('Conncetion Failed:' . mysql_error());
}
@mysql_select_db($myDatabase) or die("Unable to select database");
$result2 = mysql_query($myQuery2) or die (mysql_error());
if(mysql_num_rows($result2)){
$select2= '<select style="width:90px" id="StatusList" name="StatusList">';
$select2.='<option value="0.5"> --Status-- </option>';
while($rs=mysql_fetch_array($result2)){
$select2.='<option value="'.$rs['InventoryItemStatusID'].'">'.$rs['InventoryItemStatusDescription'].'</option>';
}
}
$select2.='</select>';
Here is the jQuery code I am using. The .change works okay. (If I replace the $.ajax method with an alert, when I change the dropdown, it alerts a popup)
<script type="text/javascript">
$(document).ready(function(){
$('[name="StatusList"]').change(function() {
var mydata = $('[name="StatusList"]').val();//$(this).val();
var inputdata = $('[name="StarmontInventoryItemID"]').val();
$.ajax({
type: 'POST',
url: 'update_status/update_starmont.php',
data: {StatusList:mydata ,StarmontInventoryItemID:inputdata}
});
});
});
</script>
Here is the form included in my php code. This includes a hidden field which includes the unique ID of the inventory item that I would like to change the status of, and the dropdown menu.
<form method='post'>
{$select2}
<input type='hidden' value='{$row14['InventoryItemID']}' name='StarmontInventoryItemID' id='StarmontInventoryItemID'/>
</form>
This is the php script that is called on change. I think this is the part that I am not using properly. I am just unclear on how the data is passed to this script. I cant figure out if it is passed in the $_POST variable or not.
include_once('../../../php/common.php');
include_once('../../../php/db.php');
$InventoryItemID = $_POST['StarmontInventoryItemID'];
//$InventoryItemStatusID = $_POST['StatusList'];
mysql_query("UPDATE InventoryItem SET InventoryItemStatusID = 2 WHERE InventoryItemID = '$InventoryItemID'") or die (mysql_error());
As you can see, I am simply trying to pass the inventory item id to the script and update that item to a status of '2'. My end result is to update it to whatever the value of the dropdown menu is, but I am sure if I can just get this part working I can figure out the rest.
Thank you in advance for any help! Bonne journée!
EDIT
This works perfectly thank you so much. I have run into another issue unfortunately. For example, I have 10 inventory items. I do complex query to retrieve all the information needed in an array, then I do a foreach. It seems like the inventory item at the top works correctly updating the status, but any item below it does not execute the sql query for an update.
if (sizeof($rows14) > 0) {
foreach($rows14 as $row14):
$query17 = "
SELECT
SystemID,
Serial
FROM
Device
WHERE
Device.InventoryItemID = :InventoryItemID
";
$query_params17 = array(
':InventoryItemID' => $row14['InventoryItemID']
);
try {
$stmt17 = $db->prepare($query17);
$stmt17->execute($query_params17);
}
catch(PDOException $ex) {
die("Failed to run query: " . $ex->getMessage());
}
$row17 = $stmt17->fetch();
$myQuery2 = "SELECT InventoryItemStatus.InventoryItemStatusID, InventoryItemStatus.InventoryItemStatusDescription FROM InventoryItemStatus";
$result2 = mysql_query($myQuery2) or die (mysql_error());
if(mysql_num_rows($result2)){
$select2= '<select style="width:90px" name="StatusList">';
while($rs=mysql_fetch_array($result2)){
if($rs['InventoryItemStatusID'] == $row14['Status']){
$select2.='<option value="'.$rs['InventoryItemStatusID'].'" selected="'.$row14['Status'].'">'.$rs['InventoryItemStatusDescription'].'</option>';
}
else{
$select2.='<option value="'.$rs['InventoryItemStatusID'].'">'.$rs['InventoryItemStatusDescription'].'</option>';
}
}
}
$select2.='</select>';
echo"
<tr class='tableRowClass2'>
<td height='20'>{$row14['OnHand']}</td>
<td height='20'>{$row14['ItemName']} - {$row17['SystemID']} - {$row17['Serial']}</td>
<td height='20'>
<form method='post'>
{$select2}
<input type='hidden' value='{$row14['InventoryItemID']}' name='StarmontInventoryItemID'/>
</form>
</td>
</tr>";
endforeach;
}
I have tried multiple different ways to get this to work with all of the inventory items. I am unsure if it is valid or not to put this within the foreach loop or not. Again any help is greatly appreciated!
