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