Displaying x number of search results per page

Question

benk1 0 Newbie Poster

16 Years Ago

Hi Guys

I've got a MySQL product database on a website, and I wrote a php script that searches the database and returns the results in an html table with no problems at all.

My problem comes when trying to display x results (I've been trying 5) per page. I've been trawling the net for days adapting various scripts that I've found.

Below is my best effort so far (it displays the correct number of results in the table, but the next page and previous page links aren't quite right).

I have been trying with $_SERVER to link back to the page, without success

Hope you can help!

<html>
<body>

<form name="form" action="search2.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

<?php

$var = @$_GET['q'];
$trimmed = trim($var);

$limit=5;

if(empty($page)){
$page = 1;
}

if ($trimmed=="")
	{
	echo "You didn't enter anything to search. <br /><br />
		Our most popular products are.."; 
		exit;
	}
$con = mysql_connect("localhost","username","password");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("csproducts", $con);

$limitvalue = ($page - 1) * $limit;

$query = "SELECT * FROM productlist WHERE partno LIKE '%$trimmed%' || fullname LIKE '%$trimmed%' || description LIKE '%$trimmed%' || category LIKE '%$trimmed%' ORDER BY partno LIMIT $limitvalue, $limit";

$sql=mysql_query($query);
$totalrows=mysql_num_rows($sql);

$result = mysql_query($query) or die("Couldn't execute query");

if ($totalrows==0) {

echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned no results</p>";

}

if($totalrows>0){

echo "<table border='1' width='100%'>
<tr align='center'>
<th width='20%'>Image</th>
<th width='20%'>Part Number</th>
<th width='20%'>Part Name</th>
<th width='20%'>Description</th>
<th width='20%'>Category</th>
</tr>";

while($row = mysql_fetch_array($result)){

$image=$row['image'];
$partno=$row['partno'];
$fullname=$row['fullname'];
$description=$row['description'];
$category=$row['category'];

echo "<tr align='center'>";
  echo "<td width='20%'> <img src ='$image'> </td>";
  echo "<td width='20%'> $partno </td>"; 
  echo "<td width='20%'> $fullname </td>";
  echo "<td width='20%'> $description </td>";
  echo "<td width='20%'> $category </td>";
  echo "</tr>";

}
}


if($page > 1){
$pageprev = $page-1;
echo("<a href=\"search2.php?page=$pageprev\">PREV</a>&nbsp;");
}

$numofpages = ceil($totalrows / $limit);

for($i = 1; $i <= $numofpages; $i++){
if($page == $i){
echo($i."&nbsp;");
}
else{
echo"<a href=\"search2.php?page=$i\">$i</a>&nbsp;";
}
}
if($page < $numofpages){
$pagenext = ($page + 1);
echo "<a href=\"search2.php?page=$pagenext\">NEXT</a>";

}
?>
</body>
</html>

mysql php

2 Contributors
3 Replies
2K Views
1 Day Discussion Span
Latest Post 16 Years Ago Latest Post by benk1

Dani AI

Generated 11 Months Ago

your paging math is almost there, but two small pitfalls cause the odd PREV/NEXT behavior. First, $totalrows is taken from the limited result, so you are dividing by 5 using only the rows from the current page. Run a separate COUNT(*) without LIMIT to get the real total, then compute pages from that. Second, as hinted, LIMIT takes an offset and a row_count, not an end index. For page N with 5 per page, offset should be (N-1)*5 and row_count should always be 5. Also, keep the search term q in every page link so the next page shows the same filtered results.

Here is a compact, safer pattern using PDO and prepared statements (modern replacement for the old mysql_* functions):

<?php
$q = trim($_GET['q'] ?? '');
$page = max(1, (int)($_GET['page'] ?? 1));
$limit = 5;

if ($q === '') { echo 'Please enter a search term.'; exit; }

$pdo = new PDO('mysql:host=localhost;dbname=csproducts;charset=utf8mb4','user','pass',[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$where = 'WHERE partno LIKE :q OR fullname LIKE :q OR description LIKE :q OR category LIKE :q';

// total rows
$stmt = $pdo->prepare("SELECT COUNT(*) FROM productlist $where");
$stmt->execute([':q' => "%$q%"]);
$total = (int)$stmt->fetchColumn();
$pages = max(1, (int)ceil($total / $limit));
$page = min($page, $pages);
$offset = ($page - 1) * $limit;

// page rows
$sql = "SELECT image, partno, fullname, description, category
        FROM productlist $where ORDER BY partno LIMIT $limit OFFSET $offset";
$stmt = $pdo->prepare($sql);
$stmt->execute([':q' => "%$q%"]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// pagination links (preserve q)
$base = ['q' => $q];
if ($page > 1) echo '<a href="?'.http_build_query($base + ['page'=>$page-1]).'">PREV</a> ';
for ($i=1; $i<=$pages; $i++) echo $i==$page ? "$i " : '<a href="?'.http_build_query($base + ['page'=>$i]).'">'.$i.'</a> ';
if ($page < $pages) echo '<a href="?'.http_build_query($base + ['page'=>$page+1]).'">NEXT</a>';

Notes: prefer <?php over short tags, avoid echoing $_SERVER['PHP_SELF'] directly, and consider a FULLTEXT index if the table grows.

All 3 Replies

Wraithmanilian 1 Junior Poster

16 Years Ago

Try this:

<?php

$var = @$_GET['q'];
$trimmed = trim($var);
$page = $_GET['n'];

$limit=5;

if(empty($page)){
$page = 1;
}

if($page==1) { $newpage = 1; ?>

<html>
<body>

<form name="form" action="search2.php" method="get">
  <input type="text" name="q" />
	<input type="hidden" name="n" value="<? echo $newpage; ?>">
  <input type="submit" name="Submit" value="Search" />
</form>

<?
 } else { $newpage+=1; }


if ($trimmed=="")
	{
	echo "You didn't enter anything to search. <br /><br />
		Our most popular products are.."; 
		exit;
	}
$con = mysql_connect("localhost","username","password");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("csproducts", $con);

$limitvalue = $page-1;
$newlimit = $limitvalue+5;

$query = "SELECT * FROM productlist WHERE partno LIKE '%$trimmed%' || fullname LIKE '%$trimmed%' || description LIKE '%$trimmed%' || category LIKE '%$trimmed%' ORDER BY partno LIMIT $limitvalue, $newlimit";

$sql=mysql_query($query);
$totalrows=mysql_num_rows($sql);

$result = mysql_query($query) or die("Couldn't execute query");

if ($totalrows==0) {

echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned no results</p>";

}

if($totalrows>0){

echo "<table border='1' width='100%'>
<tr align='center'>
<th width='20%'>Image</th>
<th width='20%'>Part Number</th>
<th width='20%'>Part Name</th>
<th width='20%'>Description</th>
<th width='20%'>Category</th>
</tr>";

while($row = mysql_fetch_array($result)){

$image=$row['image'];
$partno=$row['partno'];
$fullname=$row['fullname'];
$description=$row['description'];
$category=$row['category'];

echo "<tr align='center'>";
  echo "<td width='20%'> <img src ='$image'> </td>";
  echo "<td width='20%'> $partno </td>"; 
  echo "<td width='20%'> $fullname </td>";
  echo "<td width='20%'> $description </td>";
  echo "<td width='20%'> $category </td>";
  echo "</tr>";

}
}


if($page > 1){
	$pageprev = $page-1;
	echo("<a href=\"search2.php?page=$pageprev\">PREV</a>&nbsp;");
}

$numofpages = ceil($totalrows / $limit);

for($i = 1; $i <= $numofpages; $i++){
	if($page == $i){
		echo($i."&nbsp;");
	} else{
		echo"<a href=\"search2.php?page=$i\">$i</a>&nbsp;";
	}
}

if($page < $numofpages){
$pagenext = ($page + 1);
echo "<a href=\"search2.php?page=$pagenext\">NEXT</a>";

}
?>
</body>
</html>

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

benk1 0 Newbie Poster · Answer 1 · 2010-01-11T00:13:23+00:00

Thanks very much for getting back to me. Now, initially I get a PHP Parse error: syntax error, unexpected $end in "doc_path" on line 112, but that is easily fixed by putting <?php instead of <? only in those 2 places.

Then, the error PHP Notice: Undefined index: n in "doc_path on" line 5.

I sorted this out by putting in the following code:

if (isset($_GET['n']))
{
 $page = $_GET['n'];        
}

However the links for the next search results don't even appear.

I'm going to try and mess around with the code a little more - hopefully the 'hidden' property of the form will be the answer to my problem...

benk1 0 Newbie Poster · Answer 2 · 2010-01-11T04:51:05+00:00

Hi, solved it with the code tutorial here:

Check it out if you are having similar problems

Displaying x number of search results per page

Dani AI

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers