How do you only display 8 rows per page? PHP/MYSQL

[Scottmandoo]

In my last topic i quess i was asking to much so no one would help me, so this time ill take it one step at a time, heres my first problem.

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tutorials WHERE tut_program='Photoshop' AND tut_category='Text Effects' ";
$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

?>
<link href="file:///C|/Users/Scott/SlyGraphics/style.css" rel="stylesheet" type="text/css" />

<title>SlyGraffix: Database Test</title><table width="287" border="0" align="center" cellpadding="2" cellspacing="0">
             
<?
$i=0;
while ($i < $num) {
$tut_name=mysql_result($result,$i,"tut_name");
$tut_link=mysql_result($result,$i,"tut_link");
$tut_program=mysql_result($result,$i,"tut_program");
$tut_category=mysql_result($result,$i,"tut_category");
$tut_video=mysql_result($result,$i,"tut_video");
$tut_download=mysql_result($result,$i,"tut_download");
$tut_image=mysql_result($result,$i,"tut_image");
?>

 <tr>
                <td width="140"><a href="<? "$tut_link"; ?>"><img src="images/tutorials/fire_reflection.jpg" alt="d" width="140" height="120" border="0" /></a></td>
                <td width="139" valign="top"><p align="left"><a href="<? "$tut_link"; ?>"><? echo "$tut_name"; ?> </a> </p>
                    <p align="left"> Video: <? echo "$tut_video"; ?><br />
                      Download: <? echo "$tut_download"; ?><br />
                      External Link: Yes <br />
                      <br />
                      &gt;
                      <? echo "$tut_program"; ?></p>
                    </td>
              </tr>
<?
++$i;
} 
echo "</table>";


?>

In this code i need it to only display 8 rows per page and also it so that after more is added to the mysql database it automatically creates a new page to display the new rows on.

[nav33n]

Search for pagination. You can make use of 'limit' to display only 8 records at a time.

[Scottmandoo]

ok thanks i will try that, do you know how to do the page thing, to make it automatically add new pages?

eg. 1st page - display rows 1 - 8, 2nd page - display rows 9 - 16 etc.

or will this pagination thing do it all?

[JRSofty]

The trick is to make a count of the total records you want to eventually display. Divide this number by the number of items to display per page (you might want to make sure that if there is a remainder that you add an extra page).

Then you build your call so that it determines which page is being called and then determines where to start the data and how many items to display.

LIMIT in MySQL allows you to limit the data. It is used like this SELECT * FROM tutorials WHERE tut_program = 'PHOTOSHOP' LIMIT 16, 8 The first number in the LIMIT says where to start looking and the second number says how many to show.

[Scottmandoo]

ok heres my code again i think i did it wrong because nothing displays, i feel like such a noob, lol.

<?
if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if
?>

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tutorials WHERE tut_program='Photoshop' AND tut_category='Text Effects' ";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];
$rows_per_page = 8;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno < 1) {
   $pageno = 1;
} elseif ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query = "SELECT * FROM table $limit";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
... process contents of $result ...

if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} // if

echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

?>
<link href="file:///C|/Users/Scott/SlyGraphics/style.css" rel="stylesheet" type="text/css" />

<title>SlyGraffix: Database Test</title><table width="287" border="0" align="center" cellpadding="2" cellspacing="0">
             
<?
$i=0;
while ($i < $num) {
$tut_name=mysql_result($result,$i,"tut_name");
$tut_link=mysql_result($result,$i,"tut_link");
$tut_program=mysql_result($result,$i,"tut_program");
$tut_category=mysql_result($result,$i,"tut_category");
$tut_video=mysql_result($result,$i,"tut_video");
$tut_download=mysql_result($result,$i,"tut_download");
$tut_image=mysql_result($result,$i,"tut_image");
?>

 <tr>
                <td width="140"><a href="<? "$tut_link"; ?>"><img src="images/tutorials/fire_reflection.jpg" alt="d" width="140" height="120" border="0" /></a></td>
                <td width="139" valign="top"><p align="left"><a href="<? "$tut_link"; ?>"><? echo "$tut_name"; ?> </a> </p>
                    <p align="left"> Video: <? echo "$tut_video"; ?><br />
                      Download: <? echo "$tut_download"; ?><br />
                      External Link: Yes <br />
                      <br />
                      &gt;
                      <? echo "$tut_program"; ?></p>
                    </td>
              </tr>
<?
++$i;
} 
echo "</table>";


?>

[nav33n]

Print out the query and test it in phpmyadmin/mysql. Check if the query returns any value.

[Scottmandoo]

nope nothing

also changed $query = "SELECT * FROM table $limit"; to $query = "SELECT * FROM tutorials $limit";

[nav33n]

No. Print the query.Execute it.

$query = "select * from tutorials $limit";
echo $query;

[Scottmandoo]

I dont mean to sound so dumb but i got no idea what you mean, sorry for being like this but i've only learnt how to use php/mysql in the last couple of days and am still trying to get the grips of it.

So could you please explain it in 'dumb' terms.

btw i really appreciate this.

[nav33n]

Ok! In simple terms, You have this query. $query = "SELECT * FROM table $limit"; Just add another line, echo $query after it. It will print something on the screen. Copy it and paste it in phpmyadmin or mysql console. If your query is correct, you will get an output. :)

[Scottmandoo]

so what your pretty much saying is where it said.

$query = "SELECT * FROM tutorials $limit";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
... process contents of $result ...

make it say

$query = "SELECT * FROM tutorials $limit";
echo $query
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
... process contents of $result ...

if this is right i feel so stupid, also again it displays nothing, btw the code is on a php page

[ryan_vietnow]

here,follow this tutorial on how to do it:

http://www.php-mysql-tutorial.com/php-mysql-paging.php

[Scottmandoo]

Alright i finally got a code that works, though there are still a few problems with it.

- it makes the rows align next to each other not underneath, if you could make it go 2 next to each other and then the next 2 under them so 4 rows of 2 in total per page would be perfect.
- How do you change code, everytime i change the table or font etc. it make nothing appear on the page at all.

Anyways heres the code:

<?php
include("dbinfo.inc.php");
mysql_connect($host,$user,$pass) or die("ERROR:".mysql_error());
mysql_select_db($db) or die("ERROR DB:".mysql_error()); 
$max = 8; //amount of articles per page. change to what to want
$p = $_GET['p'];
if(empty($p))
{
$p = 1;
}
$limits = ($p - 1) * $max; 
//view the tutorials article!
if(isset($_GET['act']) && $_GET['act'] == "view")
{
$tut_id = $_GET['tut_id'];
$sql = mysql_query("SELECT * FROM tutorials WHERE tut_id = '$id'");
while($r = mysql_fetch_array($sql))
{
$tut_name = $r['tut_name'];
$story = $r['story'];
$tut_link = $r['tut_link'];
}
}else{
//view all the tutorials articles in rows
$sql = mysql_query("SELECT * FROM tutorials LIMIT ".$limits.",$max") or die(mysql_error());
//the total rows in the table
$totalres = mysql_result(mysql_query("SELECT COUNT(tut_id) AS tot FROM tutorials"),0);	
//the total number of pages (calculated result), math stuff...
$totalpages = ceil($totalres / $max); 
//the table
echo "<center><table><tr><td></td><td></td></tr><tr>";
while($r = mysql_fetch_array($sql))
{
$tut_id = $r['tut_id'];
$tut_name = $r['tut_name'];
$tut_link = $r['tut_link'];
echo "<td><a href='$tut_link'>$tut_name</a></td><td></td><br />";
}
//close up the table
echo "</tr></table></center>";
for($i = 1; $i <= $totalpages; $i++){ 
//this is the pagination link
echo "<center>Page: <a href='tutorials.php?p=$i'>$i</a> | </center>";
}
}

?>

Check out my page here: Click Here

[somedude3488]

do you need a pagination script? i have a few i developed 6 months ago you can use.

[Scottmandoo]

Thanks to kkeith29 i now have a perfect working pagination script.

[prethum]

hi i am working on pagination too i have a problem i am sure u can fix it:
i have to list box city and type,
city =list of cities and type=hotel,bars ...
when the user select from the lsit box and press search i get the out put from the mysql
my problem is i see the reult but when i click on page 2,3,4.... or next page to see rest of the result i dont see any thing the serach result returs nothing

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Registration Form</title>
<style type="text/css">
<!--
body,td,th {
font-size: 16px;
}
.style1 {font-size: 16px}
body {
background-color: #000000;
}
-->
</style><head>
<link rel="stylesheet" type="text/css" href= "Css/gbt.css" />
<link rel="stylesheet" type="text/css" href= "Css/layout.css" />
<link rel="stylesheet" type="text/css" href= "Css/sugg.css"/>
<script language="JavaScript" type="text/javascript" src="JS/ddtabmenu.js"></script>
<script type="text/javascript" src="JS/jquery-1.2.1.pack.js"></script>
<script type="text/javascript">
function lookup(inputString) {
if(inputString.length == 0) {
// Hide the suggestion box.
$('#suggestions').hide();
} else {
$.post("autocomplete.php", {queryString: ""+inputString+""}, function(data){
if(data.length >0) {
$('#suggestions').show();
$('#autoSuggestionsList').html(data);
}
});
}
} // lookup


function fill(thisValue) {
$('#inputString').val(thisValue);
setTimeout("$('#suggestions').hide();", 200);
}
</script>
</head>
<body link="blue" alink="blue" vlink="blue" >
<div id="container" style="position:relative">
<div id="container">
<div id="header">
<table width="946" height="83" border="0">
<tr>
<td width="202" bgcolor="#FFFFFF"><?PHP include "./Ads.php"?></td>
<td width="466" bordercolor="#FFFFFF" bgcolor="#FFFFFF">&nbsp;</td>
<td width="213" bordercolor="#FFFFFF" bgcolor="#FFFFFF">&nbsp;</td>
</tr>
</table>
<table width="950" border="0">
<tr>
<td width="200" bordercolor="#FFFFFF" bgcolor="#FFFFFF"><img src="img/hotel.jpg" width="200" height="121" /></td>
<td width="200" bordercolor="#FFFFFF" bgcolor="#FFFFFF"><img src="img/restaurant2.jpg" width="200" height="121" /></td>
<td width="200" bordercolor="#FFFFFF" bgcolor="#FFFFFF"><img src="img/vandrarhem.jpg" width="200" height="121" /></td>
<td width="200" bordercolor="#FFFFFF" bgcolor="#FFFFFF"><img src="img/spa.jpg" width="200" height="121" /></td>
</tr>
<tr>
<td colspan="4" bordercolor="#FFFFFF" bgcolor="#FFFFFF"><div align="center">
<p><img src="img/log.jpg" width="482" height="121" /></p>
</div></td>
</tr>
<tr>
<td colspan="4" bordercolor="#FFFFFF" bgcolor="#FFFFFF"><div align="right"><a href="#"><img src="img/Sweden.png"/></a>  <a href="chg.php"><img src="img/United Kingdom(Great Britain).png" /></a></div></td>
</tr>
</table>
<div id="glowingtabs">
<ul>
<li><a href="home.php" title="Home" target="_self" ><span>Home</span></a></li>
<li><a href="register.php" title="Upoad Your Ads" target="_self" ><span>Upload Ads</span></a></li>
<li><a href="login.php" title="Login" target="_self" ><span>Login</span></a></li>
<li><a href="register.php" title="Register" target="_self" ><span>Register</span></a></li>
<li><a href="register.php" title="Register" target="_self" ><span>Upload Ads</span></a></li>
</ul>
</div>
<div id="leftBar">
</div>
<div id="content" style="height:500px;width:752px" name ="tar">
<form action="searchdb.php" method="post" name="form1" target="_self" id="form1">
<table width="752" border="0">
<tr>
<td width="350">          <label for="companytype">
<div align="right">Company Type</div>
</label>        </td>
<td width="392"><div align="left">
<select name="companytype" id="companytype">
<option>Hotell</option>
<option>Restaurang</option>
<option>Vandrarhen</option>
<option>Pubar</option>
<option>Diskotek</option>
<option>Caf&eacute;er</option>
<option>Spa</option>
<option>Konferens</option>
</select>
</div></td>
</tr>
<tr>
<td>
<label for="kommun">
<div align="right">kommun</div>
</label>        </td>
<td><div align="left">
<input type="text" name="kommun" size="30" id="inputString" onkeyup="lookup(this.value);" onblur="fill();" />
<label for="Search"></label>
<input type="submit" name="Search" id="Search" value="Search" />
</div>
<div class="suggestionsBox" id="suggestions" style="display: none;">
<img src="img/upArrow.png" style="position: relative; top: -12px; left: 30px;" alt="upArrow" />
<div class="suggestionList" id="autoSuggestionsList"></div>
</div>     </td>
</tr>
</table>
</form>
<p>
<?


// connect to the database and query it to show the titles of all the threads,
// and the date when the thread was last posted
$kommun=$_POST;
$companytype=$_POST;
if(!$kommun)
{
echo "<font color =red>No selection was made</font>";
exit;
}


$rowsPerPage =20;


// by default we show first page
$pageNum = 1;


// if $_GET defined, use it as page number
if(isset($_GET))
{
$pageNum = $_GET;
}


// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
$query  = "SELECT companytype,companyname,address,telephone FROM addreg where kommun like '$kommun' AND companytype like '$companytype' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');


// print the random numbers
while($row = mysql_fetch_array($result))
{
echo "Companytype : " .$row . '<br>';
echo "Companyname : " .$row . '<br>';
echo "Address     : " .$row . '<br>';
echo "Telephone   : " .$row . '<br>';
echo "<img src='img/divv.jpg' />"."<br>";
}
echo '<br>';


// how many rows we have in database
$query   = "SELECT COUNT(companyname) AS numrows FROM addreg where kommun like'$kommun' AND companytype like '$companytype'";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row;


// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);


// print the link to access each page
$self = $_SERVER;
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page ";   // no need to create a link to current page
}
else
{
$nav .= " <a href=\"$self?page=$page\">$page</a> ";
}
}


// creating previous and next link
// plus the link to go straight to
// the first and last page


if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";


$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev  = '&nbsp;'; // we're on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link
}


if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";


$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = '&nbsp;'; // we're on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link
}


// print the navigation link
echo $first . $prev . $nav . $next . $last;


// and close the database connection
include '../library/closedb.php';
?>
</p>
</div>
<div id="footer">
footer
</div>
</div>
</body>
</html>