0

I have two mysql tables "category" and "applications" where the "applications" table referenced "category".

my "applications" table looks like
id  apps                 category
1   adobe reader         1&4&6&10
2   photoshop cs3        4&6&7&6

my "category" table looks like
id   category
1    utilities
2    windows
3    linux
4    mac
5    multimedia
And so on ..

Obviously one application can have multiple categories, so my problem here is trying to select applications where a category is present. I have tried

$query4 = "SELECT * from applictions Where category = '6';

But it only selects applications that has 6 as their first category e.g 6&5&1, so if i had an application "chrome browser" with "categories 5&3&6", it won't be selected.

Melody Daniel
  • 1
  • 3
  • whats the error exactly? – DZDomi Sep 29 '18 at 22:34
  • I don't think there would be an error DZDomi, it's valid SQL. However there would be no results either as the `category` field doesn't contain a "6" – trollboy Sep 29 '18 at 22:38
  • 1
    The best way is to go like @trollboy explained. If category is varchar type you could also try to do: `SELECT * FROM applications WHERE category like '%&6&%'` – Shidersz Sep 29 '18 at 22:42
  • @D.Smania that wouldn't work if the 6 is at the beginning or ending of the varchar, see my comment after my answer for an inclusive solution. And thanks for the shout out! – trollboy Sep 29 '18 at 22:46
  • @trollboy your are right, but if you use like '%6%' you can match a category with ID 16 or 26, for example. The best way is go with your explained answer. – Shidersz Sep 29 '18 at 22:50

2 Answers2

0

Yeah, relational databases don't work this way. You want what's called a many to many relation.

To accomplish this, you need to create a new table thusly

tablename: 
    Applications_Have_Category
Fields:
    Applications_id
    Category_id

Into this table you would insert the following:

1,1
1,4
1,6
1,10
2,4
2,6
2,7
2,6

This way every relationship has it's own record. Your query to get all category 6 items would then look like this:

SELECT * FROM `applictions` a JOIN  `Applications_Have_Category` ahc ON a.id = ahc. Applications_id WHERE ahc.Category_id = 6;
trollboy
  • 133
  • 7
  • Failing that, you *can* still get the data you're needing doing something akin to: `SELECT * FROM applications WHERE category LIKE "%6%"` however such a query would be VERY inefficient and lends itself to bad data normalization practices. – trollboy Sep 29 '18 at 22:39
0

I have fixed the code, I searched my table with phpmyadmin until I got the right filter that returned the result, turns out I needed something like

SELECT * FROM `softwares` WHERE `category` LIKE '%6%'

Instead of 

"SELECT * from applictions Where category = '6';

thanks all.

Melody Daniel
  • 1
  • 3