How to Execute My SQL Query in Codeigniter

How to convert from Mysql query to Codeigniter syntax?

You can use like this:

$sqlRawQuery = "UPDATE tblUserController 
INNER JOIN tblUser on tblUserController.FK_userID  = tblUser.id
SET tblUserController.passwordWrong = 1, tblUserController.updatedAt = '2019-10-04 12:19:51'
WHERE  tblUserController.FK_userID = '2'
AND ( tblUserController.FK_loggedID = '1' 
OR tblUser.updatedAt < DATE_ADD(NOW(), INTERVAL 3 HOUR) )
";
$this->db->query($sqlRawQuery);

How to convert sql query to codeigniter query

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);

Related Topics