Mysql "magic" catch all column for select statement

Question

Is there a way that I can do a select as such

select * from attributes where product_id = 500

would return

id  name     description
 1  wheel    round and black
 2  horn     makes loud noise
 3  window   solid object you can see through

and the query

select * from attributes where product_id = 234

would return the same results as would any query to this table.

Now obviously I could just remove the where clause and go about my day. But this involves editing code that I don't really want to modify so i'm trying to fix this at the database level.

So is there a "magical" way to ignore what is in the where clause and return whatever I want using a view or something ?

Answer 1

Even if it was possible, I doubt it would work. Both of those WHERE clauses expect one thing to be returned, therefore the code would probably just use the first row returned, not all of them.

It would also give the database a behaviour that would make future developers pull their hair out trying to understand.

Do it properly and fix the code.

Answer 2

or you could pass "product_id" instead of an integer, if there's no code checking for that...so the query would become:

select * from attributes where product_id = product_id;

this would give you every row in the table.

Answer 3

You may be able to use result set metadata to get what you want, but a result set won't have descriptions of fields. The specific API to get result set metadata from a prepared query varies by programming language, and you haven't said what language you're using.

You can query the INFORMATION_SCHEMA for the products table.

SELECT ordinal_position, column_name, column_comment
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'products' AND schema_name = 'mydatabase';

You can restructure the database into an Entity-Attribute-Value design, but that's a much more ambitious change than fixing your code.

Or you can abandon SQL databases altogether, and use a semantic data store like RDF, which allows you to query metadata of an entity in the same way you query data.

Answer 4

As far out as this idea seems I'm always interested in crazy ways to do things. I think the best solution I could come up with is to use a view that uses the products table to get all the products then the attributes table to get the attributes, so every possible product is accounted for and all will get the same result

Answer 5

If you can't edit the query, maybe you can append to it? You could stick

OR 1=1

on the end.