MySQL & CodeIgniter! Search form problems

Question

Could anyone give me any advice on how to get a html form with 4 text fields to search my database and look for any related data in it and display it?

Basically, this form doesnt have to be completely filled in, the user can type in just 1 or 2 fields (e.g first_name and last_name) and then the model is supposed to search for the other 2 missing fields that are related to the users entry.

At least one field has to be filled in for the search operation to work, this field can be any of the 4 (randomly). The fields are named as:

Firstname Lastname dept title

I have 3 tables in my database that contain the information needed, they are as follows:

departments(dept_no, dept_name) employees(emp_no, first_name, last_name) title(emp_no, title)

Since not all of them share the same primary key, there is another table in my database that links 'departments' table to the 'employees' table.

(departments_employees) => dept_emp(emp_no, dept_no)

My model.php file below uses all these tables in order to search for some data, but so far this function only searches for the data that matches the 'firstname' entry and the rest input fields are ignored.

<?php

class Emp_model extends CI_Model {

function find_dept()
{
    $this->db->select('employees.first_name, employees.last_name, departments.dept_name, titles.title');
    $this->db->where('last_name', $this->input->get('lastname'));
    $this->db->join('dept_emp', 'dept_emp.emp_no = employees.emp_no');
    $this->db->join('departments', 'departments.dept_no = dept_emp.dept_no');
    $this->db->join('titles', 'titles.emp_no = employees.emp_no');
    $query = $this->db->get('employees');

    if($query->num_rows > 0)
    {
        return $query->result();

    }

    else
    {
        redirect('find');

    }

}
}

?>

My view displays the results in a table, so up to now everything works with no errors. After hrs of researching I can't come up of a way to do this. If anyone has any ideas or similar tutorials that I can follow please let me know! Will very much appreciate it! Thanks :)

If I didnt make myself clear or more information is needed, please let me know!

Answer 1

Seems that you are very close to completing your task, just a couple of things that should be mentioned about your code. You should not be using $this->input->get('lastname') directly in the model, you should be grabbing the information in the controller and passing it to the model using something like this:

function find_dept($firstName, $lastName, $dept, $title) {
   ...
}

This will allow you to reuse the model function without having to rely on the method that the information is being sent to it.

function find_dept($firstName = false, $lastName = false, $dept = false, $title = false) {
   $this->db->select('employees.first_name, employees.last_name, departments.dept_name, titles.title');

   $this->db->join('dept_emp', 'dept_emp.emp_no = employees.emp_no');
   $this->db->join('departments', 'departments.dept_no = dept_emp.dept_no');
   $this->db->join('titles', 'titles.emp_no = employees.emp_no');

   if($firstName && $firstName !== '')
   {
     $this->db->where('employees.first_name', $firstName);
   }

   if($lastName && $lastName !== '')
   {
     $this->db->where('employees.last_name', $lastName);
   }

   if($dept && $dept !== '')
   {
     $this->db->where('departments.dept_name', $dept);
   }

   if($title && $title !== '')
   {
     $this->db->where('titles. title', $title);
   }


   $query = $this->db->get('employees');
}

To elaborate on codeigniter, I am using if($dept && $dept !== '') because $this->input->get('lastname') returns false if it's not set, but you might need to check if it's equal to an empty string. There might be a better way of writing this

You could also $this->db->like to improve your search. Check it out at: http://ellislab.com/codeigniter/user-guide/database/active_record.html

Answer 2

you can pass an array to CI active record where you have to do if check for your all inputs you should call active record where before get

$inputs = $this->input->post(); // you get an Associative array of post data

extract($inputs); // will extract variables from Associative array.

$where = array();

if(strlen($first_name))
 { 
   $where['first_name'] = $first_name;
 }

   if(strlen($last_name))
 { 
   $where['last_name'] = $last_name;
 }

 if(strlen($dept))
 { 
   $where['dept'] = $dept;
 }

  if(strlen($title))
 { 
   $where['title'] = $title;
 }

 $this->db->where($where);
 $query = $this->db->get('employees');