breaking SQL results into pages

LuckySyringe · Jan 11, 2006

repost from splitting search results into pages.

I just recently stumbled upon this script and decided to implement it into my site. However, it shows the first ten results on the first page (without &page=1 in the url) and when you click next, it skips 10 items and lists from 21-30 and from there on works perfectly.

Code:

//db settings
$records_per_page = 10;

//connect to MySQL
mysql_connect ($mysql_host, $mysql_user, $mysql_pass);
mysql_select_db ($mysql_db);

//find out how many records are in the table
$count_query = "SELECT count(*) from ".$mysql_table;
$rh = mysql_query($count_query);
list ($record_count) = mysql_fetch_array($rh);

//calculate the maximum "page" that can be displayed.
$max_pages = floor($record_count / $records_per_page);

//This logic takes care of reacting to input.
if (isset($_GET['page']))
{
    if ($_GET['page'] > 1)
    {
        if ($_GET['page'] > $max_pages)
        {
            $current_page = $max_pages;
        }
        else
        {
            $current_page = $_GET['page'];
        }
    }
    else
    {
        $limit_start = 0;
        $current_page = 1;
    }
    
    $limit_start = $current_page * $records_per_page;
}
else
{
    $limit_start = 0;
    $current_page = 1;
}


//query the database for the required records
$data_query = "SELECT * FROM ".$mysql_table." ORDER BY `id` DESC LIMIT " . $limit_start . ", " . $records_per_page;
$rh = mysql_query ($data_query);

print '<html><body><table width="100%" border="1">';

//output the required records
while ($word_data = mysql_fetch_array($rh))
{
    print '<tr>';
    print '<td align="center" width="50%">' . $word_data['pkID'] . '</td>';
    print '<td align="center" width="50%">' . $word_data['word'] . '</td>';
    print '</tr>';
}

//this is the logic for the "previous" link display
print '<tr><td width="50%" align="center">';
if ($current_page > 1)
{
    print '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . ($current_page - 1) . '">previous</a>';
}
else
{
    print '&nbsp;';
}
print '</td>';

//this is the logic for the "next" link display
print '<td width="50%" align="center">';
if ($limit_start + $records_per_page < $record_count)
{
    print '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . ($current_page + 1) . '">next</a>';
}
else
{
    print '&nbsp;';
}
print '</td></tr></table><body></html>';

I need the missing results to be displayed, but the items that are supposed to be shown aren't showing... if that makes any sense.

-> LuckySyringe

sleipnir214 · Jan 11, 2006

Where did you stumble upon it? It's nearly identical to the script in my faq434-5244.

I have an updated version of the code posted there, but the basic change is to replace:

Code:

if (isset($_GET['page']))
{
    if ($_GET['page'] > 1)
    {
        if ($_GET['page'] > $max_pages)
        {
            $current_page = $max_pages;
        }
        else
        {
            $current_page = $_GET['page'];
        }
    }
    else
    {
        $limit_start = 0;
        $current_page = 1;
    }
    
    $limit_start = $current_page * $records_per_page;
}
else
{
    $limit_start = 0;
    $current_page = 1;
}

with

Code:

if (isset($_GET['page']))
{
    if ($_GET['page'] > 1)
    {
        if ($_GET['page'] > $max_pages)
        {
            $current_page = $max_pages;
        }
        else
        {
            $current_page = $_GET['page'];
        }
    }
    else
    {
        $current_page = 1;
    }
}
else
{
    $current_page = 1;
}

$limit_start = ($current_page - 1) * $records_per_page;

Want the best answers? Ask the best questions!

TANSTAAFL!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

breaking SQL results into pages

LuckySyringe

Technical User

sleipnir214

Programmer

Similar threads

Part and Inventory Search

Sponsor