SET variable on codeigniter SQL

Question

I've tried this code:

SET @num := 0, @type := NULL;
SELECT categories_name, products_name, products_url, categories_id, 
       @num := IF( @type = categories_name, @num +1, 1 ) AS row_number,
       @type := categories_name AS dummy
  FROM (
        SELECT categories_name, products_name, products_url, ptc.categories_id
          FROM toc_products_description pd
         INNER JOIN toc_products_to_categories ptc ON pd.products_id = ptc.products_id
         INNER JOIN toc_categories_description cd ON cd.categories_id = ptc.categories_id
               AND pd.language_id =1
               AND cd.language_id =1
        ) AS x
 GROUP BY x.categories_name, x.products_name, x.products_url, x.categories_id
HAVING row_number <=2

On SQL (and work well), and now I need this code to be implemented on codeigniter with query(). The problem is I can't insert

SET @num := 0, @type := NULL;

On query(), because it's always give error messages when I inserted it.

I have done this:

$query="SELECT categories_name, products_name, products_url, categories_id, @num := IF( @type := categories_name, @num +1, TRUE) AS row_number, @type := categories_name AS dummy
                FROM (
                SELECT categories_name, products_name, products_url, ptc.categories_id
                FROM toc_products_description pd
                INNER JOIN toc_products_to_categories ptc ON pd.products_id = ptc.products_id
                INNER JOIN toc_categories_description cd ON cd.categories_id = ptc.categories_id
                AND pd.language_id =1
                AND cd.language_id =1
                ) AS x
                GROUP BY x.categories_name, x.products_name, x.products_url, x.categories_id
                HAVING row_number <=5";        
        $result = $this->db->query($query);

and I cant write anything before SELECT syntax because it will return error message when I try it.

Muhammad Raheel · Accepted Answer · 2013-02-22 10:37:27Z

8

You will have to run two seperate queries for this

$query  =   "SET @num := 0, @type := NULL";
$this->db->query($query);
$new_query  =   "
                SELECT categories_name, products_name, products_url, categories_id, 
                       @num := IF( @type = categories_name, @num +1, 1 ) AS row_number,
                       @type := categories_name AS dummy
                  FROM (
                        SELECT categories_name, products_name, products_url, ptc.categories_id
                          FROM toc_products_description pd
                         INNER JOIN toc_products_to_categories ptc ON pd.products_id = ptc.products_id
                         INNER JOIN toc_categories_description cd ON cd.categories_id = ptc.categories_id
                               AND pd.language_id =1
                               AND cd.language_id =1
                        ) AS x
                 GROUP BY x.categories_name, x.products_name, x.products_url, x.categories_id
                HAVING row_number <=2";
$this->db->query($new_query);

answered Feb 22, 2013 at 10:37

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

codArt Over a year ago

Thank you very much, sir! I've tried this before and forgot to add first query(). And now it's work well! Thank you.

Dan Metheus · Accepted Answer · 2013-02-22 07:41:01Z

0

Create a stored procedure with your SQL and call the stored procedure from query()

answered Feb 22, 2013 at 7:41

Dan Metheus

1,43810 silver badges16 bronze badges

2 Comments

codArt Over a year ago

I can't write any syntax before SELECT

Dan Metheus Over a year ago

The SET ... and your whole select statement would all be in the same stored procedure so your $query would be just be CALL YourSPName (mysql) or EXEC YourSPName (sql server)

André Kool · Accepted Answer · 2018-02-26 16:03:01Z

0

Write in the model

public function select_datos($param1) {
    if ($param1 == 'planilla') {
        $query1 = "SET @numero := 0, @type := NULL";
        $this->db->query($query1);
        $this->db->select('*');
        $this->db->select("@numero:=@numero+1 AS 'posicion'");
        $this->db->from($param1);
        $query = $this->db->get();
        $row['planilla'] = $query->row();
    } else {
        $query = '1'; //    Error
    }
    return $query->result();
}

edited Feb 26, 2018 at 16:03

André Kool

4,97812 gold badges36 silver badges44 bronze badges

answered Feb 26, 2018 at 15:42

Jumaycito

212 bronze badges

1 Comment

André Kool Over a year ago

Please edit your answer to explain why this piece of code answers the question.

Collectives™ on Stack Overflow

SET variable on codeigniter SQL

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related