• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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

0
barlet
Asked:
barlet
1 Solution
 
Ray PaseurCommented:
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
0
 
VanHackmanCommented:

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
0
 
soul_of_musixCommented:
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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
barletAuthor Commented:
sorry the code above does not break data into pages..
Any other solution?
0
 
soul_of_musixCommented:
Try this new one, maybe can help you :
<html><head><title>MySQL Test</title></head><body>

<form method = "POST" action = "">
<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'])? $_POST['fname'] : $_GET['fname'] ;

$dataPerPage = 5;
$page  = ($_GET['page'])? $_GET['page'] : 1 ;

$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";
}

$sql = "SELECT * FROM {$table} WHERE Name like '%{$username}%'";
$res = mysql_query($sql);
$num = mysql_num_rows($res); 

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

mysql_free_result($result);

?>
</body></html>

Open in new window

0
 
barletAuthor Commented:
did not work completely...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now