getting data from database - entries not in right format

[adammc]

Hi folks,

I am running an online job search site and employer are able to post their job vacancies under multiple categories.

Example - Accounting,Administrative,Hospitality
The example above is taken from the category table in the database.

The problem is, I am trying to create a script that lists al the jobs available in each category and its not listing the jobs that employers have selected multiple categories, its only listing the jobs that have been added into only one category.

Example query code -

$a = mysql_query("SELECT * FROM job_post WHERE JobCategory = 'Accounting' order by job_id desc LIMIT 25");

What can I do?

[jatar_k]

my first issue is searching by a textual representation

I would use ids for the categories

so
job table
category table

if you are allowing one to many between job and category then I would probably (depends, not my system, don't know the full scope) use a links table. I don't know if that is the real name but that's what I call it because it makes sense to me.

so
job table contains job_id
category table contains cat_id

jobcat table has 2 fields
job_id
cat_id

to select all jobs for any given category (ex cat_id=5)

select job_id from jobtable where cat_id=5

this will give you all jobs for any given category

[adammc]

Thanks for the ideas :)

I got it sorted by using :

$a = mysql_query("SELECT * FROM job_post WHERE JobCategory LIKE '%Accounting%' ORDER BY job_id DESC LIMIT 25");

[jatar_k]

keep in mind that like queries are slow, if your site gets high volume then you may run into slow downs.