CODEWITHSUNDEEP
phpmysqlcodeigniter
Prajakta
Prajakta

Reputation: 53

Convert MySQL JOIN query into CodeIgniter query

I'm trying to convert this query into code igniter query. But i don't know where to start. I'm new to code igniter.Please help. 

public function  get_loginsecuritydetails($security_date,$apt_id) {

    $sql = "SELECT s.* , IF( DATE( s.security_date ) = DATE( '$security_date' ) , 1, 0 ) AS loginstatus,a.agency_companyname FROM security s JOIN agency a ON(a.agency_id=s.security_cat) WHERE apartment_id='$apt_id'; ";
    $res=mysqli_query($sql) or die(mysqli_error());
    return $res;
}

Upvotes: 0

Views: 61

Answers (3)

Peter M
Peter M

Reputation: 1059

You should start at reading the manual, https://www.codeigniter.com/userguide3/database/index.html

Having said that the easiest solution is to simply run the query like:

$this->db->query('YOUR QUERY HERE');

The part that says 'YOUR QUERY HERE' should be replaced with the actual query like:

$this->db->query("SELECT s.* , IF( DATE( s.security_date ) = DATE( '$security_date' ) , 1, 0 ) AS loginstatus,a.agency_companyname FROM security s JOIN agency a ON(a.agency_id=s.security_cat) WHERE apartment_id='$apt_id'");

Now I'm pretty sure you'd want to clean this up and start using paramaters inside your query instead of PHP variables.

So you should look into the query builder class, https://www.codeigniter.com/userguide3/database/query_builder.html

Upvotes: 0

DeepakB
DeepakB

Reputation: 130

Try like this - 

$this->db->select("s.* , IF( DATE( s.security_date ) = DATE( '$security_date' ) , 1, 0 ) AS loginstatus,a.agency_companyname");
    $this->db->join("agency as a","a.agency_id=s.security_cat");
$this->db->where('apartment_id',$apt_id);
$querys = $this->db->get('security as s');
$result = $querys->result();

Upvotes: 1

B. Desai
B. Desai

Reputation: 16446

You can convert it in codeigniter query builder as below:

$this->db->select("s.* , IF( DATE( s.security_date ) = DATE( '$security_date' ) , 1, 0 ) AS loginstatus,a.agency_companyname");

$this->db->join("agency as a","a.agency_id=s.security_cat");

$this->db->where('apartment_id',$apt_id);

$query = $this->db->get('security as s');

$result = $query->result();

return $result;

Upvotes: 1

Related Questions