php - Rank query not working

611

I have looked at various SO questions for how to do count and rank. I have a field in my database called vDestination, and I want to get a count and rank of each destination. I am using Codeigniter. I have the following:

$sql = ("SET @curRank := 0;\r\n
            SELECT vDestination, COUNT(*) AS count, @curRank := @curRank + 1 AS rank FROM tbl_user_trip ORDER BY vDestination");

This looks to me like valid SQL syntax, but I keep getting database errors. Will someone please point out to me what is wrong with this query?

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 vDestination, COUNT(*) AS count, @curRank := @curRank + 1 AS rank FROM tb' at line 3

SET @curRank := 0;
SELECT vDestination, COUNT(*) AS count, @curRank := @curRank + 1 AS rank
FROM tbl_user_trip ORDER BY vDestination

This is the error I am getting.

408

Answer

Solution:

Try like this:

 $this->db->query("SET @curRank := 0");
 $this->db->query("SELECT vDestination, COUNT(*) AS count, @curRank := @curRank + 1 AS rank FROM tbl_user_trip ORDER BY vDestination");
915

Answer

Solution:

Working solution is as follows:

function getMostPopularDestinations()
    {
        $this->db->query("SET @curRank := 0");
        $query = $this->db->query("SELECT vDestination, COUNT(*) AS count, @curRank := @curRank + 1 AS rank FROM tbl_user_trip GROUP BY vDestination ORDER BY vDestination");
        return $query->result_array();
    }

So as @tyro suggested, but with a GROUP BY as well.

People are also looking for solutions to the problem: php - Remove dots below required stars in Wordpress checkout-form

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.