splitting query results

[Woodburn2006]

i have images in a database and a column in the table is 'orientation'.

 

what i want to do is for the query to get all of the images that are 'l' (landscape) first then get all of the images which are 'p' (portrait) but what i want to do is display them on seperate pages in the images gallery.

 

if you take a look at:

http://www.ontherocks.me.uk/index2.php?go=media

 

you will see that the landscape images are directly followed by the portrait images. if i can i would like the portrait images to start on the next page.

 

the page code is:

<div id="content_home">

    <div id="images_thumbs">
    <?
    $page = $_GET['page'] ? (int) $_GET['page'] : 1;

$limit= 9;                
    $query_count = "SELECT count(*) AS rowcount FROM site_images";     
    $result_count = mysql_query($query_count, $connection);     
    $row_count = mysql_fetch_array($result_count, MYSQL_ASSOC);
    $totalrows = $row_count['rowcount'];

$limitvalue = $page * $limit - ($limit);  
    $query_thumbs  = "SELECT * FROM site_images ORDER BY id LIMIT $limitvalue, $limit";         
    $result_thumbs = mysql_query($query_thumbs) or die("Error: " . mysql_error());

    
    
echo"<table width='336' border='0' cellspacing='5' cellpadding='0'>
     <tr>
     ";
$img_number=0;
while ($row_thumbs = mysql_fetch_array($result_thumbs)) {
		extract($row_thumbs);
		if($img_number == 3 OR $img_number ==6){
			echo "
			</tr>
			<tr>
			<td width='112' valign='middle' align='center'>	
			<A href='$lrg' rel='lightbox[live]' title=''><img id='$id' src='$thumb' height='75px' border='1'></a>
			</td>";
		}else{
			echo "
			<td width='112' valign='middle' align='center'>	
			<A href='$lrg' rel='lightbox[live]' title=''><img id='$id' src='$thumb' height='75px' border='1'></a>
			</td>";
		}
		$img_number++;
	}

$numofpages = ceil($totalrows / $limit);  

echo"</td>
 </tr>
 <tr>
 	<td colspan='3' class='photos_thumbs' align='center'>";
if($page > 1){  
	$pageprev = $page - 1; 
	echo"
		<a href=\"$PHP_SELF?go=media&page=".$pageprev."\"><< Previous Page</a>
		";  
}

if($page < $numofpages AND $page > 1){
        echo" :: ";  
    }

if($page < $numofpages){ 
        $pagenext = $page + 1; 
        echo"
		<a href=\"$PHP_SELF?go=media&page=".$pagenext."\">Next Page >></a>
		";
}

echo"</td></tr></table>";

?>
    </div>	
</div>

 

is there a way i can do this?

 

thanks

[soak]

This is untested so may be off by a page one way or the other but shouldn't be too far wrong I think.

 

<div id="content_home">

    <div id="images_thumbs">
    <?
    $page = $_GET['page'] ? (int) $_GET['page'] : 1;
   
   $limit= 9;
    $query_count = "SELECT count(*) AS rowcount FROM site_images";     
    $result_count = mysql_query($query_count, $connection);     
    $row_count = mysql_fetch_array($result_count, MYSQL_ASSOC);
    $totalrows = $row_count['rowcount'];

    $query_count = "SELECT count(*) AS rowcount FROM site_images WHERE orientation = 'l'";     
    $result_count = mysql_query($query_count, $connection);     
    $row_count = mysql_fetch_array($result_count, MYSQL_ASSOC);
    $landscaperows = $row_count['rowcount'];

    // work out if we have a landscape page or a portrait page
    if ($page * $limit > $landscaperows) {
        $type = 'p';
        $dbpage = $page - (ceil($landscaperows / $limit));
    }
    else {
        $type = 'l';
        $dbpage = $page;
    }
        
    $limitvalue = $dbpage * $limit - ($limit); 
    $query_thumbs  = "SELECT * FROM site_images WHERE orientation = '".$type."' ORDER BY id LIMIT $limitvalue, $limit";         
    $result_thumbs = mysql_query($query_thumbs) or die("Error: " . mysql_error());
   
   
echo"<table width='336' border='0' cellspacing='5' cellpadding='0'>
     <tr>
     ";
   $img_number=0;
   while ($row_thumbs = mysql_fetch_array($result_thumbs)) {
         extract($row_thumbs);
         if($img_number == 3 OR $img_number ==6){
            echo "
            </tr>
            <tr>
            <td width='112' valign='middle' align='center'>   
            <A href='$lrg' rel='lightbox[live]' title=''><img id='$id' src='$thumb' height='75px' border='1'></a>
            </td>";
         }else{
            echo "
            <td width='112' valign='middle' align='center'>   
            <A href='$lrg' rel='lightbox[live]' title=''><img id='$id' src='$thumb' height='75px' border='1'></a>
            </td>";
         }
         $img_number++;
      }
   
   $numofpages = ceil($totalrows / $limit); 

echo"</td>
    </tr>
    <tr>
       <td colspan='3' class='photos_thumbs' align='center'>";
   if($page > 1){ 
      $pageprev = $page - 1;
      echo"
         <a href=\"$PHP_SELF?go=media&page=".$pageprev."\"><< Previous Page</a>
         "; 
   }
   
   if($page < $numofpages AND $page > 1){
        echo" :: "; 
    }
   
   if($page < $numofpages){
        $pagenext = $page + 1;
        echo"
         <a href=\"$PHP_SELF?go=media&page=".$pagenext."\">Next Page >></a>
         ";
   }

echo"</td></tr></table>";

?>
    </div>   
</div>

[Woodburn2006]

i get this error:

 

 

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-9, 9' at line 1

 

ive had a look but cant seem to see the problem

[soak]

Try that instead, think I miscalculated.

 

<?php
    // work out if we have a landscape page or a portrait page
    if (($page - 1) * $limit > $landscaperows) {
        $type = 'p';
        $dbpage = $page - (ceil($landscaperows / $limit));
    }
    else {
        $type = 'l';
        $dbpage = $page;
    }
       

[Woodburn2006]

superb, thanks alot, works a treat at the mo, will test it ptoperly tomo when i get time

[Woodburn2006]

ok i have doen some testing, it works fine until i have a full page of landscape images. so if the last page of landscape is full then it will not load any portrait photos. but if there is space for more landscape then it will load portrait images onto next page. and i can assure that there are portrait images on the server

 

check this link for example: http://www.ontherocks.me.uk/index2.php?go=media

 

 

[soak]

Probably:

 

if ($page * $limit > $landscaperows) {

 

should be:

 

if ($page * $limit >= $landscaperows) {

 

If not I'm pretty sure the issue is going to be on that line.

[Woodburn2006]

legendary, cheers mate works a treat