迭代多个PHP变量，为每个SQL语句分配值

Question

so been searching around and not sure what to look for regarding my question. Basically, I am wanting to do what I have going on in my code already (I have the ingredient variables hardcoded), but instead create a single loop to iterate through and assign all available ingredients to the corresponding variable and update my SQL record accordingly. What's the best way to go about this?

I want to make it more efficient, so I don't end up with empty columns in my SQL DB, instead leave it as null in the database if the ingredient strings are empty in the URL API, which consists of ingredients.php?ingredient[]=apple&ingredient[]=pepper&ingredient[]=.....

$ingredients = $_GET['ingredient'];
    $ingredient1 = $ingredients[0];
    $ingredient2 = $ingredients[1];
    $ingredient3 = $ingredients[2];
    $ingredient4 = $ingredients[3];
    $ingredient5 = $ingredients[4];
    $ingredient6 = $ingredients[5];
    $ingredient7 = $ingredients[6];
    $ingredient8 = $ingredients[7];
    $ingredient9 = $ingredients[8];
    $ingredient10 = $ingredients[9];
    $ingredient11 = $ingredients[10];
    $ingredient12 = $ingredients[11];
    $ingredient13 = $ingredients[12];
    $ingredient14 = $ingredients[13];
    $ingredient15 = $ingredients[14];
$wait_time = $_GET['wait_time'];

$query = "INSERT INTO menu_items (rest_id,item_name,item_genre,item_price,item_descript,ingredient1,ingredient2,ingredient3,ingredient4,ingredient5,ingredient6,ingredient7,ingredient8,ingredient9,ingredient10,ingredient11,ingredient12,ingredient13,ingredient14,ingredient15,wait_time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

$stmt =  $mysqli->prepare($query);
    if ($stmt) {
        $stmt->bind_param('issdssssssssssssssssi',$rest_id,$item_name,$item_genre,$item_price,$item_descript,$ingredient1,$ingredient2,$ingredient3,$ingredient4,$ingredient5,$ingredient6,$ingredient7,$ingredient8,$ingredient9,$ingredient10,$ingredient11,$ingredient12,$ingredient13,$ingredient14,$ingredient15,$wait_time);
        $stmt->execute();
            echo json_encode(array('itemID' => $mysqli->insert_id, 'error' => $mysqli->error));
        $stmt->close();
    } else {
        echo json_encode(array('itemID' => null, 'error' => $mysqli->error));
    }

Answer 1

since you have a separate table with ingredients, you can create a simple junction table. Here is an example of design

id |  menuId | ingredientId
----------------------------
 1 |    1    |     2
 2 |    1    |     3

where menuId is a FK to the menu table and ingredientId is a FK to the ingredients db. The above example means that you have a menu with Id, which also contains ingredient 2 and 3. The id column is optional. Feel free to remove it if you don't need it.

Then, set your select box for ingredients that it has the next HTML result

<option value="2">pepper</option>
<option value="3">beef</option>

You can get this by selecting both id and name from the ingredients table, and use the id as value field.

Now, at your PHP, send only the items values which are filled in. (so do not send 15 if 4 got chosen. Just send 4)

$ingredients = $_GET['ingredient'];
// insert menu item (not sure if it's a part of the ajax ...)
$newMenuQuery = "INSERT INTO menu_items (rest_id,item_name,item_genre,item_price,item_descript,wait_time) VALUES (?,?,?,?,?,?)";
// query insert for junction table
$newIngredients = "INSERT INTO menuHasIngredients VALUES (?, ?)";

// prepare statement menu
$stmtMenu = $db->prepare($newMenuQuery);

// insert menu
$stmtMenu->bind_param('issdsi',$rest_id,$item_name,$item_genre,$item_price,$item_descript,$wait_time);
$stmtMenu->execute();
// get inserted id (insert_id gives last id given back
$newId = $db->insert_id;
// close connection
$stmtMenu->close();

// check if success
if($newId) {
    // prepare statement ingredients
    $stmtIngredients = $db->prepare($newIngredients);
    // use junction table
    $count = count($ingredients);
    for($i = 0; $i < $count; $i++) {
        $stmtIngredients->bind_param('ii', $newId, $ingredients[$i]);
        $stmtIngredients->execute();
    }
    // handle return good or error

    // close connection
    $stmtIngredients->close();
}
else {
   // send error back that menu inserting has failed
}
// close connection

please check the bindparam string notation at both queries. Might have missed or entered incorrect character.

However, if you have used PDO, things would be more easier. But you should be fine with the above.