CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordquery-builder
Spha C
Spha C

Reputation: 29

Convert raw SQL containing DATEDIFF and LIKE to CodeIgniter active record syntax

SELECT 
    case_id,
    serialnumber,
    DateLastHere,
    Bookedindate,
    DATEDIFF(DateLastHere,Bookedindate) 
FROM `cases` 
WHERE serialnumber like 'TTTT46'

Upvotes: 0

Views: 725

Answers (2)

mickmackusa
mickmackusa

Reputation: 47864

  • If you want wildcards on both sides of the LIKE search value:

    SELECT `case_id`, `serialnumber`, `DateLastHere`, `Bookedindate`, DATEDIFF(DateLastHere,Bookedindate)
FROM `cases`
WHERE `serialnumber` LIKE '%TTTT46%' ESCAPE '!'

    CodeIgniter:

    $this->db
    ->select([
        'case_id',
        'serialnumber',
        'DateLastHere',
        'Bookedindate',
        'DATEDIFF(DateLastHere,Bookedindate)'
    ])
    ->like('serialnumber','TTTT46')
    ->get('cases')

  • If you don't want any wildcards on the LIKE search value:

    SELECT `case_id`, `serialnumber`, `DateLastHere`, `Bookedindate`, DATEDIFF(DateLastHere,Bookedindate)
FROM `cases`
WHERE `serialnumber` LIKE 'TTTT46' ESCAPE '!'

    CodeIgniter:

    $this->db
    ->select([
        'case_id',
        'serialnumber',
        'DateLastHere',
        'Bookedindate',
        'DATEDIFF(DateLastHere,Bookedindate)'
    ])
    ->like('serialnumber','TTTT46')
    ->get('cases');

  • If you want to make a whole string match, then LIKE is not helpful:

    SELECT `case_id`, `serialnumber`, `DateLastHere`, `Bookedindate`, DATEDIFF(DateLastHere,Bookedindate)
FROM `cases`
WHERE `serialnumber` = 'TTTT46'

    CodeIgniter:

    $this->db
    ->select([
        'case_id',
        'serialnumber',
        'DateLastHere',
        'Bookedindate',
        'DATEDIFF(DateLastHere,Bookedindate)'
    ])
    ->get_where('cases', ['serialnumber' => 'TTTT46']);

  • If you want to explicitly wrap the DATEDIFF columns in identifier quotes, use escape_identifiers():

    SELECT `case_id`, `serialnumber`, `DateLastHere`, `Bookedindate`, DATEDIFF(`DateLastHere`,`Bookedindate`)
FROM `cases`
WHERE `serialnumber` = 'TTTT46'

    CodeIgniter:

    $this->db
    ->select([
        'case_id',
        'serialnumber',
        'DateLastHere',
        'Bookedindate',
        sprintf(
            'DATEDIFF(%s,%s)',
            $this->db->escape_identifiers('DateLastHere'),
            $this->db->escape_identifiers('Bookedindate')
        )
    ])
    ->get_where('cases', ['serialnumber' => 'TTTT46']);

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can do it like this

$data   =   array(
            'case_id',
            'serialnumber',
            'DateLastHere',
            'Bookedindate',
            'DATEDIFF(DateLastHere,Bookedindate)'
            );
$this->db->select($data);
$this->db->from('cases');
$this->db->like('serialnumber','TTTT46');
$query = $this->db->get()->result_array();

Upvotes: 3

Related Questions