user1033882 user1033882 - 5 months ago 22
MySQL Question

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

Answer

Yes, you should use the pc. prefix.

The reason it works is because those column names are unique to the queried tables.

The reason I say you should use the pc. prefix is to prevent a possible issue where a user creates a custom column name (in products_custom) that is also in the products table. For example, description or price. Without the pc. a duplicate column name would (should) throw a SQL error.

Comments