Searching MySQL fields with similar fieldnames

Question

Hi.

New to mysql and php, so forgive any obvious stupidity, please!

One of mysql tables has category fields, indexed from a table of categories. Each of the categories is named category_1, category_2, category_3, etc.

I'm assuming that can't I use a wildcard to search these categories, like:

'SELECT * FROM products_table WHERE category_% ="furniture"'

as I'm getting a syntax error.

How would I best go about this?

TIA.

Answer 1

This is not something that should work. A database is designed for locating information stored in a defined structure meeting defined constraints, not for locating constraints which would find some information. The table indices aren't build for what you're trying to do.

You must either write out all the column names or use a stored procedure to produce dynamic SQL queries.

The fact that you are trying to do this is a strong indicator that your database schema should be reevaluated. Specifically, if there is a many-to-many relationship between products and categories, there should be three tables: products, categories, and products_categories.

Answer 2

You have to specify all fieldnames of course ie.

where category_1 = ... OR category_2 = ... etc. 

But if you're using this kind of columns, then your model seems to be wrong. To define multiple categories per product you would create a product, a categories and a link table. The link table will just contain the product and category ids that specify which category a product belongs to (and thus you can have multiple categories per product).

Answer 3

If you have to stick with that model, you would have to list all columns

Where 'furniture' In ( category_1, category_2, category_3 )

or create your query dynamically.

---

The best solution would be to normalize your model.

Define another table (e.g. categories) containing all your categories, and another table (e.g. product_categories) containing the ids of products and categories that match:

products ( id, ... )
categories ( id, name )
product_categories ( product_id, category_id )

This would allow you to search for all products with a certain category:

Select p.id
From categories c
Join product_categories pc On ( pc.category_id = c.category_id )
Join products p On ( p.product_id = pc.product_id )
Where c.name = 'furniture'

Answer 4

Use one query to extract the column names

SHOW COLUMNS FROM db.table WHERE Field like 'category_%';

Then use the 'Field' column to build your query