库存系统多个表和配方

Question

I'm a novice in PHP and extremely new to databases, so forgive me if this is a stupid question:

I've built an inventory system in PHP/MySQL that allows someone to (among other things) add raw materials to a table, that has "Price_Per_Pound_In_Dollars" and "Pounds_In_Stock" columns. As it stands, updating the "Pounds-In-Stock" column for a raw material must be done manually, one-at-a-time via a form.

I'm now wanting to add another database for recipes that contains the steps needed for making a product, so that the "Pounds_In_Stock" column can be updated for multiple raws at once. Please see the image below.

Basically someone would go to a form, pick a product from a drop down menu (populated with recipe names by doing a mysqli_query), enter the number of gallons they want to make and press submit.

The rows in the recipe table have the figures needed for making a gallon of that product, so all of the numbers in the table would be multiplied by the number that was entered into the form.

So, if someone were to choose "Recipe Name 1" and entered "2" into the gallons input box. It would multiply the "Pounds_Needed_Per_Gallon" by 2 and put that info into a mysqli_query to remove it from the correct raw "Pounds_In_Stock" column in the "Raws" table. (If that makes sense).

Now my question:

Is it possible to do this dynamically? Am I approaching this the wrong way? I know I can get the result I want by hard coding the values for each recipe in a PHP document and linking to it via the "action =" on the form page, but this seems like an exceedingly inelegant way of doing things. I just can't seem to wrap my head around how to do this with MySQL.

Form Issue:

I created a bare-bones form that should include the table names in a drop-down menu (so that a recipe table can be selected). But I can't seem to get the table names to echo out in the drop down. I know the while loop is working correctly, since there are three options in the drop-down menu, which corresponds to the number of tables in the database, the only problem is that they're blank. Please see the image and code below. I looked over my code a few times, but can't see where I made a mistake. Any ideas?

<?php

//Prepare MySQL Query.
$Product_Menu_Query ="SHOW TABLES"; 
$Run_Product_Menu_Query = mysqli_query($Connect, $Product_Menu_Query);
?>


<form action="submit.php" method="POST">
    <span>Recipe Name:</span><br>
    <select name="recipe">

        <?php  //Populates dropdown with Product_Name from database. 
        while ($row = mysqli_fetch_array($Run_Product_Menu_Query)) {
        echo '<option value="' . $row[0] . '">' . '</option>';
        }

    ?>
     </select><br><br>
    <span>Gallons:</span><br>
    <input type="number" name="gallons"><br><br>
    <input type="submit" value="Submit">
</form>

Screenshot of form:

Answer 1

looks like you would have something like this (change your table names and fields accordingly to your database actual field and table names):

$recipeName = $_POST['recipe'];
$gallons = $_POST['gallons'];

$stmt = $mysqli->prepare("SELECT raws.id as rawId, (recipe.pounds_needed_per_gallon * ?) as gallons
  FROM RecipeName recipe
  JOIN Raws raws on recipe.raw_id=raws.raw_id
  WHERE raws.raw_name = ?");

$stmt->bind_param("ds", $gallons, $recipeName);
$stmt->execute();
$stmt->bind_result($rawId, $gallonsNeeded);
$stmt->close();

mysqli_query("UPDATE Raws set pounds_in_stock = (pounds_in_stock - $gallonsNeeded) where id = $rawId");

EDIT: I see you need more help with debugging. You should check the values obtained through the queries (i.e. rawId and gallonsNeeded) and echo out errors, but as i don't have your entire code base I cannot tell exactly what is going wrong with your code.