CODEWITHSUNDEEP
phpmysqlcodeigniter
amit
amit

Reputation: 1209

How to write custom query codeigniter

I have following query and its working in MySQL

SELECT * FROM usr_booking 
WHERE shop_id ='1' 
AND services_dates='2019-01-02' 
AND (start_time BETWEEN '10:00' AND '11:00' OR end_time BETWEEN '10:00' AND '11:00');

But if I convert in Codeigniter way then query not working, How can I write this query in Codeigniter? I tried with the following code but showing "Database Error"

$query=$this->db->query("SELECT * FROM usr_booking 
WHERE shop_id ='1' 
AND services_dates='2019-01-02'
AND (start_time BETWEEN '10:00' AND '11:00' OR end_time BETWEEN '10:00' AND '11:00')");

Where I am wrong? Thanks in advance

Upvotes: 3

Views: 8017

Answers (3)

Mohit Gupta
Mohit Gupta

Reputation: 31

In your model, create a function

function customData($str)
{
   $query = $this->db->query($str); 
   return $query->result_array();
}

In your controller load the same model and call above method

$querySTR = "SELECT * FROM usr_booking 
WHERE shop_id ='1' 
AND services_dates='2019-01-02' 
AND (start_time BETWEEN '10:00' AND '11:00' OR end_time BETWEEN '10:00' AND '11:00')";

$result = $this->model_name->customData($querySTR);
if($result){
 //Write your code
} else {
  echo 'Error Code: '.$this->db->_error_number().' -- Error Message: '.$this->db->_error_message();
}

Upvotes: 2

Shahnawaz Alam
Shahnawaz Alam

Reputation: 86

It's easy to write your own custom query in Codeigniter, You can try this. Create a function inside your model with like this

function customQuery()
{
    $query = $this->db->query("SELECT * FROM usr_booking WHERE shop_id ='1' AND services_dates='2019-01-02' AND (start_time BETWEEN '10:00' AND '11:00' OR end_time BETWEEN '10:00' AND '11:00')"); 
    return $query->result_array();
}

Load this Model inside controller and now call this function from your controller like this!

$this->customModel->customQuery();

this will return result array. Thank you

Upvotes: 1

Roshan
Roshan

Reputation: 842

you can refer to this page regarding on CI query builder.

Base on your question you are trying to convert MySQL query into CI query builder right? Let's give it a try, based on your code you are selecting a data with a condition, so it looks like this:

        $this->db->select("*");
        $this->db->from("usr_booking ");
        $this->db->where("shop_id",1);
        $this->db->where("services_dates",'2019-01-02');
        $this->db->where("start_time" >= '10:00');
        $this->db->where("start_time" <= '11:00');
        $this->db->or_where("end_time" >= '10:00');
        $this->db->where("end_time" <= '11:00');
        $query = $this->db->get();

        return $query->result();

Basically, this will return data on object type. Use result_array() if you want to convert it to array.

If you are getting an Error Database Error check your database.php file under application/config you must set up your default database config. You can refer here.

NOTE: Don't forget to load your database as always or set it on autoload.php

Let me see if there's an improvement.

Upvotes: 2

Related Questions