Query which table a given value is in

Question

I need some help.

In my database I have several tables containing product details, each product has a unique ID.

Now I have a given product ID - How do I get the table name of which table this product is stored in since I have different tables in my database? How can I store the table name in a PHP variable so that I can reuse it in another select statement?

I searched on stackoverflow but only found answers about getting all table names of a database or column names. I'm looking for a simple solution in mysql/pdo select statement.

My family is very busy

Answer 1

To search within a fixed set of tables, you can combine individual queries using the UNION clause and then add a hardcoded string to identify the source:

SELECT packaging_id AS product_id, 'packaging' AS table_name
FROM packaging
WHERE name = 'Cardboard box'
UNION ALL
SELECT stationery_id, 'stationery'
FROM stationery
WHERE description = 'Cardboard box'
UNION ALL
SELECT sales_id, 'sales'
FROM sales
WHERE offer = 'Cardboard box'

You need to ensure that each column in the result set has the same data type, and you can use the first statement to set a normalized column name for the result set.