CODEWITHSUNDEEP
phpmysqlcodeignitervariables
Query Master
Query Master

Reputation: 7097

setting mysql user-defined variable with query-builder

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

Upvotes: 4

Views: 6378

Answers (2)

IT Advanture
IT Advanture

Reputation: 49

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

Upvotes: 2

safarov
safarov

Reputation: 7804

Try separate the query

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

Upvotes: 12

Related Questions