CODEWITHSUNDEEP
phpmysqlstatistics
Andy
Andy

Reputation: 5091

MySQL Calculating Percentage of Matches

I'm making a job recruitment site using PHP and MySQL and I want to be able to match up candidates to jobs when a job is submitted

When a client submits a job they are able to select which sector the job applies to, whether a driving licence is required, whether the candidate must be SIA (Security Industry Authority) registered and which SIA level and a couple other criteria

What I want to be able to do when a job is submitted is check the database for matching candidates, then return a percentage based on how much of the criteria they match

At the moment my idea is to make x database queries for x criteria then work out the percentage that way e.g:

public function getJobCriteriaPercentage($job_id, $candidate_id) {
    $sector_query = $this->db->query(...);
    $driving_licence_query = $this->db->query(...);
    $sia_query = $this->db->query;
    ...
    $matches = 0;
    if($sector_query->num_rows) {
        $matches++;
    }
    ...
    $percent = ($matches / TOTAL_CRITERIA) * 100;
}

Is there a more efficient way of doing this perhaps in one MySQL query?

Upvotes: 2

Views: 1064

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Running a query per each criteria will kill your server. Why not return a column with the total matches?

EG: If your filters are:

  • ColA = 'valA'
  • ColB in (1, 5, 7)
  • ColC like '%valC%'

You could do somethinkg like:

select
    (ColA = 'valA') + 
    (ColB in (1, 5, 7)) + 
    (ColC like '%valC%') TotalMatches
from t
where jobId = $jobId and candidateId = $candidateID

If you'd like to know the specific matches you can split it into columns:

select
    ColA = 'valA' ColAFilter,
    ColB in (1, 5, 7) ColBFilter,
    ColC like '%valC%' ColCFilter
from t
where jobId = $jobId and candidateId = $candidateID

The comparisons return 1 for true and 0 for false.

Upvotes: 3

Related Questions