CODEWITHSUNDEEP
phpmysql
jflay
jflay

Reputation: 514

MySQL Issues Inserting Multiple Rows with One Insert Statement

Ok, I realize there are similar topics on this, many in fact, but none of them are helping me figure out my issue and it just seems to lead me farther and farther from my goal. I have tried implode, for each, loops, etc., but either I receive "Query was empty" because I am passing empty array or else I get a syntax error.

I am using mysqli prepared statements and here is what I have tried working with with no luck. Very stumped. Basically, I am inserting potentially many ingredient rows passed from an API I have built:

API URL string

menu_item.php?ingredient_name[]=bacon&ingredient_price[]=1.00&ingredient_default[]=0&ingredient_name[]=cheese&ingredient_price[]=0&ingredient_default[]=1

PHP

// set arrays, item_id, foreign key, already set from previous query
    $ingredient_name = $_GET['ingredient_name'];
    $ingredient_price = $_GET['ingredient_price'];
    $ingredient_default = $_GET['ingredient_default'];
    // define arrays
    $ingredients = array(
        'ingredient_name' => $ingredient_name,
        'ingredient_price' => $ingredient_price,
        'ingredient_default' => $ingredient_default
    );
    $insertQuery = array();
    $insertData = array();
    // set array length
    $len = count($ingredients);
    /**
    prepare the array values for mysql
    **/
    // prepare values to insert recursively
    $ingQuery = "INSERT INTO TABLE (column1,column2,column3,column4) VALUES ";
    // set placeholders
    foreach ($ingredients as $row) {
        $insertQuery[] = '(?,?,?,?)';
    }
    // iterate through all available data
    for( $i=0;$i<$len;$i++ ) {
        $insertData[] = $ingredients['ingredient_name'][$i];
        $insertData[] = $ingredients['ingredient_price'][$i];
        $insertData[] = $ingredients['ingredient_default'][$i];
    }
    // set ingredient value placeholders
    $ingQuery .= implode(', ', $insertQuery);
    // prepare statement ingredients
    $ingStmt = $mysqli->prepare($sql);
    // run the query
    if( $ingStmt ) {
        $ingStmt->execute($insertData);
    } else {
        // handle error return
        echo json_encode(array('error' => $mysqli->error.__LINE__));
        echo json_encode($insertData);
    }

For now ignore all that maybe I need to start over. Any suggestions? Currently I am receiving Query Was Empty error... I need to do this a better, more efficient way, but I am scratching my head on this!

EDIT (still in progress)

// the query
    $ingQuery = "INSERT INTO table (column1,column2,column3,column4) VALUES (?,?,?,?)";
    // prepare statement
    $ingStmt = $mysqli->prepare($ingQuery);
    if( $ingStmt ) {
        // iterate through all available data
        for( $i=0;$i<count($_GET['ingredient_name']);$i++ ) {
            $ingStmt->execute(array($item_id,$_GET['ingredient_name'][$i],$_GET['ingredient_price'][$i],$_GET['ingredient_default'][$i]));
        }
    } else {
        echo json_encode(array('error' => $mysqli->error.__LINE__));
    }
    $ingStmt->close();

Upvotes: 1

Views: 1909

Answers (2)

Sean
Sean

Reputation: 12433

First, you have 4 columns in your query - (column1,column2,column3,column4) VALUES (?,?,?,?), but you are only inserting 3 values $_GET['ingredient_name'], $_GET['ingredient_price'], $_GET['ingredient_default']

Second, unless you need to reuse your arrays/variables, why not simplify it down to the prepare and execute in a loop -

// the query
$ingQuery = "INSERT INTO TABLE (column1,column2,column3) VALUES (?,?,?)";
// prepare statement
$ingStmt = $mysqli->prepare($ingQuery);
// iterate through all available data
for( $i=0;$i<count($_GET['ingredient_name']);$i++ ) {
    $ingStmt->execute(array($_GET['ingredient_name'][$i], $_GET['ingredient_price'][$i], $_GET['ingredient_default'][$i]));
}

update
You need to use bind_param() when using mysqli - 

// the query
$ingQuery = "INSERT INTO TABLE (column1,column2,column3,column4) VALUES (?,?,?,?)";
// prepare statement
$ingStmt = $mysqli->prepare($ingQuery);
$ingStmt->bind_param("isss", $item_id, $val1, $val2, $val3);
// iterate through all available data
for( $i=0;$i<count($_GET['ingredient_name']);$i++ ) {
    $val1 = $_GET['ingredient_name'][$i];
    $val2 = $_GET['ingredient_price'][$i];
    $val3 = $_GET['ingredient_default'][$i];
    $ingStmt->execute();
}

Upvotes: 1

MrCarrot
MrCarrot

Reputation: 2778

This is incorrect

// set array length
$len = count($ingredients);

Your $ingredients array will always have 3 elements regardless. Try this instead:

// set array length
$len = count($ingredient_name);

Then, this is incorrect (there is no variable called $sql)

$ingStmt = $mysqli->prepare($sql);

It should be

$ingStmt = $mysqli->prepare($ingQuery);

Upvotes: 1

Related Questions