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();