Need Correct Format for CONCAT sql query. Codeigniter Active Record

Question

I am using codeigniter's active record, but i've realized I probably need a regular SQL query for what I'm trying to accomplish. Could someone please look at this code and give me the correct format. What am I doing wrong here?

I think it's obvious what I'm trying to accomplish just by reading the code, but if you need more than just the syntax i've used (obviously in error), I'm happy to provide an explanation of code.

$today = date('Y-m-d H:i:s');

        $where = "type == pk_card AND (end_date > $today OR qty >= 1)";

        $this->db->select('id, title, pk_card_set, pk_card_number');
        $this->db->from('auctions');
        $this->db->where($where);
        $this->db->like("CONCAT(title, ' ', pk_card_number, ' ', pk_card_set)", $keyword);
        $query = $this->db->get();

Answer 1

There are number of mistakes in your code the way you are trying to query through active record

• type == pk_card -> mysql if you want to compare any value against your column you need single = sign not double == correct one is type = 'pk_card'

• type == pk_card ->Again if you want to compare any string value against column you need to wrap it with standard single quotes ' like type = 'pk_card'

• end_date > $today if you want to compare date then again you need warp date string in single quotes like end_date > '$today'

And now you can write your active record query as below

$today = date('Y-m-d H:i:s');
$keyword='Test';
$where = "(end_date > '$today' OR qty >= 1)";
$this->db->select("id, title, pk_card_set, pk_card_number ");
$this->db->from('auctions');
$this->db->where($where,null,FALSE);
$this->db->where('type','pk_card');
$this->db->like("CONCAT(title, ' ', pk_card_number, ' ', pk_card_set)", $keyword);
$query = $this->db->get();

this will generate a query similar to

SELECT 
  `id`,
  `title`,
  `pk_card_set`,
  `pk_card_number` 
FROM
  (`auctions`) 
WHERE (
    end_date > '2014-08-10 12:47:06' 
    OR qty >= 1
  ) 
  AND `type` = 'pk_card' 
  AND CONCAT(
    title,
    ' ',
    pk_card_number,
    ' ',
    pk_card_set
  ) LIKE '%Test%' 

Active Record