duangu4943
duangu4943
2014-01-31 21:44

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

已采纳

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));
    }
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dqo58772 dqo58772 7年前

    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.

    点赞 评论 复制链接分享
  • douyan6871 douyan6871 7年前

    According to the documentation, you can not dynamically use bind_param, maybe why I thought MySQLi was obsolete :D The only solution is to use call_user_func_array():

    call_user_func_array(array($stmt, 'bind_param'), array_unshift('issdssssssssssssssssi', $_GET['ingredient']));
    

    I am not pro bind_param, i don't recommend it instead of checking it by yourself, i don't recommend MySQLi (but this is better than mysql_* functions).

    But in fact, you should never affect values from an array to a lot of variables. Moreover, you should always check user input, you can NOT trust any input variables, even administrators (they make mistakes).

    点赞 评论 复制链接分享

相关推荐