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";

Join the 2 tables then do a where clause as needed. You also shouldn't execute multiple times do 1 execution and build the query dynamically. — user3783243
– user3783243, Commented Jun 22, 2018 at 1:30

Rando · Accepted Answer · 2018-06-22 01:55:25Z

1

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 ...

answered Jun 22, 2018 at 1:55

Rando

113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A l w a y s S u n n y · Accepted Answer · 2018-06-22 01:36:54Z

0

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);

answered Jun 22, 2018 at 1:36

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

Comments

PHP Web · Accepted Answer · 2018-06-22 02:11:16Z

0

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'"

answered Jun 22, 2018 at 2:11

PHP Web

2573 silver badges8 bronze badges

Collectives™ on Stack Overflow

PHP search query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related