madjaeg madjaeg - 1 month ago 13
MySQL Question

Dynamic Dropdown Displays Dynamic Content In a Div

I have an inventory list being stored in a MySQL database. I have a form that takes user input that updates stock changes, all on a table with an Item Name column with a dropdown full of items, and a (currently) blank

# In Stock
column.

Here is the code for my table:

<table id="updateStockTable">
<thead>
<tr>
<th>Item Name</th>
<th># In Stock</th>
</tr>
</thead>
<tbody>
<tr>
<td>
<select required>
<option></option>
<?php
$query = mysqli_query($db, "SELECT itemName FROM SOISInventoryList ORDER BY itemName");

if(!$query){
echo "Could not grab query.";
}

else{
while($row = mysqli_fetch_assoc($query)){
echo "<option>" . $row['itemName'] . "</option>";
}
}
?>
</select>
</td>
<td>
-
</td>
</tr>
</tbody>
</table>


The Item Name dropdown list is generated via the database. I would like, whenever a user selects an item from the dropdown, for that item's number in stock to appear in a div in the
# In Stock column
.

My issue is this: the
Item Name
dropdown list is generated via the database, so none of the items currently have a value, which is the easiest solution I've found when you want a dropdown selection to bring up content in a div. Using AJAX appears to be another possible solution, but I'm not even sure how to approach using it for the inventory list.

I'm not sure if either of these ideas are the proper or best solution, and if they are, how I would go about using either of them.

EDIT:

So I've decided to go with the AJAX method. I have a separate PHP file set up that simply returns the numbers in stock from the database, and now I'm attempting to use that in order to pull the number in stock I need for the main page. I've been told I need to use a change event to trigger each new value, and to use an AJAX request, but I don't know how to do this and would appreciate any help anyone can offer!

Answer

So here's the solution I came up with:

I setup a separate php file with a query that grabs the currently selected dropdown item and retrieves the corresponding number in stock value from the MySQL database:

$receivedString = null;

if ($_GET["item"] != ""){
    $receivedString = $_GET["item"];
}

if($receivedString != null){
    //Check connection
    $db = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to SOIS MySQL server with error: ' . mysqli_connect_error());

    $query = mysqli_query($db, "SELECT numInStock FROM SOISInventoryList WHERE id =  $receivedString");

    if (!$query){
        echo "Could not find query.";
    }

    while($row = mysqli_fetch_assoc($query)){
        echo $row['numInStock'];
    }
}

And then used jQuery's .change() function, so when the item in the drop down changes, I'm able to call the separate php file.

$(function(){
$('#itemNameDropDown').change(function(){
    var SelectedItem = $(this).val();
    //Sending the selected item value to the php file as JSON
    //Assuming that the php file is setup to consume $_GET variables
    $.get('grab-num-in-stock.php', {"item": SelectedItem})
    .done(function(returnedData){
    //Assuming just a string is returned right now
        $('.numInStockCol').text(returnedData);
    });
});

});

Comments