Page 1 of 1

limit the amount of results on a page

Posted: Mon Jul 17, 2006 2:57 am
by katd
Hi I hope someone can help, I only want to display three results per page or the option to change the amount, and it was working until I started messing round with the display of the artists details and craft titles. I think it's because I've got two new select statments where I'm calling my data from so it's ignoring the records code? Not sure how to rectify the problem
If anyone can help it would be greatly appreciated. Here's the code:

Code: Select all

<?php

// Set the page title and include the HTML header.
$page_title = 'Staffordshire Open Studios - Artists Listings';
include_once ('includes/header_directory.html');

require_once ('../mysql_connect.php'); // Connect to the database.

// Number of records to show per page:
$display = 3;

// Determine how many pages there are.
if (isset($_GET['np'])) { // Already been determined.
        $num_pages = $_GET['np'];
        } else { // Need to determine.
        $query = "SELECT * FROM artists ORDER by _craftId ASC"; // Standard query.
        $query_result = mysql_query ($query);
       
        $num_records = mysql_num_rows ($query_result);
       
        if ($num_records > $display) { // More than 1 page.
                $num_pages = ceil ($num_records/$display);
                } else {
                        $num_pages = 1;
                }
        }
       
        // Determine where in the database to start returning results.
        if (isset($_GET['s'])) { // Already been determined.
                $start = $_GET['s'];
        } else {
                $start = 0;
        }
       
        // Make the query.
        $query = "SELECT * FROM artists ORDER by _craftId ASC LIMIT $start, $display";
        $result = mysql_query ($query); // Run the query.
        $num = mysql_num_rows ($result); // How many artists are there?
       
        if ($num > 0) { // If it ran OK, display the records.
       
                echo "<h1>ARTISTS LISTINGS</h1><span class=\"small\">Details of other artists operating in the County of Staffordshire.<br><br></span>";
               
                                        // Make the links to other pages, if necessary.
                if ($num_pages > 1) {
                       
                        echo '<table width="100%" align="right"><tr><td align="right">';
                        // Determine what page the script is on.
                        $current_page = ($start / $display) + 1;
                       
                        echo'<table align="right"><tr>';
                        // If it's not the first page, make a Previous button.
                        if ($current_page !=1) {
                                echo '<td align="right"> <a href="artists_listings.php?s=' . ($start - $display) . '&np=' . $num_pages . '"><img src="images/arrow_left.jpg" width="28" height="34" border="0"></a> </td>';
                        }
                       
                        // Make all the numbered pages.
                        for ($i = 1; $i <= $num_pages; $i++) {
                                echo '<td align="center">';
                                if ($i != $current_page) {
                                        echo ' <a class="small_link_underline" href="artists_listings.php?s=' . (($display*($i - 1))) . '&np=' . $num_pages . '">'.$i.'</a> ';
                                } else {
                                        echo $i.'';
                                }
                                echo'</td>';
                        }
                       
                        // It it's not the last page, make a next button.
                        if ($current_page != $num_pages) {
                                echo '<td align="right"><a href="artists_listings.php?s=' . ($start + $display) . '&np=' . $num_pages . '"><img src="images/arrow_right.jpg" width="28" height="34" border="0"></a> </td></tr></table>';
                        }
                       
                        echo '</td></tr></table><br /><br>';
                       
                } // End of links section.
               
               
                $craft_query = 'select * from craft order by title';
                $craft_result = mysql_query($craft_query);
                while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                while ($craft = mysql_fetch_array($craft_result)) {
               
                $artists_query = "select * from artists where _craftId = '".$craft['craftId']."' order by name";
                $artists_result = mysql_query($artists_query);
                print'<h1>'.$craft['title'].'</h1>';
                print'<table>';
                if (mysql_num_rows($artists_result)) {
        while ($artists = mysql_fetch_array($artists_result)) {
            echo ''.$artists['name'].'<br>';
        }
    }
                       
                       
                }
               
               

        } else { // If there are no registered artists.
                echo '<strong>There are currently no artists listings.</strong>';
        }
               
        mysql_close(); // Close the database connection.

include_once ('includes/footer.html'); // Require the HTML footer.
?>
Thank you

Posted: Mon Jul 17, 2006 10:50 am
by RobertGonzalez
Typically, with paging, you want to establish which record you are starting the display from and how many records will display in each list. There are million ways to do this (not really, but there are a lot). What I usually do is:

1. Select all the data into an array.
2. Check to see if the user has entered a page (through $_GET). If they did, use that as the start, else 0.
3. Set the page limit of records (either default or user supplied)
4. For loop the array using;

Code: Select all

<?php
for ($i = $start; $i < $display_limit; $i++)
{
    // display this section only
    // all items from $start to $display_limit
    // Where display limit is the total array size
    // or the segment size if smaller than the array size
}
?>

Posted: Tue Jul 18, 2006 5:35 am
by katd
I added the forloop but it's not limiting the display it's still just displaying
all the data from the database. I know it's probably because I've added the forloop to the wrong place or I need to change the structure of how i'm displaying the data, but as I said in a previous post I need to have just one craft title for the artists related to it. I'm just completely stuck on this at the moment and it's hard to move forward without it working.

Code: Select all

for ($i = $start; $i < $display; $i++)
{ 
		$craft_query = 'select * from craft order by title';
		$craft_result = mysql_query($craft_query);
			while ($craft = mysql_fetch_array($craft_result)) {
		
		$artists_query = "select * from artists where _craftId = '".$craft['craftId']."' order by name";
		$artists_result = mysql_query($artists_query);
		print'<h1>'.$craft['title'].'</h1>';
		print'<table>';
			if (mysql_num_rows($artists_result)) {
        		while ($artists = mysql_fetch_array($artists_result)) {
            		echo ''.$artists['name'].'<br>';
       		 	}
    		} 
			}
		}

Posted: Tue Jul 18, 2006 8:34 am
by RobertGonzalez
I am not near any of my code right now, but when I can get to it I can post my paging routine. Of course, you could search these boards from pagination and you should come up with a lot of useful posts and information.

Posted: Tue Jul 18, 2006 8:40 am
by katd
That's the thing the code I've got in my page did limit the results to whatever i specified in display but since I added the where statments so that I could have just the one title for more than one artist it stops affecting the display. It's because I'm not displaying my results using the select statment that's the basis for the calculation of the number of pages.

Posted: Tue Jul 18, 2006 8:54 am
by RobertGonzalez
That is a little different than how I do it. I typicaly don't use where clauses in the query (except for things outside the need for display), but limit matches in the loops. When I can I will see if I can scan your code and see what is happening, if someone dones't beat me to it.

Posted: Wed Jul 19, 2006 10:06 am
by katd
Right I've changed my code so that it limits the artist results to 4 on a page again which works fnie all I need to be able to do now is display one craft title no matter how many artists are associated with it e.g.

Calligraphy

Tom Smith
Joe Bloggs

Textiles

Richard Lewis
Mandy Rymill
David Stubbs

This is the code I've got for displaying the results now

Code: Select all

$craft_query = "select * from artists, craft where artists._craftId = craft.craftId order by craft.title LIMIT $start, $display";
		$craft_result = mysql_query($craft_query);
			print'<h1>'.$craft['title'].'</h1>';
			while ($craft = mysql_fetch_array($craft_result)) {
            		echo ''.$craft['name'].'<br>';

}

Posted: Thu Jul 20, 2006 9:08 am
by katd
Hi

Sorry to be a pain but is there anyone who can help I've tried numerous ways of displaying the data but it always comes out in one way other the other e.g. With the results limited to four a page but with no title or all the titles, or all the results on one page with no limiting on the results. I've messed with the code so much that I'm completely lost with it now so if anyone can help it would be greatly appreciated.

Thanks again

Posted: Thu Jul 20, 2006 9:43 am
by JayBird
So, explain again (in detail) what you want to acheive. Im lost

Posted: Thu Jul 20, 2006 9:49 am
by katd
Right what I am trying to do is display artists and their mediums of work but limit the display. The problem is I only want to display the medium title once although their may be more than one artist associated with it. For example

If i limited the display to 4 artists it would display

Calligraphy

Derek Harley

Crafts

Jenny Ellam
Wings 'n' Things - Tony Wright

Jewellery

Angela Smith

I hope that it explains it better
:)

Oh the original code I had was

Code: Select all

<?php

// Set the page title and include the HTML header.
$page_title = 'Staffordshire Open Studios - Artists Listings';
include_once ('includes/header_directory.html');

require_once ('../mysql_connect.php'); // Connect to the database.

// Number of records to show per page:
$display = 3;

// Determine how many pages there are.
if (isset($_GET['np'])) { // Already been determined.
        $num_pages = $_GET['np'];
        } else { // Need to determine.
        $query = "SELECT * FROM artists ORDER by _craftId ASC"; // Standard query.
        $query_result = mysql_query ($query);
       
        $num_records = mysql_num_rows ($query_result);
       
        if ($num_records > $display) { // More than 1 page.
                $num_pages = ceil ($num_records/$display);
                } else {
                        $num_pages = 1;
                }
        }
       
        // Determine where in the database to start returning results.
        if (isset($_GET['s'])) { // Already been determined.
                $start = $_GET['s'];
        } else {
                $start = 0;
        }
       
        // Make the query.
        $query = "SELECT * FROM artists ORDER by _craftId ASC LIMIT $start, $display";
        $result = mysql_query ($query); // Run the query.
        $num = mysql_num_rows ($result); // How many artists are there?
       
        if ($num > 0) { // If it ran OK, display the records.
       
                echo "<h1>ARTISTS LISTINGS</h1><span class=\"small\">Details of other artists operating in the County of Staffordshire.<br><br></span>";
               
                                        // Make the links to other pages, if necessary.
                if ($num_pages > 1) {
                       
                        echo '<table width="100%" align="right"><tr><td align="right">';
                        // Determine what page the script is on.
                        $current_page = ($start / $display) + 1;
                       
                        echo'<table align="right"><tr>';
                        // If it's not the first page, make a Previous button.
                        if ($current_page !=1) {
                                echo '<td align="right"> <a href="artists_listings.php?s=' . ($start - $display) . '&np=' . $num_pages . '"><img src="images/arrow_left.jpg" width="28" height="34" border="0"></a> </td>';
                        }
                       
                        // Make all the numbered pages.
                        for ($i = 1; $i <= $num_pages; $i++) {
                                echo '<td align="center">';
                                if ($i != $current_page) {
                                        echo ' <a class="small_link_underline" href="artists_listings.php?s=' . (($display*($i - 1))) . '&np=' . $num_pages . '">'.$i.'</a> ';
                                } else {
                                        echo $i.'';
                                }
                                echo'</td>';
                        }
                       
                        // It it's not the last page, make a next button.
                        if ($current_page != $num_pages) {
                                echo '<td align="right"><a href="artists_listings.php?s=' . ($start + $display) . '&np=' . $num_pages . '"><img src="images/arrow_right.jpg" width="28" height="34" border="0"></a> </td></tr></table>';
                        }
                       
                        echo '</td></tr></table><br /><br>';
                       
                } // End of links section.
               
               
                $craft_query = 'select * from craft order by title';
                $craft_result = mysql_query($craft_query);
                while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                while ($craft = mysql_fetch_array($craft_result)) {
               
                $artists_query = "select * from artists where _craftId = '".$craft['craftId']."' order by name";
                $artists_result = mysql_query($artists_query);
                print'<h1>'.$craft['title'].'</h1>';
                print'<table>';
                if (mysql_num_rows($artists_result)) {
        while ($artists = mysql_fetch_array($artists_result)) {
            echo ''.$artists['name'].'<br>';
        }
    }
                       
                       
                }
               
               

        } else { // If there are no registered artists.
                echo '<strong>There are currently no artists listings.</strong>';
        }
               
        mysql_close(); // Close the database connection.

include_once ('includes/footer.html'); // Require the HTML footer.
?>