lundi 27 juin 2016

mysql select data from 2 tables, but 2nd table has dynamic cell names


I have a table that holds product names, description, prices, etc and another tables were the admin can add custom columns to expand the first table. These columns are create through php forms. I have no way of knowing how many the user will create.

For example my "products" tables has:

id 
name    
description
price
created

and the second table "products_custom" has:

id
product_id    //this value holds the product id from the above table
serial_number //a custom column the user created
product_image //another column the user created

Now, in order to show all these values in a table i have to LEFT JOIN the 2 tables based on the products.id and products_custom.product_id.

The proper SQL query should be:

SELECT p.*, pc.serial_number, pc.product_image FROM products AS p 
LEFT JOIN products_custom as pc ON p.id = pc.product_id ORDER BY p.id ASC

I can get the cell names that exist in the products_custom tables using a php function that queries the 2nd table so having those values in order to use them in the query above is not a problem. I can have these column names returned to a variable.

(example: $mycolumns = "serial_number,product_image")

The problem is that by accident this query here works as well.

SELECT p.*, serial_number,product_image FROM products AS p 
LEFT JOIN products_custom ON p.id = products_custom.product_id ORDER BY p.id ASC

Should this be working? Can i leave it like that? or should i add the pc. prefix in every value that $mycolumns has and then reconstruct the sql to make it look like the 'proper query' i wrote above?

Thanks in advance !

FINAL EDIT: Based on the answers bellow, to convert the SQL to include pc.prefix from a string of column names:

//$whatCol = "serial_number,product_image"; 
$choices = explode(",", $whatCol); 
foreach($choices as &$choice) { 
  $choice = "pc.".mysql_real_escape_string($choice); 
} 
$whatColSQL = implode(",", $choices); 

$prSQL = "SELECT p.*, $whatColSQL FROM products AS p LEFT JOIN products_custom as pc ON p.id = pc.product_id ORDER BY p.id ASC";

Aucun commentaire:

Enregistrer un commentaire