英文:
Finding the table name of a given value
问题
我需要一些帮助。
在我的数据库中有一些包含产品详情的表，每个产品都有一个唯一的ID。
现在我有一个给定的产品ID - 我如何获取存储产品的表的名称，因为我的数据库中有不同的表？我如何将表名存储在PHP变量中，以便在另一个select语句中重复使用它？
我在stackoverflow上搜索，但只找到了关于获取数据库的所有表名或列名的答案。我正在寻找在mysql/pdo select语句中的一个简单解决方案。
谢谢你的建议！
英文:
I need some help.
In my database are serveral tables with details of products, each product has a unique ID.
Now I have a given product ID - how can I get the name of the table in which the product is stored, hence there are different tables in my database? How could I store the table_name in a PHP variable to reuse it in another select-statement?
I search on stackoverflow but found only answers about getting all table_names of a database or columns names. I am looking for a simple solution in a mysql/pdo select statement.
Thanks for an advice!
答案1
得分: 0
# 用于在固定表集中搜索的查询，可以使用`UNION`子句组合各个查询，然后添加硬编码的字符串来标识来源：
query = """
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'
"""
# 确保结果集中每个列具有相同的数据类型，并可以使用第一个语句来为结果集设置规范化的列名。
英文:
To search in a fixed set of tables, you can combine the individual queries using the UNION clause and then add a hard-coded 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'll need to ensure you have the same data type for each column in the result set, and you can use the first statement to set a normalised column name for the result set.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论