在sql查询中创建计数器

Question

I am using codeigniter and have working query which take user 3 images. I want to make a count an give every image a number 1,2,3,4,5,6,7 ... and want that query output

-number (count),
-id,
-image,
-date

my sql query :

function bar_images_first($user_id)
{   
    $sql = "SELECT id, image, UNIX_TIMESTAMP(date) as date FROM images WHERE user_id = 3 LIMIT 3";
    $query = $this->db->query($sql, $user_id);
    return $query->result_array();
}

Is it possible to do counter in query?

Answer 1

It is possible by setting a SQL parameter as

SET @cnt := 0;
SELECT
    @cnt := @cnt + 1,
    id,
    image,
    UNIX_TIMESTAMP(date) as date 
FROM images WHERE user_id = 3 LIMIT 3";

But such multiple statements cannot be executed from the PHP's mysql_query() method. But, mysqli function like mysqli_multi_query() does allow to execute multiple queries too, so if possible use mysqli method rather than the AR methods.

However, you can run multiple sets of query one by one.

$query = array(
    "SET @cnt := 0;",
    "SELECT
        @cnt := @cnt + 1,
        id,
        image,
        UNIX_TIMESTAMP(date) as date 
    FROM images WHERE user_id = 3 LIMIT 3"
);

foreach($query as $qry) {
     $result= $this->db->query($qry);
     //.........
}