PHP foreach in SQL statement

Asked Mar 04 '18 at 00:39

Active Mar 05 '18 at 02:26

Viewed 266 times

I'm creating a plugin that will have a variable amount of user input. I need a way to be able to loop through that variable amount in an SQL statement. Basically what I want is this

$sqlArray = [
  'loop_carousel' => 'on',
  'stop_on_hover' => 'on',
  'reverse_order' => '',
  'navigation_arrows' => 'on',
  'show_pagination' => 'on'
];

$sql = "UPDATE $table_name
        SET
        ".
        foreach ($sqlArray as $key => $value) {
          echo "$key = '$value'";
        }.
        ";";

Which would output a statement like

UPDATE wp_carousel
SET
loop_carousel = 'on',
stop_on_hover = 'on',
reverse_order = '',
navigation_arrows = 'on',
show_pagination = 'on';

But obviously, it fails. So what are my options?

edited Mar 04 '18 at 00:41

asked Mar 04 '18 at 00:39

Brandon Benefield

1,254
20
31

What `error message` does it returns? – User 328 6227 Mar 04 '18 at 00:41
Parse error: syntax error, unexpected 'foreach' (T_FOREACH) – Brandon Benefield Mar 04 '18 at 00:42
are you wanting to update the whole database? because that's what it's doing without a `WHERE` clause. – Funk Forty Niner Mar 04 '18 at 00:42
well there you go; you can't `foreach` inside an already executing statement. – Funk Forty Niner Mar 04 '18 at 00:43
Well this table is only going to hold one column for now. I just want to focus on handling this dynamic data first. Thanks for the heads up though. – Brandon Benefield Mar 04 '18 at 00:43
Besides the already marked duplicates, this one would also be a good one: https://stackoverflow.com/q/26062524 – Ivar Mar 04 '18 at 00:47
Use prepare statment and avoid loop for generating query. 1. It's execution time will increase 2. Will make your code more sensitive to SQL injection as well it can cause sql exception. So i suggest you to go with prepared statement then generating Query at run time. – Ronak Bokaria Mar 04 '18 at 09:10

PHP foreach in SQL statement

0 Answers0