comparing mysql database table values in php

Question

I've added database structure at the bottom

I have a ranking system in the making for a recruitment agency.. I capture all applicants details in different tables, and to rank them (if they fit the needs/requirements of a certain job advert) by comparing the data of the candidates that is in the database to the job in the job_advert table. And then display a list of the 10 best ranking (qualified) candidates would be sent a notification that they qualify for the job.

I get the candidates data from the database like so:

class ranking_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    function age() {
        $sql = "SELECT * FROM membership";
        $query = $this->db->query($sql)->result();
        foreach ($query as $row) {
            $id = $row->id_number;
            $dobs = substr($id, 0, 6);
            $dob = str_split($dobs, 2);
            $day = date('d', mktime(0, 0, 0, 0, $dob[2], 0));
            $month = date('m', mktime(0, 0, 0, $dob[1] + 1, 0, 0));
            $year = date('o', mktime(0, 0, 0, 0, 0, $dob[0] + 1));
            $date = "$day/$month/$year";
            //explode the date to get month, day and year
            $date = explode("/", $date);
            //get age from date or birthdate
            $age = (date("md", date("U", mktime(0, 0, 0, $date[0], $date[1], $date[2]))) > date("md") ? ((date("Y") - $date[2]) - 1) : (date("Y") - $date[2]));
        }
        return $age;
    }

    function job_experience() {
        $sql = "SELECT * FROM  job_list 
        JOIN job_history
        ON job_list.job_history_id = job_history.job_history_id";
        $query = $this->db->query($sql)->result();

        foreach ($query as $row) {
            $start = $row->start_date;
            $end = $row->end_date;

//            //explode the date to get month, day and year
            $start = explode("-", $start);
            $end = explode("-", $end);
//            //get age from date or birthdate
            $exp_in_years = (date("md", date("U", mktime(0, 0, 0, $start[2], $start[1], $start[0]))) > date("md", mktime(0, 0, 0, $end[2], $end[1], 0)) ? ((date("Y", mktime(0, 0, 0, 0, 0, $end[0])) - $start[0])) : (date("Y", mktime(0, 0, 0, 0, 0, $end[0])) - $start[0]));
        }

        return $exp_in_years;


    }

    function location() {
        $sql = "SELECT * FROM personal";
        $query = $this->db->query($sql)->result();

        foreach ($query as $row) {
            $city = $row->city;
        }
        return $city;
    }

    function relocate() {
        $sql = "SELECT * FROM personal";
        $query = $this->db->query($sql)->result();

        foreach ($query as $row) {
            $relocate = $row->relocate; //are you willing to relocate yes/no
        }
        return $relocate;
    }

    function get_personal() {
        $this->db->select('*');
        $this->db->from('membership');
        $this->db->join('personal', 'membership.id_number = personal.id_number');
        $query = $this->db->get()->result();

        foreach ($query as $row) {
            $row->id_number;
            $row->firstname;
        }
        return $query;
    }

and the advert details like this:

    function get_advert() {
        $sql = "SELECT * FROM job_advert";
        $query = $this->db->query($sql)->result();
        foreach ($query as $row) {

            $job_id = $row->job_id;
            $job_title = $row->job_title;
            $salary_offered = $row->salary_offered;
            $is_negotiable = $row->negotiable;
            $company_location = $row->company_location;
            $experience = $row->required_experience;
            $age = $row->age;
        }
    }

}

now I don't know how to compare the candidates data with the data that I get from the job_adverts table. I really have no idea. Help of any sort would be appreciated.

Database structure

bold PK itacic is FK.

membership(id_number, firstname, lastname, username, email, phone, password, role, Reg_time, activated);

personal(person_id, address, city, licence, id_number, gender, relocate, minimum_salary, prefered_salary, contract_type);

job_list(job_list_id, job_history_id, start_date, end_date, income, company_name, industry_type, reason_for_leaving, job_title);

job_history(job_history_id, id_number);

job_advert(advert_id, job_title, job_description, start_date, end_date, salary_offered, negotiable, benefits, company_location, required_experience, age);

I have more tables in the db, but these are the ones I use for ranking.

Answer 1

That's gonna be interesting :)

I guess the easiest is $exp_in_years > $experience and ($salary_offered >= $salary_asked_for) OR $is_negotiable - but even there you probably need to include a few % below these limites ($exp_in_years * 0.9) > $experience.

$job_title: will probably require a VERY good naming-scheme and some very tolerant checking

$company_location: I'd suggest to use web-service which will calculate the distance between compan's location and applicants location - but I haven't done that yet, so can't help with details, sry.

yet...despite all automation...you probably still need humans to filter, so I'd rather give them a larger set of candidates than eliminate totally appropriate candidates just because of one "little" test that failed somewhere...

Answer 2

I'm not sure how much data you are working with but pulling down everything from you db and storing it in a local php variable seems like a bad idea.

Your db structure all seems really bad....store as much data into the members table as possible...no need to split it off into a bunch of tables unless that table is getting a large amount of columns or a its a field that a person can have multiple data points for. An example of this would be prior employers...how I would approach this is create a link/join table called membersData with a membersDataType = 'employeer'

If I was to approach this problem I would do the following...

1) create an html form that displays all the job postings. When you choose one of the jobs from the dropdown it submits the form and the job_id.

2) select the job data from the db for that job_id

3) build a query that matches each criteria

$query = "SELECT * FROM members WHERE";
if ($job["age"]){ $query .= " age > ".$job["age"]; }

Answer 3

Although you have not posted the database structure, you can try to create a relationship between the tables. You can associate a a set a candidates with some records from job_advets. In the recruitment process some candidates depending on experience will become compatible or not with certain positions in the company. So job_experience function for each candidate will have to determine whether a particular job for which he applied it is compatible or not.