May 13, 2012
Make counter in sql query
Question by Viktors Golubevs
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 by Starx
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);
//.........
}
