2

I have a dynamic table named 'products' with multiple Languages. The table columns looks like this:

id, product_id, store_de, store_en, store_fr, store_es... etc

The languages can be more or less.

Now I want to update this table and set all columns beginning with "store_" to value 1.

I tried the following:

$stmt = $dbh->prepare( "UPDATE products SET `store_%` = ? WHERE product_id = ?" );

$stmt->execute( array( 1, $lastID ) );

I get following error message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'store%' in 'field list'

Is there a way to update all columns beginning with 'store_' or do I have to list all the columns?

Based on the answer from jekaby here is the full solution that worked for me:

$get_stores = $dbh->prepare("SHOW COLUMNS FROM products_active WHERE field REGEXP '^store'");
    $get_stores->execute();
    $stores = $get_stores->fetchAll();

    $update_string = "";
    $first_store = true;
    foreach($stores as $store) {
        if(!$first_store) {

            $update_string .= ", ";

        } else {

            $first_store = false;

        }
        $update_string .= $store['Field']." = '".$status."'";

    }

    $update_activity = $dbh->prepare("UPDATE products_active SET $update_string WHERE product_id = ?");
    $update_activity->execute(array($product_id));
Cœur
  • 37,241
  • 25
  • 195
  • 267
Jaycaponex
  • 65
  • 7
  • 1
    You can go into INFORMATION_SCHEMA,get all the desired columns names from there and build the queries with that info.But this is even more complicated than doing it manually. – Mihai Mar 30 '16 at 11:17
  • The Problem is, that the languages are user generated. So if the user adds a language the query needs to change automaticly when the table changes. – Jaycaponex Mar 30 '16 at 11:22

2 Answers2

5

You need to set each column explicitly. SQL doesn't support wildcards for column names (with the exception of * for SELECT *):

update products
    set store_de = 1,
        store_en = 1,
        store_fr = 1,
        store_es = 1,
        . . .
    where product_id = ?;

Your data structure suggests that you really want a ProductStores table. This would have one row per language (?) and per product. It would have at least three columns:

  • ProductId
  • Language
  • Value

Then you would simply do:

update ProductStores
    set Value = 1
    where ProductId = ?;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The table above is only for product status. It handles active or inactive product status in stores. But I have a 'languages table' so I could build the query from this table? So two ways are possible I dynamicly build a String like: $culumns = "store_de = 1, store_en = 1, etc"; "UPDATE products SET `$columns` WHERE product_id = ?" Or I can update each language with a foreach or while loop? – Jaycaponex Mar 30 '16 at 11:29
  • Thanks for the answer. :) It wasn't the solution that helped me but it was the correct answer to my question. – Jaycaponex Mar 30 '16 at 11:58
3

You can not do like this store_% - it is nonexistent column in table as it is written in your error.

You should get all colums with names like /^store_/ (regexp). And than update all this fields having listed them.

mysql> SHOW COLUMNS FROM products where field REGEXP '^store_'

Than collect all fields from 'Field'... You can read more how to get all columns here.

Community
  • 1
  • 1
jekaby
  • 403
  • 3
  • 13