How do I select a data from other likely data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

How do I select a data from other likely data

Post by adsegzy »

Hello friends,

I have a range of numbers in a table like below,

CATEGORIES TABLE
ID CATEGORY
1 Accounting
2 Real Estate
3 Banking & Finance
4 Aviation
5 Biochemistry
6 Poultry
etc

MEMBERS TABLE
ID CATEGORIES
1 2, 5, 7, 13, 21
2 3, 4, 5, 6, 10, 15
3 3, 7, 9
4 1, 15, 32, 35
5 8, 9, 12,

To view members under a category, the category id is passed as a string ($category) to the Category Page
Now i need to select Members that can work under Banking & Finance (3) so i used this query

Code: Select all

$category = 3;
$get = mysql_query("SELECT id FROM members WHERE categories='%$category%'");
It is expected that the result should be ID 2 & ID 3, but it will also bring members with ID 1 (because it has 13) & ID 4 (that has 35).

Pls How do I select members with 3 and not 13 or 33 or 23 or 30 etc

Thanks,
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I select a data from other likely data

Post by Celauran »

Don't store multiple values in the same column. What you want here is a join/pivot table that would have member_id and category_id as columns and you could then query against either of those to get all members for a category or all categories for a member.
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

Re: How do I select a data from other likely data

Post by adsegzy »

Thank Celauran,

I have solved the problem. What i simple did was that i created another colomn called tid i now created unique 5 digit random for each category,. so instead of using the id (eg 3) to query (%3%) whicl usually bring results like 13, 33, 23, 103, i just us the unique tid and the result it's ok.

Thanks
Post Reply