CODEWITHSUNDEEP
phpmysqlcodeigniter
user1080247
user1080247

Reputation: 1166

how to search range date in advanced serach

i use this function to build advanced search with many criteria but when search with time i rain into one problem how to use between - or (or) condition in only with date

// post inputs

$pi_num = $this->input->post('pi_num');
$pi_name = $this->input->post('pi_name');
$pi_passport = $this->input->post('pi_passport');
$pi_date_reg = $this->input->post('pi_date_reg');
$pi_date_reg2 = $this->input->post('pi_date_reg2');
$pi_branch_id = $this->input->post('pi_branch_id');
$pi_gender = $this->input->post('pi_gender');
$pi_importance = $this->input->post('pi_importance');

// put it into array $conditions = array();

if ($pi_num !== '') {
    $conditions[] = 'pi_num LIKE "' . $pi_num . '"';
}
if ($pi_name !== '') {
    $conditions[] = 'pi_name LIKE "%' . $pi_name . '%"';
}
if ($pi_passport !== '') {
    $conditions[] = 'pi_passport = "' . $pi_passport . '"';
}
if ($pi_date_reg !== '') {
    $conditions[] = 'pi_date_reg = "' . strtotime($pi_date_reg) . '"';
}
if ($pi_date_reg2 !== '') {
    $conditions[] = 'pi_date_reg = "' . strtotime($pi_date_reg2) . '"';
}
if ($pi_gender !== '') {
    $conditions[] = 'pi_gender = "'.$pi_gender.'"';
}
if ($pi_importance !== '') {
    $conditions[] = 'pi_importance = "' . $pi_importance . '"';
}
if ($pi_branch_id !== '') {
    $conditions[] = 'pi_branch_id = "' . $pi_branch_id . '"';
}

then collect all that with implode function..but i want to use another query condition for date..how to do this?

$sqlStatement = 'SELECT
            d_branch.*,
            d_patient.*
          FROM  d_patient
            LEFT JOIN d_branch
              ON d_branch.branch_id = d_patient.pi_branch_id WHERE ' . implode(' AND ', $conditions)
;
$result = $this->db->query($sqlStatement);

return $result->result_array();

}

this is query example

SELECT d_branch.*, d_patient.* FROM d_patient LEFT JOIN d_branch ON d_branch.branch_id = d_patient.pi_branch_id WHERE pi_num LIKE "555555" AND pi_passport = "333333" AND pi_date_reg = "1404684000" AND pi_date_reg = "1405029600" AND pi_gender = "1" AND pi_importance = "1" AND pi_branch_id = "1443"

Upvotes: 0

Views: 98

Answers (2)

Giulio De Marco
Giulio De Marco

Reputation: 137

if ($pi_date_reg !== '') {
  if ($pi_date_reg2 !== '') {
    $conditions[] = '(pi_date_reg BETWEEN "'. $pi_date_reg .'" AND "'. $pi_date_reg2 .'")'; 
  }
  else {
    $conditions[] = 'pi_date_reg >= "'. $pi_date_reg .'"';
  }
}

In this way the second date is optional but you can search enyway for all rows with pi_date_reg >= $pi_date_reg. Be sure that format input date is yyyy-mm-dd

Just i way to improve you code security, using placeholders:

$conditions = $values = array();
if ($pi_num !== '') {
    $conditions[] = 'pi_num LIKE "%d"';
    $values[] = $pi_num;
}
if ($pi_name !== '') {
    $conditions[] = 'pi_name LIKE "%%s%"';
    $values[] = $pi_name;
}
//...

and so on

Then:

$result = $this->db->query($sqlStatement, $values);

Upvotes: 1

Karan Thakkar
Karan Thakkar

Reputation: 1467

you can do something like this

if ($pi_date_reg !== '' AND $pi_date_reg2 !== '') {
    $conditions[] = "(pi_date_reg  BETWEEN '" . strtotime($pi_date_reg) . "' AND '" . strtotime($pi_date_reg2)."')";
}

Upvotes: 0

Related Questions