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!

score 1 · Accepted Answer · answered Dec 30 '12 at 06:08

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');

OMG Thanks, this worked perfect after I added it :), althought at the moment I'm using my localhost for the database and it takes a litle while to retrieve all the info. Hoping after I transfer it to my teachers server it will speed up a bit. Thanks again :) — Ragazz4, Dec 31 '12 at 01:18

GrayB · Answer 2 · 2012-12-30T06:27:10.560

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

Thank you very much for your response @GrayB, however I have recently applied your solution to my codeigniter folder and got this error when I execute the search: _Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 47 bytes) in C:\xampp\htdocs\w1228355\system\database\drivers\mysql\mysql_result.php on line 167_ My database is quite huge, so I dont know if that has to do something with it. Also grabbing the info from my controller as you recommended did give me some errors. I'm sorry but I'm kinda new at CodeIgniter. Any follow ups would be greatly appreciated. — Ragazz4, Dec 31 '12 at 00:54
Try this link: http://stackoverflow.com/questions/561066/php-fatal-error-allowed-memory-size-of-134217728-bytes-exhausted-codeigniter — GrayB, Dec 31 '12 at 00:58
Also check the extract php documentation about warnings when using with $_GET and $_POST http://php.net/manual/en/function.extract.php — GrayB, Dec 31 '12 at 02:38

MySQL & CodeIgniter! Search form problems

2 Answers2