mysqlcodeigniteractiverecordconcatenationsql-server-group-concat

Need Correct Format for CONCAT sql query. Codeigniter Active Record


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

Solution

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

    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