PHP - need help with selecting rows from mySql

Question

Hey, well Im trying to create an web index, every adveretiser in my database will be able to appear on a few categories, so i've added a "categorys" column, and in that column il store the categories seperated by "," so it will look like:

1,3,5

the problem is that i have no idea how im supposed to select all of the advertisers in a certain category, like mysql_query("SELECT * FROM advertisers WHERE category = ??");

Thanks in advance.

Answer 1

You should design your database in another way. Take a look at Atomicity.
Short: You should not store your value in the form of 1,3,5.

I won't give you an answer because if you starting you use it this way now, you going to run into much more severe problems later. No offense :)

Answer 2

If categories is another database table, you shouldn't use a plain-text field like that. Create a "pivot table" for the purpose, something like advertisers_categories that links the two tables together. With setup, you could do a query like:

SELECT A.* FROM advertisers AS A
JOIN advertisers_categories AS AC ON AC.advertiser_id = A.id
WHERE AC.category_id = 12;

The schema of advertisers_categories would look something like this:

# advertisers_categories
# --> id INT
# --> advertiser_id INT
# --> category_id INT

Answer 3

Your implementation as-is will make it difficult and taxing on your server's resources to do what you want.

I'd recommend creating a table that relates advertisers to categories and then querying on that table given a category id value to obtain the advertisers that are in that category.

Answer 4

That is a very wrong way to define categories, because your array of values cannot be normalized. Instead, define another table called CATEGORIES, and use a JOIN-table to match CATEGORIES with ADVERTIZERS.

Only then you will be able to properly select it.

Hope this helps!

Answer 5

It's not possible having comma-separated values to do this strictly in an SQL query. You could return every row and have a PHP script which goes through each row, using explode($row,',') and then if(in_array($exploded_row,'CATEGORY')) to check for the existence of the category.

The more common solution is to restructure your database. You're thinking too two-dimensionally. You're looking for the Many to Many Data Model

advertisers
-----------
id
name
etc.

categories
----------
id
name
etc.

ad_cat
------
advertiser_id
category_id

So ad_cat will have at least one (usually more) entry per advertiser and at least one (usually more) entry per category, and every entry in ad_cat will link one advertiser to one category.

The SQL query then involves grabbing every line from ad_cat with the desired category_id(s) and searching for an advertiser whose id is in the resulting query's output.