control mysql output as in show 10-30 results per page - Web Design & Web Development Forum: HTML, CSS, JavaScript, PHP

Task1 · #1 29 Jul 2009, 11:18 AM

Hi all.... :-)

putting start, back, forward and end.. to control mysql output as in show 10-30 results per page

i have a web page that you can view infomation from mysql i can limit the amount of data to be shown on a page but the next step i am stuck and need your help...

the code that i have, i will supply i need to add some code to the PHP file to do the following,

start, back, forward and end..

My Regards
Task1

Alan · #2 29 Jul 2009, 11:34 AM

Code Please.

http://www.webdevforums.com/announcement.php?a=37

Task1 · #3 29 Jul 2009, 12:05 PM

Hi Alan
Code as requested
form_testinfo = table name

PHP Code:


			
<html>
<body>
<?php

$host = "localhost";
$username = "username ";
$password = "password ";
$database = "database ";

mysql_connect($host, $username, $password);
mysql_select_db($database) or die("Cannot connect to the database");

$query="SELECT * FROM form_testinfo LIMIT 0, 10";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="2" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
<th><font face="Arial, Helvetica, sans-serif">Site Info</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {


$f1=mysql_result($result,$i,"Name");
$f2=mysql_result($result,$i,"Email");
$f3=mysql_result($result,$i,"Website");
$f4=mysql_result($result,$i,"Message");
$f5=mysql_result($result,$i,"created_at");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="http://<?php echo $f3; ?>" target="_blank"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
</tr>

<?php
$i++;
}
?>


</body>
</html>

Alan · #4 29 Jul 2009, 01:25 PM

The easiest way to solve this is to add a variable in to keep track of the current page. A GET variable is the common approach.

So index.php?p=1 would be the first page, index.php?p=2 would be the second etc...
I'll just throw the code into what you supplied.

PHP Code:


			
<html>
<body>
<?php

$host = "localhost";
$username = "username ";
$password = "password ";
$database = "database ";

mysql_connect($host, $username, $password);
mysql_select_db($database) or die("Cannot connect to the database");

/**** Begin Alan's Code ****/

// Get the max number of rows in the database (Used for the page links)
$num_rows = mysql_result(mysql_query("SELECT COUNT(*) From form_testinfo"),0);

// Check the page number and update the first index ($findex) and last index ($lindex) accordingly
$p = (isset($_GET['p']) && $_GET['p'] > 0) ? $_GET['p'] : 1;

$row_limit = 10; // Number of rows to return per page
$findex = ($p > 1) ? ($p*$row_limit)-$row_limit : 0;
$lindex = ceil($num_rows/$row_limit);

/**** End Alan's Code ****/

$query="SELECT * FROM form_testinfo LIMIT $findex, $row_limit";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="2" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
<th><font face="Arial, Helvetica, sans-serif">Site Info</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {


$f1=mysql_result($result,$i,"Name");
$f2=mysql_result($result,$i,"Email");
$f3=mysql_result($result,$i,"Website");
$f4=mysql_result($result,$i,"Message");
$f5=mysql_result($result,$i,"created_at");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="http://<?php echo $f3; ?>" target="_blank"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
</tr>

<?php
$i++;
}

/**** Begin Alan's Code ****/

// First link (Will always be p=1)
$flink = '<a href="index.php?p=1">&lt;&lt; First</a>';
$llink = ($p < $lindex) ? '<a href="index.php?p='.$lindex.'">Last &gt;&gt;</a>' : ''; 

// Next and Previous page links
$plink = ($p > 1) ? '<a href="index.php?p='.($p-1).'">&lt; Previous</a>' : '';
$nlink = ($p+1 <= $lindex) ? '<a href="index.php?p='.($p+1).'">Next &gt;</a>' : ''; 

/**** End Alan's Code ****/
?>

<!-- The navigation links -->
<div><?php echo $flink . $plink . $nlink . $llink; ?></div>

</body>
</html>

I haven't actually tested this, so there could be syntax errors somewhere. Sorry if there is. If you have any questions, feel free to ask.

Task1 · #5 29 Jul 2009, 02:04 PM

Thanks Alan

it did bring up a syntax error

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING
it refers to this line
$plink = ($p > 1) ? '<a href="index.php?p='.$p-1.'">< Previous</a>' : '';

if we get it going i will let you see what the final output is for and its cool

Alan · #6 29 Jul 2009, 02:27 PM

Ok sorry about that. I have tested it this time and it works perfectly on my end.

(see the updated code in my previous post)

If it's still not working for you, post up the database table schema and ill have a look at it. (Put some dummy data in the table.)

Task1 · #7 29 Jul 2009, 03:19 PM

OMG it works Alan

here is a link of what it does (pic)

i have an input form that goes to the db,
i had the feed back from the MYsql working ok,

but with your help i now can have a few showing at a time and be able to go back and forth MEGA

http://www.onekings.net/thanks/thanks.jpg

My Very

best regards
Louis Eckersall ( Task1 )

Alan · #8 29 Jul 2009, 03:27 PM

Your very welcome.