Convert concat sql string to INSERT INTO SELECT

Question

I have been looking for a way to convert this query / php to an INSERT INTO SELECT, but i can´t find a way to add values to the concat part of the query, any suggestions

$query = tep_db_query("SELECT DISTINCT products_attributes.products_id AS products_id, GROUP_CONCAT(DISTINCT 'filter_size_', products_attributes.options_id, '_', filter_options.filter_options_id) AS size FROM products_attributes INNER JOIN filter_options_to_values ON products_attributes.options_id = filter_options_to_values.options_id AND products_attributes.options_values_id = filter_options_to_values.values_id INNER JOIN filter_options ON filter_options_to_values.filter_options_id = filter_options.filter_options_id GROUP BY products_attributes.products_id;");
tep_db_query("TRUNCATE table " . $tableName);

while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
    $array = explode(',', $row['size']);
    $count = count($array);
    $string = '';
    foreach ($array as $a) {
        $string .= '1,';
    }
    $string = rtrim($string, ',');

    tep_db_query("INSERT INTO " . $tableName . " (products_id, " . $row['size'] . ") VALUES (" . $row['products_id'] . ", " . $string . ")");
}

This is what i am trying for the insert into but the problem is the dynimic cols size :

SELECT DISTINCT products_attributes.products_id, filter_options_to_values.filter_options_id, GROUP_CONCAT(DISTINCT 'filter_size_', products_attributes.options_id, '_', filter_options.filter_options_id) AS size, GROUP_CONCAT(DISTINCT '1') AS vals FROM products_attributes INNER JOIN filter_options_to_values ON products_attributes.options_id = filter_options_to_values.options_id AND products_attributes.options_values_id = filter_options_to_values.values_id INNER JOIN filter_options ON filter_options_to_values.filter_options_id = filter_options.filter_options_id WHERE products_attributes.options_quantity > 0 GROUP BY products_attributes.products_id

This is the schema:

CREATE TABLEproducts_temp(products_idint(11) NOT NULL default '0',final_pricedecimal(15,4) default NULL,special_pricedecimal(15,4) default NULL,manufacturers_idint(11) default NULL,efv4int(11) default NULL,efv5int(11) default NULL,efv10int(11) default NULL,products_date_addeddatetime default NULL,filter_size_2_1int(11) default NULL,filter_size_2_2int(11) default NULL,filter_size_2_3int(11) default NULL,filter_size_2_4int(11) default NULL,filter_size_2_5int(11) default NULL,filter_size_2_6int(11) default NULL,filter_size_2_7int(11) default NULL,filter_size_2_8int(11) default NULL,filter_size_2_9int(11) default NULL,filter_size_2_10int(11) default NULL,filter_size_2_11int(11) default NULL,filter_size_2_12int(11) default NULL,filter_size_2_57int(11) default NULL,filter_size_2_58int(11) default NULL,filter_size_2_59int(11) default NULL,filter_size_2_60int(11) default NULL,filter_size_2_62int(11) default NULL,filter_size_2_63int(11) default NULL,filter_size_2_293int(11) default NULL,filter_size_2_294int(11) default NULL,filter_size_2_295int(11) default NULL,filter_size_2_296int(11) default NULL,filter_size_9_20int(11) default NULL,filter_size_9_21int(11) default NULL,filter_size_9_24int(11) default NULL,filter_size_9_25int(11) default NULL,filter_size_9_26int(11) default NULL,filter_size_9_27int(11) default NULL,filter_size_9_28int(11) default NULL,filter_size_9_29int(11) default NULL,filter_size_9_30int(11) default NULL,filter_size_9_31int(11) default NULL,filter_size_9_32int(11) default NULL,filter_size_9_33int(11) default NULL,filter_size_9_34int(11) default NULL,filter_size_9_35int(11) default NULL,filter_size_9_36int(11) default NULL,filter_size_9_37int(11) default NULL,filter_size_9_38int(11) default NULL,filter_size_9_39int(11) default NULL,filter_size_9_40int(11) default NULL,filter_size_9_41int(11) default NULL,filter_size_9_42int(11) default NULL,filter_size_9_43int(11) default NULL,filter_size_9_44int(11) default NULL,filter_size_9_45int(11) default NULL,filter_size_9_46int(11) default NULL,filter_size_9_47int(11) default NULL,filter_size_9_48int(11) default NULL,filter_size_9_49int(11) default NULL,filter_size_9_50int(11) default NULL,filter_size_9_51int(11) default NULL,filter_size_9_52int(11) default NULL,filter_size_9_53int(11) default NULL,filter_size_9_54int(11) default NULL,filter_size_9_55int(11) default NULL,filter_size_9_56int(11) default NULL, PRIMARY KEY (products_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The code is a tad unclear. Can you provide some sample data and the result you'd like to get for it? It would help us help you. — Mureinik, Oct 03 '16 at 18:49

score 0 · Answer 1 · edited May 23 '17 at 10:34

Your SELECT statement has 4 fields. The table in which you are trying to insert has like 50. Your SELECT statement either needs to have 50 fields, or you need to change your temp table to 4 fields to match the results of your SELECT. My wager is that your schema for that table isn't so great and maybe the results of that SELECT are what you actually want in the table.
You are including filter_options_id in your SELECT so that should either be aggregated as well, or it should in the GROUP BY. Failure to do otherwise will mean that if there are multiple filter_options_id for each products_id your Database will just grab a random filter_options_id to include in the record. If there is only a single distinct filter_options_id for each products_id then just add it to the GROUP BY. If you ever upgrade to MySQL 5.7 this query will error out otherwise.
There is no need to use DISTINCT and GROUP BY in the same query. You are aggregating one of your columns using GROUP_CONCAT so GROUP BY is the right choice. Drop the DISTINCT after you SELECT.
GROUP_CONCAT(DISTINCT 1) as vals is the same as just 1 as vals

My only other thought is that perhaps you are aiming for a PIVOT query to fill your table, which is maybe why you were going after a group_concat. Check out this question for information on how to pull that off.

Convert concat sql string to INSERT INTO SELECT

1 Answers1