Page 1 of 1

How do I select a data from other likely data

Posted: Tue Sep 02, 2014 4:39 pm
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,

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

Posted: Tue Sep 02, 2014 5:30 pm
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.

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

Posted: Mon Sep 08, 2014 7:42 am
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