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:
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
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
(example: $mycolumns = "serial_number,product_image")
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
Yes, you should use the
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,
price. Without the
pc. a duplicate column name would (should) throw a SQL error.