Hi all im new to the forums ands im getting more into php but im stuck on a pagination problem,
I am trying to get my images to display with pagination in order of the date field, so far the prev and next links only work off of the image id is does any one have any idea of how i can get the pagination to work using the date field? obviously i still need the image id to display the correct data but i need the image id to correspond with the next date, below is the code im using your help is appreciated :-)
PHP Code:
<?php
// get image id from URL
if (!isset($_GET['image'])) {

    echo 
"Image id is not defined";

} else {
// get the image id
    
$imageId $_GET['image'];
    
$sql  "SELECT im_id, im_album_id, im_title, im_description, im_image, DATE_FORMAT(im_date, '%d-%m-%Y')AS im_date , al_name

             FROM tbl_image im, tbl_album al 

             WHERE im_id = 
$imageId AND im.im_album_id = al.al_id";
    
$result mysql_query($sql) or die('Error, get image info failed. ' mysql_error());
    if (
mysql_num_rows($result) == 0) {

        
// can't find image with that id

    
?>

<p align="center">Image not found. </p>

<?php    

    
} else {    

        
$image mysql_fetch_assoc($result);

        
// find the previous and next image in this album
        // set the initial value for previous and next image id

        
$prev $next 0;

        
// get the previous image

        
$sql  "SELECT im_id, im_title, DATE_FORMAT(im_date, '%d-%m-%Y')AS im_date 

                 FROM tbl_image 

                 WHERE im_id < 
$imageId AND im_album_id = {$image['im_album_id']}

                 ORDER BY im_date ASC

                 LIMIT 0, 1"
;

        
$result mysql_query($sql) or die('Error, get image info failed. ' mysql_error());

        if (
mysql_num_rows($result) > 0) {

            
$row mysql_fetch_assoc($result);

            
$prev $row['im_id'];

        }

        
// get the next image

        
$sql  "SELECT im_id, im_title, DATE_FORMAT(im_date, '%d-%m-%Y')AS im_date

                 FROM tbl_image 

                 WHERE im_id > 
$imageId AND im_date AND im_album_id = {$image['im_album_id']}

                 ORDER BY im_date DESC

                 LIMIT 0, 1"
;

        
$result mysql_query($sql) or die('Error, get image info failed. ' mysql_error());

        if (
mysql_num_rows($result) > 0) {

            
$row mysql_fetch_assoc($result);

            
$next $row['im_id'];

        }        

        

?>

 

<table width="610" border="0" cellpadding="2" cellspacing="1">

<tr>
  <td width="320" rowspan="2" align="center" valign="top"><img src="images/events/<?php echo $image['im_image']; ?>"></td>
  <td valign="top"><div id="titletext"><?php echo $image['im_title']; ?></div></td>
</tr>
<tr> 

        <td width="279" valign="top"><div id="titletext"><?php echo $image['im_date']; ?></div><br>

<div id="textinfo"><?php echo $image['im_description']; ?></div></td>
  </tr>

    <tr>

        <td width="320" style="text-align:left"></td>

      <td ></td>
    </tr>

    <tr> 

        <td width="320"> 

            <?php

if ($prev 0) {

?>

            <a href="comingevents.php?page=image-detail&album=<?php echo $image['im_album_id']; ?>&image=<?php echo $prev?>"><img src="prevlabel.gif" alt="PREV" border="0"  /></a> 

            <?php

}

?>      </td>

      <td>            

            <?php

if ($next 0) {

?>

            <a href="comingevents.php?page=image-detail&album=<?php echo $image['im_album_id']; ?>&image=<?php echo $next?>"><img src="nextlabel.gif" alt="NEXT" border="0" /></a> 

        <?php

}

?>  

      </div></td>
    </tr>
</table>



<?php

    
}

}

?>