PHP search query

Question

I try to do a search on a CVs application, which stored in many tables, and I'm trying to find a CV that has a specific information like (qualifications and educations).

So I have sent the search criteria via POST:

$_POST['qualifications'] = array(5,9) and <br> 
$_POST['educations'] = array(13);

and I wanna to find the cvs that has just that information.

the tables are look like:

cv_qualifications table: id, resume_id , qualifications_id.<br>
cv_educations table    : id, resume_id , educations_id.

I have tried to use (WHERE IN statements):qualifications

select * FROM cv_qualifications where qualifications_id IN (19,5);

select * FROM cv_educations where educations_id IN (13);

but that statements bring all CVs which have qualifications_id 19 or qualifications_id 5 or educations_id = 13.

This is my code:

$keys = array_keys($_POST);

$found = array();
foreach ($keys as $key) {
    $found[$key] = DataBase::query("SELECT * FROM cv_" . $key . " WHERE " . $key . "_id IN (?)", array($_POST[$key]));
}
echo count($found)."\n";
print_r($found)."\n";

Answer 1

Have a look at this :-

SELECT * FROM cv_qualifications q 
JOIN cv_educations e ON e.resume_id = q.resume_id
where (q.qualifications_id = '5'
       or q.qualifications_id = '9')
and e.educations_id = '13'"

Answer 2

Assuming both tables are using a common resume_id, you can use that to JOIN your tables into one query that will :

SELECT * FROM cv_qualifications q 
JOIN cv_educations e ON e.resume_id = q.resume_id
where q.qualifications_id IN (5,19)
and e.educations_id IN (13)

The SELECT * in this example will only retrieve from cv_qualifications, if you wanted fields from both tables you can substitute SELECT q.*, e.* FROM ...

Answer 3

Make a try with single query by INNER JOIN

select * FROM cv_qualifications as cq
 INNER JOIN cv_educations as ce ON cq.resume_id=ce.resume_id 
WHERE cq.qualifications_id IN (19,5) and ce.educations_id IN (13);