Using MySQL user-defined variables with database class

[eluser]nsbingham[/eluser]
I'm attempting to dynamically create a rank # based on a sort of a column in a table using something like the following:

Code:

SET @rank:0;SELECT *, @rank := @rank + 1 FROM table_name;

CI is throwing a "You have an error in your SQL syntax" whenever I attempt to include the "SET @rank:0" statement in the query.

Code:

$query = $this->db->query("SET @rank:0;SELECT *, @rank := @rank + 1 FROM table_name;");

Has anyone else had success with this or is it even possible to use MySQL user-defined variables with CI's Database Class or ActiveRecord?

I'm needing to do some sorting based on the rank and so that's why I'm trying to avoid creating another column and storing the rank.

Thanks,
Nathan

[eluser]danmontgomery[/eluser]
You can't run multiple queries at once with php's mysql implementation. You might be able to do something like this by switching to mysqli (you would probably have to prevent CI from escaping queries)... But to me it seems like a whole lot less trouble to just keep a counter in PHP once the results are retrieved...?

[eluser]nsbingham[/eluser]
noctrum, thanks for the quick reply. I was afraid of something like that. I thought about using a stored proc as well, but it looks like that would require mysqli too.

The issue with ranking after the dataset is loaded is that my rank is based on a particular column and the user might sort by another column in the table, for example a name alphabetically ascending. I want to preserve the rank that is based on another column, but still allow the alphabetical sort. I'd have to load the whole dataset into PHP (I'm using pagination to try to avoid that) and then sort by my initial column to rank, store the rank in the dataset, and then sort alphabetically in PHP.

Thanks,
Nate

[eluser]EBS Promo[/eluser]
Here is an example of accessing user-defined mysql variables in codeigniter.

I goofed my query the same way you did initially. The correct way to do it is in the code samples.

As for using a counter in PHP... hey that's great if you wanna run a loop every time when you could just pass a 2-line sql query and do it where it should be done, using minimal code and minimal processing/memory

[eluser]danmontgomery[/eluser]
I'm not convinced that

Code:

SET @pos=0;
@pos := @pos +1

is less code or processing time than

Code:

$count = 0;
$count++;

But regardless, the contribution is definitely a useful one.

[eluser]EBS Promo[/eluser]
Look at it this way, you are doing an update with a single MySQL query instead of loading each record and updating them one at a time in a PHP loop.

I'm not sure about your experiences, but I have never seen it take less resources to do it your way. In case you misundertsand the code reference... no PHP loops are used, only 1 MySQL variable and a single query is necessary.

I have always been of the persuasion to use your database engine to it's full potential. Using PHP to do what the db driver can already do with simple commands is overkill.

[eluser]danmontgomery[/eluser]
I absolutely agree, but the question wasn't about updating multiple records, it was about getting a counter with records returned from a select statement. These records have to be looped through to be used in PHP anyways, I can't see any real benefit in doing it one way over the other.

[eluser]EBS Promo[/eluser]
[quote author="nsbingham" date="1271723986"]I'm attempting to dynamically create a rank # based on a sort of a column ... I’m needing to do some sorting based on the rank[/quote]

Maybe if the original author could clarify it would help, but my example is a solution to what he said he needed to achieve. Granted he doesn't want to add a column, but I believe it's needed for his type of sorting requirements to be modifyable.

The only time I have seen the need for that type of reference, it made more sense to store the value in a column (ie: a sequential value you can modify for use with ORDER BY). Otherwise the number would not be important, in which case you could just pull the records with no ORDER BY and achieve the same results (exluding the "rank" alias which was not assigned as such in his example).