setting mysql user-defined variable with query-builder

Question

I have an issue with setting mysql user-defined variables with codeigniter. How am I able to use SET with CI query builder?

SET @weekVideoCount := (SELECT COUNT(*) FROM videos v );

SELECT @weekVideoCount;

When I execute this Query on Sqlyog the result will be successfully shown but if I call this Query in the model like this

function getWeeklyUserData(){
        $query= $this->db->query("SET @weekVideoCount := (SELECT COUNT(*) FROM videos v);
                                SELECT @weekVideoCount;
                                ");
        return $query->result();
    }

the error has generated

enter image description here

safarov · Accepted Answer · 2012-04-09 13:37:13Z

12

Try separate the query

 $this->db->query("SET @weekVideoCount := (SELECT COUNT(*) FROM videos v)");
 $query= $this->db->query("SELECT @weekVideoCount");

edited Apr 9, 2012 at 13:37

answered Apr 9, 2012 at 13:30

safarov

7,7942 gold badges38 silver badges52 bronze badges

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

1 Comment

babycakes Over a year ago

FYI this works too SELECT @rn:=@rn+1 AS rank FROM (SELECT (SELECT @rn:=0) )

IT Advanture · Accepted Answer · 2012-04-11 15:13:07Z

2

you should know $this->db->query() execute only one sql statement just like mysql_query.

answered Apr 11, 2012 at 15:13

IT Advanture

491 silver badge5 bronze badges

Collectives™ on Stack Overflow

setting mysql user-defined variable with query-builder

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related