Active record query max length?

[eluser]bartlomiejr[/eluser]
I have many similar functions to that below but this one doesn't work. SQL is 100% tested. What am i doing wrong? Maybe this query is too long for CI Active record?

Model function:

Code:

public function getActualRank($user_id, $id_quiz)
{
    $query = $this->db->query("SET @rownum := 0;
        SELECT rank
        FROM (
        SELECT @rownum := @rownum +1 AS rank, score, user_id, quiz_id, finish
        FROM scores
        ORDER BY score DESC, finish ASC
        ) AS result
        WHERE user_id = ".$user_id." AND quiz_id = ".$id_quiz."");
    
    foreach ($query->result() as $row)
    {
        return $row->rank;
    }
    // return $query->result_array();
}

Controller:

Code:

$rank = $this->Home_model->getActualRank(21, 1);
echo '<pre>';
print_r($rank);
echo '</pre>';

[eluser]juanvillegas[/eluser]
I think you should discard the "query too long issue". Does that program output something? You should post the error to help us debug that.
Just curious here, why are you using raw queries instead of active record? The guys behind active record would be very sad hah

[eluser]bartlomiejr[/eluser]
When i check manual in MySQL i get desired effect:


I think my syntax is correct.
Error:

Code:

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT rank FROM ( SELECT @rownum := @rownum +1 AS rank, score, user_i' at line 2

SET @rownum := 0; SELECT rank FROM ( SELECT @rownum := @rownum +1 AS rank, score, user_id, quiz_id, finish FROM scores ORDER BY score DESC, finish ASC ) AS result WHERE user_id =4 AND quiz_id =1;

Filename: /Users/Bartek/apps/bartek.quiz/models/user_model.php

Line Number: 113

[eluser]bartlomiejr[/eluser]
I get yet a similar error in this function below. Both sql inserts shold be together. When I paste sql from CI error in mysqladmin everything works. Please help.

Code:

public function createUser()
    {
        $time = date("Y-m-d H:i:s");

        $insert = "INSERT INTO users (id_fb, name, first_name, last_name, email, link, first_login) VALUES (" .
                  "'" . $this->me['id'] . "', " .
                  "'" . $this->me['name'] . "', " .
                  "'" . $this->me['first_name'] . "', " .
                  "'" . $this->me['last_name'] . "', " .
                  "'" . $this->me['email'] . "'," .
                  "'" . $this->me['link'] . "'," .
                  "'" . $time . "'); " .
                  "INSERT INTO scores( user_id ) VALUES (LAST_INSERT_ID( ));";
        
        $this->db->query($insert);
    }

Error:

Code:

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO scores( user_id ) VALUES (LAST_INSERT_ID( ))' at line 1

INSERT INTO users (id_fb, name, first_name, last_name, email, link, first_login) VALUES ('10000100000047', 'Bartek Rozanski', 'Bartłomiej', 'Róşański', '[email protected]','http://www.facebook.com/profile.php?id=10000100000047','2011-05-07 02:39:04'); INSERT INTO scores( user_id ) VALUES (LAST_INSERT_ID( ));

Filename: /Users/Bartek/apps/bartek.quiz/models/user_model.php

Line Number: 65

[eluser]Nick_MyShuitings[/eluser]
[quote author="juanvillegas" date="1304740120"]I think you should discard the "query too long issue". Does that program output something? You should post the error to help us debug that.
Just curious here, why are you using raw queries instead of active record? The guys behind active record would be very sad hah[/quote]

Wrong... the guys behind Active Record would be glad... you do realize Active record is just a crutch for idiots who can't write SQL? Why do you think they've not updated it in the last forever adding in the oh so demanded ability to do nested Where statements or selects within selects.

If he is able to write RAW SQL, then he should bloody well do so, and avoid the unnecessary processing overhead of Active Record.

He should however, take advantage of query bindings for security.

[/end rant]

[eluser]Nick_MyShuitings[/eluser]
That said... have you checked the actual MySQL logs to see what command is being run by CI? I've found there are sometimes issues when using MySQL variables @asdfasdf... and when attempting to run two queries with one $this->db->query() command. This could be due to how the driver is written, but I've never taken the time to slueth that code.

[eluser]bartlomiejr[/eluser]
I solved both problems. the problem was that php mysql_query or $ this-> db-> query () doen't support multiple queries. Solution:

Code:

public function getRank($id_quiz)
    {
        $sql1 = "SET @rownum := 0;";    
        $sql2 = "SELECT rank
                FROM (
                SELECT @rownum := @rownum +1 AS rank, score, user_id, quiz_id, finish
                FROM scores
                ORDER BY score DESC, finish ASC
                ) AS result
                WHERE user_id =".$this->id." AND quiz_id =".$id_quiz.";";

        $this->db->query($sql1);
        $query = $this->db->query($sql2);
        foreach ($query->result() as $row)
        {
            return $row->rank;
        }
    }

[eluser]Nick_MyShuitings[/eluser]
Kudos... and even better a thanks for having the etiquette to make sure to post the solution.