One Query, different pages

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

One Query, different pages

Post by pookie62 »

Hi all,

I've got a query wich results in about 100 records.
I would like to present parts of results on a different (new) page based on values.
I want it based on the Level value, e.g When Level B is returned, a new page is created
Probably create an array, but don't know how to start.
This the code I have:

Code: Select all

<?php
    $limit          = 15;               
    $query_count    = "SELECT * FROM klassement";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_num_rows($result_count);
    $totalpages = ceil($totalrows/$limit);
    
    $page = (!empty($_REQUEST['page'])) ? $_REQUEST['page'] + 1 : 1; 
    $s = $page;
    
    $limitvalue = $page * $limit - ($limit);
    $query  = "SELECT `klasse`.`Klasse` AS `Klasse`,`klassement`.`LevelId` AS `Level`,`deelnemer`.`Naam` AS `naam`,`deelnemer`.`Vereniging` AS `Vereniging`, `deelnemer`.`URL` AS `URL`, TRUNCATE (`klassement`.`Totaal percentage` * 100, 2) AS `Totaal%`, TRUNCATE (`klassement`.`Gemiddelde percentage` * 100,2) AS `Gem%` FROM `klassement` INNER JOIN `deelnemer` ON (`klassement`.`DeelnemerId` = `deelnemer`.`Id`) INNER JOIN `deelnemerklasselevel` ON (`deelnemer`.`Id` = `deelnemerklasselevel`.`DeelnemerId`) INNER JOIN `klasse` ON (`klassement`.`KlassId` = `klasse`.`Id`)GROUP BY `Klasse`,`Naam`,`Level` ORDER BY `Klasse`,`Level`,`Totaal Percentage` DESC LIMIT $limitvalue, $limit";        
    $result = mysql_query($query) or die("Error: " . mysql_error());
    $numrows = mysql_num_rows($result);
    if($numrows == 0){
        echo("Nothing to Display!");
    } 
   

// Begin your table outside of the array
echo "<table width="65%" border="1" cellpadding="2" cellspacing="2">
    <tr>
    	<td><b>Nr</b></td>
        <td><b>Klasse</b></td>
        <td><b>Level</b></td>
        <td><b>Naam</b></td>
        <td><b>Vereniging</b></td>
        <td><b>Totaal%</b></td>
        <td><b>Gem%</b></td>
                
    </tr>"; 
    
// Define your colors for the alternating rows

$color1 = "#ADD8E6";
$color2 = "#E0FFFF";
$row_count = 1; 
    




/* Printing results in HTML */


while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$row_count = $row_count;
	$Klasse = $line["Klasse"];
    $Level = $line["Level"];
    $Naam = $line["naam"];
    $Vereniging = $line["Vereniging"]; 
    $Totaal = $line["Totaal%"];
    $Gem = $line["Gem%"];
	$email = $line["URL"];

        
    
    /* Now we do this small line which is basically going to tell
    PHP to alternate the colors between the two colors we defined above. */

    $row_color = ($row_count % 2) ? $color1 : $color2; 
    
    
     // Echo your table row and table data that you want to be looped over and over here. 
  
echo "<tr>
    <td width="5" bgcolor="$row_color">$row_count</td>
    <td width="10" bgcolor="$row_color">$Klasse</td>
    <td width="10" bgcolor="$row_color">$Level</td>
    <td width="200" bgcolor="$row_color">$Naam</td>
    <td width="160" bgcolor="$row_color" nowrap><a href="$email">$Vereniging</td>
    <td width="80" bgcolor="$row_color" nowrap>$Totaal%</td>
    <td width="100" bgcolor="$row_color">$Gem</td>
    </tr>"; 
    
     // Add 1 to the row count

    $row_count++;
	}

//break before paging
echo "<br />";

// next we need to do the links to other results

if ($s>1) { // bypass PREV link if s is 0
$prevs=($page-2);
print "&nbsp;<a href="$PHP_SELF?page=$prevs&q=$var"><<
Vorige 15</a>&nbsp&nbsp;";
}

// calculate number of pages needing links

$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division


// check to see if last page
if (((($s+$limit)-1)>=$pages) && $pages!=0) {

// not last page so give NEXT link

$news=$page;

echo "&nbsp;<a href="$PHP_SELF?page=$news&q=$var">Volgende 15 >></a>";
}

$a = $s * $limit;
if ($a > $totalrows) { $a = $totalrows; }
$b = $limit * $page - $limit + 1;
echo "<p>Records $b tot $a of $totalrows</p>";
    
mysql_free_result($result);
?>
Thanks for replying..
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: One Query, different pages

Post by timvw »

pookie62 wrote: I've got a query wich results in about 100 records.
I would like to present parts of results on a different (new) page based on values.
Search these forums. Pagination has been explained many times before...
pookie62 wrote: I want it based on the Level value, e.g When Level B is returned, a new page is created
Say you have pages 1 to 26 begin:


1 = a
...
26 = z

so for a page you would have to lookup the character/letter that belongs to the page. And then all you need to do is

Code: Select all

SELECT * 
FROM foo
ORDER BY column
WHERE SUBSTRING(column, 1) = '$char'
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Hi timvw,
Pagination is working fine, that isn't the problem.
About your suggestion, is this new Query not going to give problems with the original query ? I thought I can only define one Query ?? Or am I mistaken here..?

I just want when the output of Level is becoming 'B' or 'C' a new fresh page is created with the same table layout as this one longlisting page (although seperated through pagination)
Hope this makes sense... :-)
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

:(
Nobody any suggestions ??
I thought this wasn't a major problem for experienced guys like I see in this Forum..
Please look at the code and push me in the right direction.
Thanks
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Just make it so you know which page you're on. IE when the first page is displayed, pass it 'A' and do WHERE LEVEL = $level. Then display all the A's on that page and on the next page pass it 'B' and so on...
Post Reply