CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Viktors
Viktors

Reputation: 935

Make counter in sql query

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?

Upvotes: 0

Views: 1069

Answers (2)

Ja͢ck
Ja͢ck

Reputation: 173552

To run as a single query:

SELECT @curRow := @curRow + 1 AS row_number, id, image, UNIX_TIMESTAMP(date) as date
FROM images
JOIN (SELECT @curRow := 0) r
WHERE user_id = 3
LIMIT 3

This is very specific to MySQL though. If you want more portable code I would recommend making your row counter in code instead.

Edit - forgot to give due credit: MySQL - row number in recordset?

Upvotes: 2

Starx
Starx

Reputation: 78971

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);
     //.........
}

Upvotes: 3

Related Questions