asked on

PHP and MySQL data paging

Hello there,

I have some data on MySQL database. I am showing them on my screen without any problem..
I can also search for any data I want from my search box.. Till here everything is OK.

what I need is for example to show ONLY 5 data per page! and when I click Next to show the next 5 data and so on same with previous.

Please tell me what should I write on my code and where...

Thanks...

This is my php code:

<html><head><title>MySQL Test</title></head><body>

<form method = "POST" action = "http://localhost/barlet/index.php">
<input type="text" name="fname"/><br />
<input type="submit" value="Query" />
</form>


<?php
$db_host = 'localhost';
$db_user = 'root';
$db_pwd = 'aa';

$database = 'dbtest';
$table = 'Personal';

$username;
$username = $_POST['fname'];
echo "You searched for: <b><u>'" . ($username) . "'</u></b>";


if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table} WHERE Name like '%{$username}%'");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1 align=center>Table: {$table}</h1>";
echo "<table border='0' align=center width='300'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td align=Left width='100'><b><u>{$field->name}</u></b></td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td align=Left>$cell</td>";

    echo "</tr>\n";
}


for ($i = 1; $i <= $page_count; ++$i) { 
    echo '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . $i . '">' 
        . $i . '</a> '; 
} 

mysql_free_result($result);

?>
</body></html>

Open in new window

Ray Paseur

The canonical article on "paging" is available at SitePoint, with a good explanation and clear examples. Available online here: http://articles.sitepoint.com/article/perfect-php-pagination

Best regards, ~Ray

VanHackman

The easy way: Use a Jquery Pagination Plugin.

With most Jquery pagination plugins you can paginate data just adding a specific class to the data.

Just Look:

http://projects.allmarkedup.com/jquery_quick_paginate/
http://tablesorter.com/docs/
http://plugins.jquery.com/project/pagination

soul_of_musix

Try this code :

<html><head><title>MySQL Test</title></head><body>

<form method = "POST" action = "http://localhost/barlet/index.php">
<input type="text" name="fname"/><br />
<input type="submit" value="Query" />
</form>


<?php
$db_host = 'localhost';
$db_user = 'root';
$db_pwd = 'aa';

$database = 'dbtest';
$table = 'Personal';

$username;
$username = $_POST['fname'];

$dataPerPage = 5;
$page  = $_GET['page'];

$start = $page * $dataPerPage;

echo "You searched for: <b><u>'" . ($username) . "'</u></b>";


if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table} WHERE Name like '%{$username}%'
LIMIT $start,$dataPerPage
");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1 align=center>Table: {$table}</h1>";
echo "<table border='0' align=center width='300'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td align=Left width='100'><b><u>{$field->name}</u></b></td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td align=Left>$cell</td>";

    echo "</tr>\n";
}


for ($i = 1; $i <= $page_count; ++$i) { 
    echo '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . $i . '">' 
        . $i . '</a> '; 
} 

mysql_free_result($result);

?>
</body></html>

Open in new window

barlet

ASKER

sorry the code above does not break data into pages..
Any other solution?

ASKER CERTIFIED SOLUTION

soul_of_musix

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

barlet

ASKER

did not work completely...