CODEWITHSUNDEEP
phpmysqlcodeigniter
Nadia
Nadia

Reputation: 73

display record with specific date in codeigniter

I want to select record with certain date and id. My datetime format in database is 2018-02-23 08:38:45. I've try this in my model but it always return the records with specific id only. My date format in $tgl is 23-01-2018 so I change the format using $date = date('Y-m-d', strtotime($tgl) ); 

public function spesific_odontogram($id_pasien, $date){
    $query = "SELECT * FROM odontogram WHERE DATE('inserted_at') = $date AND id_pasien=$id_pasien";
    $record = $this->db->query($query);
    if ($record->row_array()>0) {
        return $record->result_array();
    }
    return false;
    //return $record;
}

Here's my controller

public function spesific_odontogram(){
    $id_pasien=$_POST['id_pasien'];
    $tgl=$_POST['tgl'];
    $date = date('Y-m-d', strtotime($tgl) );
    $data['record']=$this->m_pasien->spesific_odontogram($id_pasien, $date);
    if ($this->input->is_ajax_request())
        {
          echo json_encode($data);
          exit;
        }
}

Upvotes: 1

Views: 2545

Answers (2)

Danish Ali
Danish Ali

Reputation: 2352

Use Date variable like '$date' instead of $date. Also make sure date format are same 

public function spesific_odontogram($id_pasien, $date){
$query = $this->db->query("SELECT * FROM odontogram WHERE inserted_at = '$date' AND id_pasien=$id_pasien");

if ($query->row_array()>0) {
    return $query->result_array();
}
return false;
}

Upvotes: 1

Hasta Dhana
Hasta Dhana

Reputation: 4719

Try using below query on your model : 

public function spesific_odontogram($id_pasien, $date){
    $query = "SELECT * FROM odontogram WHERE inserted_at >= $date AND inserted_at < $date + INTERVAL 1 DAY AND id_pasien=$id_pasien";
    $record = $this->db->query($query);
    if ($record->row_array()>0) {
        return $record->result_array();
    }
    return false;
    //return $record;
}

It basically check for only matches date record without calculate DATE() value of every row

Upvotes: 0

Related Questions