Get a row position with condition in mysql

Question

i have some trouble with mysql query. I just figure out, how to get a row position in mysql with condition. Let me explain what i want to achieve.

I have a table gallery and it look like this :

id_gallery    source   status    post
   1          img1      last     2012/12/11
   5          img2      new      2013/01/01
   7          img3      new      2013/01/01
   10         img4      last     2012/12/11
   22         img5      last     2012/12/14
   30         img6      last     2012/12/15

I call the image in page (ex:test1.php) with this query and give the result like this: Here is my query:

select * from gallery where status ='last' order by post DESC

Here is my result :

id_gallery   source    status    post
   30         img6      last     2012/12/15 
   22         img5      last     2012/12/14
   10         img4      last     2012/12/11
    1         img1      last     2012/12/11

In page gallery i make a query like this :

select * from gallery order by post desc

and give the result like this :

id_gallery    source   status    post
       5          img2      new      2013/01/01
       7          img3      new      2013/01/01
       30         img6      last     2012/12/15
       22         img5      last     2012/12/14
       1          img1      last     2012/12/11
       10         img4      last     2012/12/11

what i want to achieve is something like this :

      id_gallery    source   status    post      position
       5          img2      new      2013/01/01      1
       7          img3      new      2013/01/01      2
       30         img6      last     2012/12/15      3
       22         img5      last     2012/12/14      4
       1          img1      last     2012/12/11      5
       10         img4      last     2012/12/11      6

And final result would become like this

          id_gallery    source   status    post      position
           30         img6      last     2012/12/15      3
           22         img5      last     2012/12/14      4
           1          img1      last     2012/12/11      5
           10         img4      last     2012/12/11      6

I want to know the right position of the image because in second page(ex:gallery.php), i have a lot of image. And in the first page(test1.php), i just select 5 img with status last and order by post DESC. I want make a link to page gallery.php and i need a correct position. When i get a correct position, i can make a link to each of image and it would be something like this:

<a href='http://localhost/testing/gallery/<?=$result[position]?>.htm'><img src='http://localhost/testing/<?=$result[source]?>.jpg' /></a>

//or in the html would be like this

<a href='http://localhost/testing/gallery/1.htm'><img src='http://localhost/testing/img6.jpg' /></a>

So, if i can get a correct row position, i can make a link that direct to the page correctly.

Can anyone tell me how can i achieve that? I will appreciated your answer,thx before

Answer 1

The simplest way would probably be to partition your query by the columns you want such as position, post, status... then order by within the partition. Analytic functions will help - there are many examples in documentation and on the web. Add Row_Number() for position or Rank()...:

SELECT * FROM
( 
 SELECT deptno, ename, sal
    , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal desc) Position_Within_Dept
    , RANK () OVER (PARTITION BY deptno ORDER BY sal desc) ranks
    , DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal desc) d_ranks
   FROM emp_test
)
--WHERE rno = 3
 ORDER BY deptno, sal DESC
/

It is always good idea to post create table and insert scripts. No one has time to do this for you.

Answer 2

SELECT a.*, @row:=@row+1 AS `Position`
FROM   gallery a, (SELECT @row:=0) s
WHERE  status = 'last' 
ORDER  BY post DESC

• SQLFiddle Demo

UPDATE 1

SELECT *
FROM
  (
    SELECT a.*, @row:=@row+1 AS `Position`
    FROM   gallery a, (SELECT @row:=0) s
    ORDER  BY case when status = 'new' then 0 else 1 END ASC,
               post DESC, id_gallery ASC
  ) a
WHERE status = 'last'

• SQLFiddle Demo

Answer 3

Do it in your php code (Instead of adding new row in the SQL's temp generated table):

$query = mysql_query("YOUR QUERY");
$counter = 0;
while($rows = mysql_fetch_assoc($query)){
     $counter++;
     echo "<a...............".$counter.">.html'><img..../></a>";

}