comparing mysql database table values in php -

i've added database structure @ bottom

i have ranking system in making recruitment agency.. capture applicants details in different tables, , rank them (if fit needs/requirements of job advert) comparing data of candidates in database job in job_advert table. , display list of 10 best ranking (qualified) candidates sent notification qualify job.

i candidates data database so:

class ranking_model extends ci_model {      function __construct() {         parent::__construct();     }      function age() {         $sql = "select * membership";         $query = $this->db->query($sql)->result();         foreach ($query $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 date month, day , year             $date = explode("/", $date);             //get age 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 *  job_list          join job_history         on job_list.job_history_id = job_history.job_history_id";         $query = $this->db->query($sql)->result();          foreach ($query $row) {             $start = $row->start_date;             $end = $row->end_date;  //            //explode date month, day , year             $start = explode("-", $start);             $end = explode("-", $end); //            //get age 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 * personal";         $query = $this->db->query($sql)->result();          foreach ($query $row) {             $city = $row->city;         }         return $city;     }      function relocate() {         $sql = "select * personal";         $query = $this->db->query($sql)->result();          foreach ($query $row) {             $relocate = $row->relocate; //are willing 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 $row) {             $row->id_number;             $row->firstname;         }         return $query;     } 

and advert details this:

    function get_advert() {         $sql = "select * job_advert";         $query = $this->db->query($sql)->result();         foreach ($query $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 don't know how compare candidates data data job_adverts table. have no idea. of sort appreciated.

database structure

bold pk itacic 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 db, these ones use ranking.

i'm not sure how data working pulling down db , storing in local php variable seems bad idea.

your db structure seems bad....store data members table possible...no need split off bunch of tables unless table getting large amount of columns or field person can have multiple data points for. example of prior employers...how approach create link/join table called membersdata membersdatatype = 'employeer'

if approach problem following...

1) create html form displays job postings. when choose 1 of jobs dropdown submits form , job_id.

2) select job data db job_id

3) build query matches each criteria

$query = "select * members where"; if ($job["age"]){ $query .= " age > ".$job["age"]; }