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.

I think it will be very useful and easier for you if you at first use something like WEKA, then you can find the criteria and define a term/condition so to compare each time with that, eg. you can find a 0-100 metric and each time to compare only one variable from db and then sort. It's the best way i think and very profesional (if you don't mind to mess a little bit with data mining). — Mhche
– Mhche, Commented Jul 18, 2013 at 12:29
I googled WEKA and as far as I can see it is only for Java? I don't mind if I have to do data mining :) — Renier
– Renier, Commented Jul 18, 2013 at 13:08
Yes but actually no. WEKA is a very easy tool to data mining, that you do at WEKA is to give it the data and then based on which genetic algorithm you choose you can get diagrams and results. Actually measures the "weight" of each value so to show you which value is more important, that's why i am saying if helpful for what you want to do. You will actually run it and see on which value you have to stand so to compare after. Is very easy to learn, as far as you just have to write at a notepad or to immigrate data from excel. You can do it with Matlab too if you know. — Mhche
– Mhche, Commented Jul 18, 2013 at 13:44
For WEKA you can see more from there youtube.com/watch?v=gd5HwYYOz2U Seems very good guide and better from the others. If you decide to proceed, let me know and ask me anything you want. — Mhche
– Mhche, Commented Jul 18, 2013 at 13:46
okay, it seems like an excellent tool.But before I proceed, I'm not exactly sure how WEKA and php will work together? — Renier
– Renier, Commented Jul 18, 2013 at 22:05

hendr1x · Accepted Answer · 2013-07-18 12:18:39Z

1

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

answered Jul 18, 2013 at 12:18

hendr1x

1,5011 gold badge14 silver badges23 bronze badges

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

1 Comment

Renier Over a year ago

there are to many columns to put all data in one.. I'll update my question to include database structure

Mihai8 · Accepted Answer · 2013-07-18 12:17:04Z

0

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.

answered Jul 18, 2013 at 12:17

Mihai8

3,1471 gold badge23 silver badges33 bronze badges

Comments

MBaas · Accepted Answer · 2013-07-18 13:19:48Z

0

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

edited Jul 18, 2013 at 13:19

answered Jul 18, 2013 at 12:20

MBaas

7,5318 gold badges50 silver badges66 bronze badges

2 Comments

Renier Over a year ago

I want to eventually create a form where the recruiters can also select from a list the criterias. so its not going to be totally dependent on the automatic ranking. But I first want to do it this way, as it is how the client wants it that way...

MBaas Over a year ago

I would probably also have a look at Google Maps first - but as I said, I really do not have experience iin that subject...

Collectives™ on Stack Overflow

comparing mysql database table values in php

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related