PHP MySQL insert array syntax

Question

I have a form where I input 2 values: sku and filter_id. I then take sku and retrieve from DB an array of product_id that correspond that sku. For each product_id I need to insert a query back into DB with 2 values: product_id and filter_id.

I would like to build the query in the foreach loop and run a single SQL query at the end for efficient use of resources.

Here is my PHP code:

// Escape user inputs for security
$sku = mysqli_real_escape_string($db, $_POST['sku']);
$filter_id = mysqli_real_escape_string($db, $_POST['filter_id']);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
} 

//get product_id array for requested sku
$sql = "SELECT product_id FROM oc_product WHERE sku = '$sku'";
$product_id_array = $db->query($sql);

if ($product_id_array->num_rows > 0) {
    foreach( $product_id_array as $row ) {
    $query_row[] = "('$row['product_id']','$filter_id')";
    }

    $final_query = "INSERT IGNORE INTO oc_product_filter (product_id, filter_id) VALUES " .implode(',', $query_row);
    $db->query($final_query);
} else {
    echo "no products found.";
}

Current error is:

Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)

That is for this line: $query_row[] = "('$row['product_id']','$filter_id')";

Can anyone help me with the correct syntax to use? Thank you!

Is `$query_row[] = "('".$row['product_id']."','".$filter_id."')";` working? — mario.van.zadel, Sep 08 '15 at 11:11

Narendrasingh Sisodia · Accepted Answer · 2015-09-08T11:14:35.850

2

Update your code

$query_row[] = "('$row['product_id']','$filter_id')";
                 ^^                 ^^

into

$query_row[] = "('{$row['product_id']}','$filter_id')";
                 ^^                  ^^

You need to enclose your array variables within {}

edited Sep 08 '15 at 11:14

answered Sep 08 '15 at 11:07

Narendrasingh Sisodia

21,247
6
47
54

Downvoters please post the reason behind downvoting so I can update my answer – Narendrasingh Sisodia Sep 08 '15 at 11:38
I didn't vote, so it wasn't me, but one possible reason for downvoting is that the whole thing could be better rewritten using a single query (ie an INSERT with an embedded SELECT), which would give performance benefits and also allow the code to be written as a parameterised query instead of the old-school method of using the escape functions and building the query string manually. – Simba Sep 08 '15 at 11:47
@Simba I'm always open to such improvements and what you're suggesting was initially my idea but I was unable to find syntax examples for doing combined INSERT and SELECT queries. Can you rewrite my example using this better method? – Florin C. Sep 08 '15 at 15:39
1

@FlorinC. Something like this: `$db->prepare("INSERT IGNORE INTO oc_product_filter (product_id, filter_id) SELECT product_id, :filter_id FROM oc_product WHERE sku = :sku", ['filter_id'=>$filter_id, 'sku'=>$_POST['sku']); $db->execute();` – Simba Sep 08 '15 at 15:46
(sorry for the formatting and/or any typos -- the comments don't make for the easiest way of presenting code) – Simba Sep 08 '15 at 15:46
But anyway, that couple of lines there should be able to replace most of the code in your original question. – Simba Sep 08 '15 at 15:47
See also http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from – Simba Sep 08 '15 at 15:47

score 0 · Answer 2 · answered Sep 08 '15 at 11:11

0

Change from

$query_row[] = "('$row['product_id']','$filter_id')";

to

 $query_row[] = "('".$row['product_id']."','".$filter_id."')";

answered Sep 08 '15 at 11:11

Happy Coding

2,517
1
13
24

Your solution works ok but @Uchiha answered first so I'm going to accept his solution. Thank you. – Florin C. Sep 08 '15 at 11:19

PHP MySQL insert array syntax

2 Answers2