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

LVL 7
barletAsked:
Who is Participating?
 
soul_of_musixConnect With a Mentor Commented:
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
barletAuthor Commented:
sorry the code above does not break data into pages..
Any other solution?
0
 
barletAuthor Commented:
did not work completely...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.