performance hit on having a list of primary keys on an array and then querying based on them

In my application i have a jobs table. In the jobs table there are two fields

1. A
2. B whi开发者_如何学Cch has comma separated list (1,5,6,3)

On a particular page i need to

show records where A=2 and B=$pageid (which will be any one id)

So what i did is prequeried the table with the conditions and made an array with a list of job ids satisfying my condition and then querying specific records based on those ids.

$jobs = $this->db->get("job_postings");
        foreach($jobs->result_array() as $job)
        {
            $univArr = explode(",",$job["job_university"]);
            if((in_array($univid,$univArr)) or ($job["job_post_to"]==2)) {
                $jobArr[] = $job["id"];
            }   
        }
      return $jobArr;

But i feel this is not the right way to do it. Is there any better way to do it.

The issue here is that your list of values is stored in a single comma-separated field. If you had a proper 3NF schema, then you would have a mapping table going from university ID to posting ID and you could use a query like the following:

SELECT * FROM job_postings, universities, university_to_job WHERE
job_postings.job_post_to=2 AND
university_to_job.job_id=job_postings.job_id AND
university_to_job.university_id=universities.university_id AND
universities.name="Juliard College"

This would select all the job postings for Juliard College and could be extended to arbitrary sets of university IDs.

As things stand, you can do this by having a stored procedure on the SQL server return the split list of field IDs in your comma-separated job_university column, but I strongly recommend changing the database schema to use a proper mapping table.

To elaborate, right now your job_university column could have something like "3,5,8" in it, indicating that job post applies to the universities with ID 3, 5, and 8. The correct relational-algebra way to build this is to have a unique job posting ID (check), a unique university ID (check), and then a table like this:

job_posting_id, university_id
1             , 3
1             , 5
1             , 8

This table would map job posting number 1 to universities 3, 5, and 8.

Once you have this mapping table you may use JOIN statements (I have an implicit one above) to select only job posts associated with a certain university or the universities associated with a certain job posting.

The magic words for this type of situation where each job may be associated with one or more universities, and each university with one or more jobs is a "many-to-many relationship". That's why you need a mapping table - each side may be associated with one or more of the other side, and you don't want to store all the information about a university or job more than once.

I strongly recommend the following web site to learn a little bit more about structuring a database:

http://philip.greenspun.com/sql/

What database framework do you use? $this->db->get("job_postings"); seems to retreive all records, which isn't what you want. Can you add a Where clause to the get?