Help with Complex Query

[Monotoba]

MySQL ver: 5.1

 

I have a table of data that I need to sort out. The data is randomly dispersed throughout the table fields. For example, the price field may be in any "field_%_name' column and the corresponding "field_%_value" column will hold the price.  Size color, etc. are all the same way. These values may end up in any column. The only guaranty is that the property name will be a "field_%_name" column and the properties value will be in the corresponding "field_%_value" column.  The maximum number of properties per product will be 14. However, some products will have only a few property/value pairs.

 

What I need to accomplish is query the source table and put the values in this table in another (target) table that has columns for all the properties listed in this table. Finding the property names is easy by locating distinct values in the field_%_name" columns. So I now have the target table. But I need to find a way to pull the data from the source table and place each value in the proper field of the target table.

 

Any suggestions?

 

 

Source Table:

CREATE TABLE `ci_product_specs` (

  `ID` varchar(255) DEFAULT NULL,

  `Field_1_name` varchar(255) DEFAULT NULL,

  `Field_1_value` varchar(255) DEFAULT NULL,

  `Field_2_name` varchar(255) DEFAULT NULL,

  `Field_2_value` varchar(255) DEFAULT NULL,

  `Field_3_name` varchar(255) DEFAULT NULL,

  `Field_3_value` varchar(255) DEFAULT NULL,

  `Field_4_name` varchar(255) DEFAULT NULL,

  `Field_4_value` varchar(255) DEFAULT NULL,

  `Field_5_name` varchar(255) DEFAULT NULL,

  `Field_5_value` varchar(255) DEFAULT NULL,

  `Field_6_name` varchar(255) DEFAULT NULL,

  `Field_6_value` varchar(255) DEFAULT NULL,

  `Field_7_name` varchar(255) DEFAULT NULL,

  `Field_7_value` varchar(255) DEFAULT NULL,

  `Field_8_name` varchar(255) DEFAULT NULL,

  `Field_8_value` varchar(255) DEFAULT NULL,

  `Field_9_name` varchar(255) DEFAULT NULL,

  `Field_9_value` varchar(255) DEFAULT NULL,

  `Field_10_name` varchar(255) DEFAULT NULL,

  `Field_10_value` varchar(255) DEFAULT NULL,

  `Field_11_name` varchar(255) DEFAULT NULL,

  `Field_11_value` varchar(255) DEFAULT NULL,

  `Field_12_name` varchar(255) DEFAULT NULL,

  `Field_12_value` varchar(255) DEFAULT NULL,

  `Field_13_name` varchar(255) DEFAULT NULL,

  `Field_13_value` varchar(255) DEFAULT NULL,

  `Field_14_name` varchar(255) DEFAULT NULL,

  `Field_14_value` varchar(255) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

The target table looks something like this:

 

CREATE TABLE `ci_product_specs_temp` (

  `id` varchar(255) DEFAULT NULL,

  `name` varchar(255) DEFAULT NULL,

  `sku` varchar(255) DEFAULT NULL,

  `price` varchar(255) DEFAULT NULL,

  `color` varchar(255) DEFAULT NULL,

  `size` varchar(255) DEFAULT NULL,

  `weight` varchar(255) DEFAULT NULL,

  `description` varchar(255) DEFAULT NULL,

  `short_desc` varchar(255) DEFAULT NULL,

  `cost` varchar(255) DEFAULT NULL,

  `manufacturer` varchar(255) DEFAULT NULL,

  `mfg_item_number` varchar(255) DEFAULT NULL,

  `order_level` varchar(255) DEFAULT NULL,

  `qty_instock` varchar(255) DEFAULT NULL,

  `discount` varchar(255) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

Note their is a change in the case of field_%name values and the the fields in the target table.

Once the data is sorted into the target table, I will be able to re-type the value to more appropriate types such as integers for the id and real doubles for the price value. I also should note that the source table has about 100,000 records in it. I really don't want to have to write 20 queries to accomplish this. There must be a way to use a columns value as a field name?

 

Thanks for your help.

 

 

[mikosiko]

do you want to solve it just with a query?... I don't think it is possible.

 

I can think in 2 approachs:

- Use a combination of PHP and mysql PREPARED INSERT.  or if you are more comfortable with MYSQL

- a MySQL Stored procedure that include the PREPARED INSERT

 

below is a few lines that could help you as an example (incomplete example of course)

 

/* Prepare Insert Statement */
$stmt = $mysqli->prepare("INSERT INTO test1 (ip,posdate,obs) VALUES (?, ?, ?)") or die("Error " . $mysqli->error); 
$stmt->bind_param('sss', $val1, $val2, $val3)  or die($mysqli->error);  

/* execute prepared statement */ 
$stmt->execute()  or die($mysqli->error);  

printf("%d Row inserted.\n", $stmt->affected_rows); 

/* close statement and connection */ 
$stmt->close(); 

[Monotoba]

Yes, I was really hoping to solve it with a query only. By using the field_%_name values as the field names of the second table.

[DavidAM]

You might be able to do it in a single query, but I would be concerned about the transaction log. I'm not sure how mySql handles it, but I have had big update queries like this (on other DB servers) fill the logs and fail and rollback - after running for a few hours -- what a waste of time. 

 

Anyway, a statement like this, might work -- UNTESTED

INSERT INTO ci_product_specs_temp (id, name, sku) 
SELECT ID, 
MAX(IFNULL(IF(Field_1_name = 'name', Field_1_value, 
	IF(Field_2_name = 'name', Field_2_value,
	IF(Field_3_name = 'name', Field_3_value, NULL), '')) AS name,
MAX(IFNULL(IF(Field_1_name = 'sku', Field_1_value, 
IF(Field_2_name = 'sku', Field_2_value,
IF(Field_3_name = 'sku', Field_3_value, NULL), '')) AS sku
FROM ci_product_specs
GROUP BY ID

I think you get the idea, test each field for the field name and use its value if you find it. If you don't find it, set it to NULL, then if the whole IF is NULL, change it to an empty string (that's so that MAX will use any value found instead of NULL). GROUP BY the ID so you should end up with a single row per ID with all of the data. [ You don't technically need the column aliases in there, I just include them to kind of document the code]

 

You'll have to add the other 11 fields in the two statements I provided above, then add the other 12 fields.  It's ugly, but it might work.