Jump to content

splitting query results


Woodburn2006

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/154650-splitting-query-results/
Share on other sites

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>

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;
    }
       

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

 

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.