Multiple search criteria

[eluser]ninjayan[/eluser]
I am currently working on generating reports on the application I am developing. In my report (search form), there are several criterias (document_type, source_type, source, status and datetime). In my model, I am checking if the post variables from the form are empty before doing a query. My problem is, what if a user did not select a document_type or he only filled up 1 or 2 or 3 or 4 criteria? How can I make the search query? Thanks in advance.

MODEL:

Code:

$document_type = $this->input->post('specific_document_type');
$source_type   = $this->input->post('specific_source_type');
$source        = $this->input->post('specific_source');
$status        = $this->input->post('specific_status');
$date          = date('Y-m-d', strtotime($this->input->post('specific_date')));

if (!empty($document_type) && !empty($source_type) && !empty($source) && !empty($status) && !empty($date))
{
$query = $this
   ->db
   ->select("reference_number, transaction_type, document_type, source_type, source_name, source_location, subject_matter, status")
   ->select("DATE_FORMAT(datetime, '%m/%d/%Y-%h:%i %p') as datetime_added", FALSE)
   ->like('document_type', $document_type)
   ->like('source_type', $source_type)
   ->like('source_location', $source)
   ->like('status', $status)
   ->like('DATE(datetime)', $date)
   ->order_by('document_id', 'desc')
   ->get('documents');
return $query->result_array();
}

[eluser]Pert[/eluser]
You do not have to complete Active Record chain in one go, so you can do this:

Code:

$this->db->select("reference_number, transaction_type, document_type, source_type, source_name, source_location, subject_matter, status")
   ->select("DATE_FORMAT(datetime, '%m/%d/%Y-%h:%i %p') as datetime_added", FALSE)
   ->order_by('document_id', 'desc');

if (!empty($document_type))
   $this->db->like('document_type', $document_type)

if (!empty($source_type))
   $this->db->like('source_type', $source_type)

...

$query = $this->db->get('documents');
return $query->result_array();

[eluser]ninjayan[/eluser]
Thanks for the response. How about in date range? If start date and end date is set or only 1 date is set. The between?
Thanks

[eluser]PravinS[/eluser]
for date range you can use where condition like this

Code:

$this->db->where("DATE BETWEEN START_DATE AND END_DATE", '', FALSE)

[eluser]ninjayan[/eluser]
Thanks for the response. This is now the updated code.

Code:

$document_type    = $this->input->post('document_type');
$transaction_type = $this->input->post('transaction_type');
$source_type      = $this->input->post('source_type');
$source_location  = $this->input->post('source_location');
$status           = $this->input->post('status');
$start_date       = date('Y-m-d', strtotime($this->input->post('start_date')));

if ($this->input->post('end_date') == '')
{
$end_date = $this->input->post('end_date');
}
else
{
$end_date = date('Y-m-d', strtotime($this->input->post('end_date')));
}

$this
->db
->select("reference_number, transaction_type, document_type, source_type, source_name, source_location, subject_matter, status")
->select("DATE_FORMAT(datetime, '%m/%d/%Y-%h:%i %p') as datetime_added", FALSE)
->order_by('document_id', 'desc');

if (!empty($document_type))
{
$this->db->like('document_type', $document_type);
}
if (!empty($transaction_type))
{
$this->db->like('transaction_type', $transaction_type);
}
if (!empty($source_type))
{
$this->db->like('source_type', $source_type);
}
if (!empty($source_location))
{
$this->db->like('source_location', $source_location);
}
if (!empty($status))
{
$this->db->like('status', $status);
}
if (!empty($start_date) && empty($end_date))
{
$this->db->like('datetime', $start_date);
}
elseif (!empty($start_date) && !empty($end_date))
{
$this->db->where("datetime BETWEEN '$start_date' AND '$end_date' ", '', FALSE);

}

$query = $this->db->get('documents');
return $query->result_array();

[eluser]PravinS[/eluser]
echo your query and check the output on mysql server through phpmyadmin

[eluser]ninjayan[/eluser]
Voted up your comments. Thanks for helping. Just one last. I want to show all record if all criteria all blank

[eluser]PravinS[/eluser]
as you have checked empty values for all your criteria's, so it will not execute the code with empty values and if all values are empty then, it will show all records

[eluser]ninjayan[/eluser]
Hmm. My select query is above the if statements and where conditions will only be added to the query if the post variable is not empty. So I am expecting my query with empty variables for condition will be like this:

[code]
$this
->db
->select("reference_number, transaction_type, document_type, source_type, source_name, source_location, subject_matter, status")
->select("DATE_FORMAT(datetime, '%m/%d/%Y-%h:%i %p') as datetime_added", FALSE)
->order_by('document_id', 'desc');
$query = $this->db->get('documents');
return $query->result_array();
[code]

I'm not really sure.

[eluser]Pert[/eluser]
That should do it, no?