CODEWITHSUNDEEP
mysqlcodeignitercodeigniter-3
AgentRed
AgentRed

Reputation: 72

How to convert sql query to codeigniter query

Can somebody help me convert this Sql Query 

SELECT *
FROM customer c
LEFT JOIN customer_order co 
ON c.customer_number = co.customer_number 
AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid'
AND c.order_status = 'unserve'
AND co.cus_ord_no IS null

into Codeigniter query just like the image below for example

enter image description here

Upvotes: 4

Views: 3084

Answers (2)

GGw
GGw

Reputation: 443

You can do

public function view_customers()
{
    $sql = "SELECT * FROM customer c LEFT JOIN customer_order co ON c.customer_number = co.customer_number AND co.order_status IN ('preparing', 'prepared') WHERE c.customer_status='unpaid' AND c.order_status = 'unserve' AND co.cus_ord_no IS null";

    return $this->db->query($sql)->result();
}

You can use row() for one output to object, or row_array() if one output but array. result() is multiple objects and result_array() is multiple arrays.

My way do usually is like this:

Controller:

public function view()
{
    $this->load->model('My_Model');

   $data = new stdclass;
   $data->user_lists = $this->my_model->view_users(array('nationality'=>'AMERICAN'));
}

Model:

public function view_users($param = null) //no value passed
{
   $condition = '1';
   if (!empty($param)) { //Having this will trap if you input an array or not
     foreach ($param as $key=>$val) {
       $condition .= " AND {$key}='{$val}'"; //Use double quote so the data $key and $val will be read.
     }
   }

   $sql = "SELECT * FROM users WHERE {$condition}"; //Use double quote so the data $condition will be read.
   // Final out is this "SELECT * FROM users WHERE 1 AND nationality='AMERICAN'";

   return $this->db->query($sql)->result();
}

Upvotes: 3

DFriend
DFriend

Reputation: 8964

When query statements do not have clauses that need to change conditionally then using $this->db-query() is the way to go.

$sql = "SELECT * FROM customer c LEFT JOIN customer_order co 
ON c.customer_number=co.customer_number AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid' AND c.order_status='unserve' AND co.cus_ord_no IS null";

$query = $this->db->query($sql)->result();
echo json_encode($query);

It might be wise to include a check on the return from query() though because if it fails (returns false) then the call to result() will throw an exception. One way that can be handled is like this.

$query = $this->db->query($sql);
if($query !== FALSE) 
{
    echo json_encode($query->result());
    return;
}

echo json_encode([]); // respond with an empty array

Query Builder (QB) is a nice tool, but it is often overkill. It adds a lot of overhead to create a string that literally is passed to $db->query(). If you know the string and it doesn't need to be restructured for some reason you don't need QB.

QB is most useful when you want to make changes to your query statement conditionally. Sorting might be one possible case.

if($order === 'desc'){
   $this->db->order_by('somefield','DESC'); 
} else {
       $this->db->order_by('somefield','ASC'); 
}

$results = $this->db
    ->where('other_field', "Foo")
    ->get('some_table')
    ->result();

So if the value of $order is 'desc' the query statement would be 

SELECT * FROM some_table WHERE other_field = 'Foo' ORDER BY somefield 'DESC'

But if you insist on using Query Builder I believe this your answer

$query = $this->db
    ->join('customer_order co', "c.customer_number = co.customer_number AND co.order_status IN ('preparing', 'prepared')", 'left')
    ->where('c.customer_status','unpaid')
    ->where('c.order_status','unserve')
    ->where('co.cus_ord_no IS NULL')
    ->get('customer c');

//another variation on how to check that the query worked
$result = $query ? $query->result() : [];
echo json_encode($result);

Upvotes: 2

Related Questions