php query asc desc

[corillo181]

how would i put a desc and asc order so only the next 2 and the last 2 pictures are shown?

<? 
$selectless=mysql_query("SELECT * FROM tra_gallery_photo WHERE album_id='$album' AND photo_id<'$photo_id' LIMIT 2 ")or die(mysql_error());
while($lessthumb=mysql_fetch_array($selectless)){?>
<a href="displayimage.php?album=<?=$album?>&ptd=<?=$lessthumb['photo_id']?>"><img src="<?=$lessthumb['thumb_path']?>" alt="<?=$lessthumb['thumb_path']?>" /></a>
<?
}?><?
$picture=mysql_query("SELECT thumb_path FROM tra_gallery_photo WHERE album_id='$album' AND photo_id='$photo_id'")or die(mysql_error());
$display=mysql_fetch_array($picture);
?>
<img src="<?=$display['thumb_path']?>" alt="<?=$display['image_path']?>"/>
<? 
$select=mysql_query("SELECT * FROM tra_gallery_photo WHERE album_id='$album' AND photo_id>'$photo_id' LIMIT 2")or die(mysql_error());
while($morethumb=mysql_fetch_array($select)){?>
<a href="displayimage.php?album=<?=$album?>&ptd=<?=$morethumb['photo_id']?>"><img src="<?=$morethumb['thumb_path']?>" alt="<?=$morethumb['thumb_path']?>"/></a>
<?
}?>
[code]

[/code]

[Full-Demon]

To use desc or asc you can use the ORDER command in your query, followed by the column you want to order, followed by DESC or ASC...

 

Hope that helps.

 

FD

[corillo181]

i try using a desc and if the current image is 22 it display the fallow wing  | 22 | 21 | 23 | 24 | 25 |

 

if i use asc it goes back tot he first image and displays | 1 | 2 | 23 | 24 | 25 |

 

notice that i only apply to the first 2 images before the current image.

[corillo181]

no answer?

[1bradillac]

Say you have 50 rows in a table. To show 1 | 2 | 22 | 49 | 50

 

"SELECT * FROM table ORDER BY id ASC LIMIT 2"

and

"SELECT * FROM table ORDER BY id DESC LIMIT 2"

 

To show 20 | 21 | 22 | 23 | 24

 

$currentimage = 22;
$currentimage = $currentimage - 2;

$blah = mysql_query("SELECT * FROM table WHERE id = $currentimage ORDER BY id ASC" LIMIT 5);
while ($row = mysql_fetch_array($blah)){
echo $row["id"];
}

 

 

[corillo181]

that method does not work if i remove a record.. because is going to search for the number -2 so if the number are not in order it wont work.

[corillo181]

no pros here to answer that question?

[per1os]

no pros here to answer that question?

 

Apparently not.

 

At anyrate you are doing it wrong. I would suggest looking into Pagination, a nice tutorial is on this site.

 

The LIMIT function usage you are doing is incorrect.

 

LIMIT HOWMANY, START

 

So if you wanted to limit it to 5 images starting at 20 it would look like so:

 

LIMIT 5,20

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

Make sense?

[corillo181]

that's the problem that i'm  working on a gallery the number are always going to be changing, i had it before but someone deleted my code by mistake. here is a website of what i have in mind.

 

http://www.lostraficante.com/user/displayimage.php?album=138&pos=2

 

see there is the 2 past picture and the 2 next to come and the current in the middle and they not by order of numbers they are just by > or < the middle picture.

[Wildbug]

(SELECT * FROM tra_gallery_photo WHERE album_id=xx ORDER BY ordercol LIMIT 2) UNION (SELECT * FROM tra_gallery_photo WHERE album_id=xx ORDER BY ordercol DESC LIMIT 2) ORDER BY ordercol

 

This will give you the first two and last two rows where "ordercol" is the column by which you are ordering the results.

[corillo181]

the result is not even close to what the website looks like above

[Wildbug]

Oh, sorry, I misread your post.

 

You should probably be doing it like has been already suggested above, using "....LIMIT offset, 5".

[corillo181]

i did tried it that way, but when i click on a image they disappear one by one until there i only one left.

[Wildbug]

Well, then troubleshoot your code as there is a bug somewhere in its logic.

[corillo181]

i guess..

[corillo181]

i went with this but if anyone ever finds  better solution let me know.

$selectless=mysql_query("SELECT * FROM tra_gallery_photo WHERE album_id='$album' AND photo_id<'$photo_id' AND photo_id>'$photo_id'-3 LIMIT 3")or die(mysql_error());