Mysql Sort Order Problem

[madhead29]

Hi All, I am trying to get my sort order system working but I am having problems getting it working properly. What I am trying to do it when the user clicks next it will go to the next record depending on the sort order. Say the user is on an image with the sort order 4 when you click next I want it to get the sort order of 5 and if you click prev it will go to 3 as the image you are current on it sort order 4. Currently the system uses the id but now I want it to use the sort order. There is not set number of images so there is no set number of sort order so its not like i can tell it to get the order using $_GET and using a value from the url. My current code that uses id to sort is below but now i want it to use the field sort_order as each image as a sort_order number. I have tried changing the Order in the queries to sort by sort_order but nothing. I think i need to change the id>$id and id<$id part but im not sure so i posted here. Thanks.

This part of the code gets the information for the current image that is displayed:

	<?php 

$getimage_query="SELECT id,title,image_url,web_url FROM gallery WHERE id=$id AND area=$area;";

$getimage_result=@mysql_query($getimage_query);

$getimage_data=@mysql_fetch_array($getimage_result)

?>

<div id="view-full-image"><div align="center"><a href="<?php echo $getimage_data['image_url']; ?>"><img src="<?php echo $getimage_data['web_url']; ?>" alt="<?php echo $getimage_data['title']; ?>" border="0"  /></a></div>

This part of the code gets the next information for the next link:

</div>
<?php
	$getimage_query_next="SELECT id,title,image_url,web_url,sort_order FROM gallery WHERE id>$id AND area=$area ORDER BY id;";

$getimage_result_next=@mysql_query($getimage_query_next);

$getimage_data_next=@mysql_fetch_array($getimage_result_next)
?>

This part of the code gets the data for the Previous link:

<?php
	$getimage_query_prev="SELECT id,title,image_url,web_url,sort_order FROM gallery WHERE id<$id AND area=$area ORDER BY id DESC;";

$getimage_result_prev=@mysql_query($getimage_query_prev);

$getimage_data_prev=@mysql_fetch_array($getimage_result_prev)
?>

This part of the code gets the information from the queries above and creates the links for Previous link and next link:

</div>

</div>
<div id="gallery-links">
<div id="prev"><?php if (empty($getimage_data_prev)){ }else{ echo "<a href=\"view-image-".$getimage_data_prev['id']."-".$area.".php\">< Previous</a>"; } ?></div>
<div id="gallery-back"><a href="<?php if ($area=="1"){ echo "gallery.php"; }else{ echo "gallery-art.php"; } ?>">Back to Gallery</a></div>
<div id="next"><?php if (empty($getimage_data_next)){ }else{ echo "<a href=\"view-image-".$getimage_data_next['id']."-".$area.".php\">Next ></a>"; } ?></div>
</div>

Any help would be great thanks.

[devinemke]

the basic idea for ( first | prev | next | last )

<?php
if (isset($_GET['id'])) {$id = $_GET['id'];}

$result = @mysql_query('SELECT title, image_url, web_url, sort_order FROM gallery WHERE id = ' . $id) or exit(mysql_error());
if (mysql_num_rows($result))
{
	$row = mysql_fetch_assoc($result);
	extract($row);
}
else
{
	exit('no rows found matching ' . $id);
}

$result = mysql_query('SELECT MIN(sort_order) AS first, MAX(sort_order) AS last FROM gallery') or exit(mysql_error());
if (mysql_num_rows($result))
{
	$row = mysql_fetch_assoc($result);
	extract($row);
}

$result = @mysql_query("SELECT id AS prev FROM gallery WHERE sort_order < $sort_order LIMIT 1") or exit(mysql_error());
if (mysql_num_rows($result))
{
	$row = mysql_fetch_assoc($result);
	extract($row);
}

$result = @mysql_query("SELECT id AS next FROM gallery WHERE sort_order > $sort_order LIMIT 1") or exit(mysql_error());
if (mysql_num_rows($result))
{
	$row = mysql_fetch_assoc($result);
	extract($row);
}

echo '<div align="center"><a href="' . $image_url . '"><img src="' . $web_url . '" alt="' . $title . '" border="0"></a></div>';

if (isset($first)) {echo '<a href="' . $_SERVER['PHP_SELF'] . '?id=' . $first . '">first</a> ';}
if (isset($prev)) {echo '<a href="' . $_SERVER['PHP_SELF'] . '?id=' . $prev . '">prev</a> ';}
if (isset($next)) {echo '<a href="' . $_SERVER['PHP_SELF'] . '?id=' . $next . '">next</a> ';}
if (isset($last)) {echo '<a href="' . $_SERVER['PHP_SELF'] . '?id=' . $last . '">last</a> ';}
?>

[madhead29]

Thanks devinemke - I am still new to php and mysql and have a few things I want to make sure I know. Sorry if these are stupid questions. None of your code is using register globals is it? Also where you have $first and the other links at the bottom where and how is this linked to the other parts using AS in the query? Is this safe as the $first is not declared? Sorry to ask supid questions I just want to understand the code as I want to learn rather than just copying code without understanding it. Sorry to be cheeky but could you label the code you put so I can learn from it and understand it. The reason I am asking it because I have never seen a query done like that espesically the AS part. Im not asking all this for a quick project I am asking it so I can learn.

Thanks Again.

[Weedpacket]

The key to your answering your questions is in devinemke's use of [man]extract[/man].

[madhead29]

Thanks for the reply Weedpacket but I dont see where this is being used? Also how does it answer my questions?

Thanks.

[Weedpacket]

madhead29 wrote:

but I dont see where this is being used?

It's used in the lines where it appears (lines 8, 19, 26, and 33 of the posted sample).