I've managed to set up a loop to retrieve results from a table and display them in a list...

But now how can I limit it so that it only displays 10/per page and then it says..

Showing results 1-10 of 100
Page 1/10

..and includes links to page 1,2,3 etc.

An example of the code I've already got...

PHP Code:

$query="SELECT * FROM test2 WHERE name LIKE 'J%'";
$result=mysql_query($query);

$num=mysql_numrows($result);

echo "<table>";
$i=0;
while ($i < $num) {

$first=mysql_result($result,$i,"name");
$last=mysql_result($result,$i,"age");

echo "    <tr>
        <td width='478'><p>Name: $first</td>
        <td width='478'><p>Age: $last</td>
    </tr>";

$i++;
}
echo "</table>"; 


Anyone be able to help?

Thanks

-James

Try something like this. This will show the

Prev 1 2 3 Next

links and below those, the 'Displaying results 1-10 of 100'. It's untested, and probably has errors Just something you can work off of.

PHP Code:

<?php

$script   = 'myscript.php';
$per_page = 10;

////////////////////

$totalrows = mysql_query("SELECT COUNT(*) FROM test2 WHERE name LIKE 'J%'");
$totalrows = mysql_fetch_row($totalrows);
$totalrows = $totalrows[0];

if($totalrows < 1)
    die('There is no data to display.');

////////////////////

if(isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] > 0)
    $page = $_GET['page'];
else
    $page = 1;

////////////////////

$limit_start = ($page * $per_page) - $per_page;
if($limit_start > $totalrows)
    die('Sorry, this page does not exist.');

$result = mysql_query("SELECT * FROM test2 WHERE name LIKE 'J%'");

////////////////////

echo '<table>'; 
while($row = mysql_fetch_array($result))
{
    echo '    <tr>';
    echo '        <td width="478"><p>Name: ' . $row['first'] . '</td>';
    echo '        <td width="478"><p>Age: '  . $row['last']  . '</td>';
    echo '    </tr>'; 
}
echo '</table>';

////////////////////

echo '<br />';
if($per_page < $totalrows)
{
    if($page != 1)
        echo '<a href="' . $script . '?page=' . $page - 1 . '">Prev</a> ';

    $totalpages = $totalrows / $per_page;
    for($i = 1; $i <= $totalpages; $i++)
    {
        if($i != $page)
            echo '<a href="' . $script . '?page=' . $i . '">'. $i . '</a> ';
        else
            echo '<strong>' . $i . '</strong> ';
    }

    if(($totalrows - ($per_page * $page)) > 0)
        echo '<a href="' . $script . '?page=' . $page + 1 . '">Next</a> ';
}

////////////////////

echo '<br />';
echo 'Displaying results ' . $limit_start . '-' . $limit_start + $per_page . ' of ' . $totalrows;

I tied this.. But whatever i set $per_page as thats how many pages it displays and it displays all the results on everypage..

Im not going to bother with the Showing results 1-10 of 100 now...

So how would I do it so that it just limits the amount of results per page to 10 and then displays at the bottom...
1 2 3 4 5 Next
So when you goto the next page it displays the next 10 results???

Thanks

-James

What I'd do is add 2 new tables too the mysql. Table max pages and linked_page. Instead of it just selecting stuff out of the table add a AND linked_page='$current_page' have it keep the variable current page info in the url.

PHP Code:

$counter=1;
$current_page=$_GET['current_page']; //Gets the page from the url

if($current_page == ""){ //if it doesn't find the info in url sets it too page 1
$current_page=1;
}

$get_max_pages=mysql_query("select * from test2"); //selects the table too get the max pages
$got_max_pages=mysql_fetch_array($got_max_pages); // gets it and puts it into array
$query="SELECT * FROM test2 WHERE name LIKE 'J%' AND linked_page='$current_page'"; // Selects the db and gets the info only set for the current page

$result=mysql_query($query); //your work
$count_them=mysql_num_rows($result); // count them and exports them 
$num=mysql_numrows($result); 

echo "<table>"; 
$i=0; 
while ($i < $num) { 

$first=mysql_result($result,$i,"name"); 
$last=mysql_result($result,$i,"age"); 

echo "    <tr> 
        <td width='478'><p>Name: $first</td> 
        <td width='478'><p>Age: $last</td> 
    </tr>"; 

$i++; 
} //your work

while ($counter == $got_max_pages[max_pages]) { //loops until the counter is equaled too the max pages

echo"<tr><td>Showing $count_them results</td></tr>";
echo"<tr><td>Page</td></tr>";

echo"<tr><td><a href="CURRENTPAGE.php?current_page=$counter">$counter</a></td></tr>"; //Will export the page number
$counter++; //increases counter so loop will end up stopping
}

echo "</table>"; 

//make sure too edit CURRENTPAGE too your page 


Thats a bit more work. But its well worth it and its very easy on the server its just asking for 2 more tables .
Hope this works as I didn't debug it or even look over it .

In need of more help just Pm me I'v got too much free time on my hands .

That would work..But I would be setting which results to be displayed on each page..Where what I want to be able to do is have the results selected by changing different variables..

E.g.

Selecting rows where age=15
Selecting rows where location=England

So it just gets all the results..say there is 100 and splits them up so 10 are displayed on each page

-James

PHP Code:

ounter=1; 
$current_page=$_GET['current_page']; //Gets the page from the url 

if($current_page == ""){ //if it doesn't find the info in url sets it too page 1 
$current_page=1; 
} 
$numbe_end=$current_page*10;
$number_start=$number-10;



$get_max_pages=mysql_query("select * from test2"); //selects the table too get the max pages 
$got_max_pages=mysql_fetch_array($got_max_pages); // gets it and puts it into array 
$query="SELECT * FROM `admins` WHERE 1 AND `name` LIKE '%j' LIMIT $number_start , $number_end '"; // Selects the db and gets the info only set for the current page 

$result=mysql_query($query); //your work 

$num=mysql_numrows($result); 

echo "<table>"; 
$i=0; 
while ($i < $num) { 

$first=mysql_result($result,$i,"name"); 
$last=mysql_result($result,$i,"age"); 

echo "    <tr> 
        <td width='478'><p>Name: $first</td> 
        <td width='478'><p>Age: $last</td> 
    </tr>"; 

$i++; 
} //your work 
$count=mysql_query("SELECT * FROM `admins` WHERE `name` LIKE '%j");
$counted=mysql_num_rows($count);
$pages=$counted/10;
$round=round($pages);   
echo"<tr><td>Showing $number_start too $number_end</td></tr>"; 
echo"<tr><td>Page</td></tr><tr>"; 

while ($counter != $round) { //loops until the counter is equaled too the max pages 


echo"<td><a href="CURRENTPAGE.php?current_page=$counter">$counter</a></td>"; //Will export the page number 
$counter++; //increases counter so loop will end up stopping 
} 

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

//make sure too edit CURRENTPAGE too your page 


Now that will work and gets everything u wanted