1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 8:30 am May 2, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Need help setting up pagination code for a search engine

setting pagination links in a full text search

         

mrelliott

5:55 pm on Mar 14, 2007 (gmt 0)

10+ Year Member



I'm trying to create a small search engine for a database that lists the songs for the band Queen. I've been able to set up proper pagination for each individual album, but I can't figure out how to place it into a code that uses full text search and boolean full text search to look up results. I strongly believe that the problem is in my code for counting the records which on all my other pages selects exactly the columns I need based on the album selected. That code reads as such: 

// Count the number of records
 $query = "SELECT COUNT(*) FROM master_list WHERE album ='Queen' ORDER BY track"; 
 $result = @mysql_query ($query);
 $row = mysql_fetch_array ($result, MYSQL_NUM);
 $num_records = $row[0];

I need the code on my search page to count only the results that match what the user enters such as 'mercury' would bring up all the songs that have the word 'mercury' in their writer section.

The search engine code does work without pagination, but I'd rather have ten results per page rather then 74 records on one page which is what 'mercury' brings up.

Here's my search engine code. Sorry it's long but I didn't want to leave anything out in case it isn't the count function that's causing the problem. 


<?php 
// Full-Text Search Example 
// Connect to the database. 
require_once ('./includes/mysql_connect.php'); //connect to database
 
//Records to display per page.
$display = 10;
//Check if the number of required pages has been determined. 
if (isset($_GET['np'])) { //Already been determined.
$num_pages = $_GET['np']; 
} else { //Need to determine.
 // Count the number of records
/*This code has produced the same error as the rest.*/
$query = "SELECT song_id, title, album, writer, COUNT(*) AS number FROM master_list WHERE MATCH (title, album, writer) 
AGAINST ('$searchstring') ORDER BY number";
/*The code below has produced this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource 
in /home/www/example.com/searchresults2.php on line 81. Line 81 being: 
$row = mysql_fetch_array ($result, MYSQL_NUM);*/
 $query = "SELECT song_id, title, album, writer COUNT(*) FROM master_list WHERE title, album, writer LIKE ('$searchstring') ORDER BY song_id";
/*The code directly below this generated this error. Warning: mysql_fetch_array(): supplied argument is not a valid 
MySQL result resource in /home/www/example.com/searchresults2.php on line 72*/
/*$query = "SELECT song_id, title, album, writer, COUNT(*), MATCH (title, album, writer) 
 AGAINST ('$searchstring') FROM master_list WHERE MATCH(title, album, writer) AGAINST ('$searchstring') ORDER BY song_id";*/
/*The code directly below this produces page links with no results after the first page those results disappear when a link
is selected, they do not refresh afterwards.*/
/*$query = "SELECT COUNT(*) FROM master_list ORDER BY song_id";*/ 
 
 $result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
 $num_records = $row[0];
//calculate number of pages.
if ($num_records > $display) { 
$num_pages = ceil ($num_records/$display); 
} else {
$num_pages = 1;
}
} //end of np if.
//where to start returning results.
if (isset($_GET['s'])) { 
$start = $_GET['s'];
} else {
$start = 0;
} 
// Create the search function: 
function searchForm() 
{ 
 // Re-usable form 
 
 // variable setup for the form. 
 $searchwords = (isset($_GET['words'])? htmlspecialchars(stripslashes($_REQUEST['words'])) : ''); 
 $normal = (($_GET['mode'] == 'normal')? ' selected="selected"' : '' ); 
 $boolean = (($_GET['mode'] == 'boolean')? ' selected="selected"' : '' );
 
 echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">'; 
 echo '<input type="hidden" name="cmd" value="search" />'; 
 echo '<font size="-3">Search for: <input type="text" name="words" value="'.$searchwords.'" /></font> '; 
 echo '<font size="-3">Mode: </font>'; 
 echo '<select name="mode">'; 
 echo '<option value="normal"'.$normal.'>Normal</option>'; 
 echo '<option value="boolean"'.$boolean.'>Boolean</option>'; 
 echo '</select> '; 
 echo '<input type="submit" value="Search" />'; 
 echo '</form>'; 
}
// Create the navigation switch 
$cmd = (isset($_GET['cmd'])? $_GET['cmd'] : ''); 
switch($cmd) 
{ 
 default: 
 //echo '<h1><font size="-3">Search Database!</font></h1>'; 
 searchForm(); 
 break; 
 case "search": 
 searchForm(); 
 //echo '<h3><font size="-3">Search Results:</font></h3><br />'; 
 
 $searchstring = mysql_escape_string($_GET['words']); 
 switch($_GET['mode']) 
 { 
 case "normal": 
 $sql = "SELECT song_id, title, album, writer, MATCH (title, album, writer) AGAINST ('$searchstring') FROM master_list 
 WHERE MATCH(title, album, writer) AGAINST ('$searchstring') ORDER BY title LIMIT $start, $display"; 
 break; 
 
 case "boolean": 
 $sql = "SELECT song_id, title, album, writer, MATCH (title, album, writer) AGAINST ('$searchstring' IN BOOLEAN MODE) FROM master_list 
 WHERE MATCH(title, album, writer) AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY title LIMIT $start, $display"; 
 break; 
 } 
 
 // echo $sql;
 
$result = mysql_query($sql) or die (mysql_error()); 
while($row = mysql_fetch_object($result)){ 
 echo '<font size="-3">Title: '.stripslashes(htmlspecialchars($row->title)).'<br /></font>'; 
 echo '<font size="-3">Album: '.stripslashes(htmlspecialchars($row->album)).'<br /></font>';
 echo '<font size="-3">Writer: '.stripslashes(htmlspecialchars($row->writer)).'<br /></font>'; 
 echo '<hr size="-3" />'; 
 } 
 break; 
} 
// Make the links to other pages, if necessary.
if ($num_pages > 1) {
 echo '<br /><p>';
 // Determine what page the script is on.
 $current_page = ($start/$display) + 1;
 // If it's not the first page, make a Previous button.
 if ($current_page!= 1) {
 echo '<a href="searchresults2.php?s=' . ($start - $display) . '&np=' . $num_pages . '&words=' . $searchstring .'">Previous</a> '; 
 }
 // Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i!= $current_page) {
echo '<a href="searchresults2.php?s='.(($display * ($i - 1))).'&np='.$num_pages.'&words='.$searchstring .'">'.$i.'</a> '; 
} else {
echo $i.' ';
}
}
 // If it's not the last page, make a Next button.
 if ($current_page!= $num_pages) {
 echo '<a href="searchresults2.php?s=' . ($start + $display) . '&np=' . $num_pages . '&words=' . $searchstring .'">Next</a> '; 
 }
 echo '</p>';
} 
// End of links section.
?>

[edited by: eelixduppy at 6:55 pm (utc) on Mar. 14, 2007]
[edit reason] exemplified domain - see TOS [/edit]

eelixduppy

12:18 am on Mar 15, 2007 (gmt 0)



Hello, mrelliott, and Welcome to WebmasterWorld!

There are many threads floating around here that address this issue. Here's a good one [webmasterworld.com]. (hehe) This should get you on the right path. Of course, if you need any further help along the way, this community will be willing to help you with anything :)

Best of luck!

mrelliott

5:02 pm on Mar 15, 2007 (gmt 0)

10+ Year Member



I read over the posts that relate to my problem. Thanks. I've been trying to fix my counting code, what I've come up with doesn't cause an error. It appears to be doing exactly what I want it to do, which is count only the results requested by a search to help calculate how many pages are needed. but I can't get more then 10 results to appear and I know there's more. Here's my fixed code, I'm only putting in what I changed the rest is the same as the code I already placed in this post so I'll leave that out: 

 // Count the number of records
$query = "SELECT COUNT(*) FROM master_list WHERE MATCH(title, album, writer) AGAINST('$searchstring') ORDER BY song_id";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM);
 $num_records = $row[0];


I think the problem might be here as well: 

// Make the links to other pages, if necessary.
if ($num_pages > 1) {
 echo '<br /><p>';
 // Determine what page the script is on.
 $current_page = ($start/$display) + 1;
 // If it's not the first page, make a Previous button.
 if ($current_page!= 1) {
  echo '<a href="searchresults2.php?s=' . ($start - $display) . '&np=' . $num_pages . '&words=' . $searchstring .'">Previous</a> '; 
 }
 // Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i!= $current_page) {
echo '<a href="searchresults2.php?s='.(($display * ($i - 1))).'&np='.$num_pages.'&words='.$searchstring .'">'.$i.'</a> '; 
} else {
echo $i.' ';
}
}
 // If it's not the last page, make a Next button.
 if ($current_page!= $num_pages) {
  echo '<a href="searchresults2.php?s=' . ($start + $display) . '&np=' . $num_pages . '&words=' . $searchstring .'">Next</a> '; 
 }
 echo '</p>';
} 
// End of links section.

Here's what comes up in my address bar when the page loads after a search is done:
mywebsiteaddress/searchresults3.php?cmd=search&words=mercury&mode=normal

It looks to me like I'm missing my page number, here's what it looks like on a page where the pagination code works:
mywebsiteaddress/anightattheopera.php?s=10&np=2&sort=tracka

mrelliott

4:18 pm on Mar 26, 2007 (gmt 0)

10+ Year Member



I'm still fighting this. Any input would be great.

Nutter

5:26 pm on Mar 26, 2007 (gmt 0)

10+ Year Member



I guess my first question is why bother sorting a COUNT() query? Won't that just slow down a query that's going to give you the same result whether it's sorted or not?

What I've done is use "SELECT SQL_CALC_FOUND_ROWS field, field2, field_etc FROM table WHERE field='value' LIMIT 0, 10". That gets you the first 10 records plus you can follow it with "SELECT FOUND_ROWS()" to get the total number of records. That way you don't have to run multiple queries. You'll just need to replace the zero with an offset number based on the page number you're on.

mrelliott

5:37 pm on Mar 26, 2007 (gmt 0)

10+ Year Member



Using the count query was part of the course I took to learn PHP/MySQL. Apparently trying it with code such as a search engine, which was only mentioned in the course, doesn't go over well. It works fine with code selected from a single column and value. But with this I can't get past a result of ten and on all the other pages I can. I can't figure out why.

This code brings up the proper results when I tested it.

// Count the number of records
$query = "SELECT COUNT(*) FROM master_list WHERE MATCH(title, album, writer) AGAINST('$searchstring') ORDER BY song_id";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM);
$num_records = $row[0];

As did this code.

$sql = "SELECT song_id, title, album, writer, MATCH (title, album, writer) AGAINST ('$searchstring') FROM master_list
WHERE MATCH(title, album, writer) AGAINST ('$searchstring') ORDER BY title LIMIT $start, $display";

Even with the limit clause it brought up the proper results. With the limit clause and the rest of my pagination code it SHOULD see that there are more then ten results and add more page links to the bottom of the page. It's not and I don't know why.

mrelliott

5:39 pm on Mar 29, 2007 (gmt 0)

10+ Year Member



Can somebody PLEASE just read through this and tell me what am I doing wrong with this code. Why can't I get the page links to display at the bottom of the page. What am I missing here! Here's the full code. It does look up words but will only display a max of 10 even when I know there's more. If there's something wrong with it tell me, I'm new with this and I'm getting ready to just give up.

<?php
// Full-Text Search Example
// Connect to the database.
require_once ('./includes/mysql_connect.php'); //connect to database

//Records to display per page.
$display = 10;

//Check if the number of required pages has been determined.
if (isset($_GET['np'])) { //Already been determined.
$num_pages = $_GET['np'];
} else { //Need to determine.

// Count the number of records
$query = "SELECT COUNT(*) FROM master_list WHERE MATCH(title, album, writer) AGAINST('$searchstring') ORDER BY song_id";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM);
$num_records = $row[0];

//calculate number of pages.
if ($num_records > $display) {
$num_pages = ceil($num_records/$display);
} else {
$num_pages = 1;
}

} //end of np if.

//where to start returning results.
if (isset($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}

// Create the search function:
function searchForm()
{
// Re-usable form

// variable setup for the form.
$searchwords = (isset($_GET['words'])? htmlspecialchars(stripslashes($_REQUEST['words'])) : '');
$normal = (($_GET['mode'] == 'normal')? ' selected="selected"' : '' );
$boolean = (($_GET['mode'] == 'boolean')? ' selected="selected"' : '' );

echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">';
echo '<input type="hidden" name="cmd" value="search" />';
echo '<font size="-1">Search for: <input type="text" name="words" value="'.$searchwords.'" /></font> ';
echo '<font size="-1">Mode: </font>';
echo '<select name="mode">';
echo '<option value="normal"'.$normal.'>Normal</option>';
echo '<option value="boolean"'.$boolean.'>Boolean</option>';
echo '</select> ';
echo '<input type="submit" value="Search" />';
echo '</form>';
}

// Create the navigation switch
$cmd = (isset($_GET['cmd'])? $_GET['cmd'] : '');

switch($cmd)
{
default:
echo '<h1><font size="-1">Search Database!</font></h1>';
searchForm();
break;
case "search":
searchForm();
echo '<h3><font size="-1">Search Results:</font></h3><br />';

$searchstring = mysql_escape_string($_GET['words']);
switch($_GET['mode'])
{
case "normal":
$sql = "SELECT song_id, title, album, writer, MATCH (title, album, writer) AGAINST ('$searchstring') FROM master_list
WHERE MATCH(title, album, writer) AGAINST ('$searchstring') ORDER BY title LIMIT $start, $display";
break;

case "boolean":
$sql = "SELECT song_id, title, album, writer, MATCH (title, album, writer) AGAINST ('$searchstring' IN BOOLEAN MODE) FROM master_list
WHERE MATCH(title, album, writer) AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY title LIMIT $start, $display";
break;
}

// echo $sql;

$result = mysql_query($sql) or die (mysql_error());

while($row = mysql_fetch_object($result)){
echo '<font size="-1">Title: '.stripslashes(htmlspecialchars($row->title)).'<br /></font>';
echo '<font size="-1">Album: '.stripslashes(htmlspecialchars($row->album)).'<br /></font>';
echo '<font size="-1">Writer: '.stripslashes(htmlspecialchars($row->writer)).'<br /></font>';
echo '<hr size="-1" />';
}
break;
}
// Make the links to other pages, if necessary.
if ($num_pages > 1) {

echo '<br /><p>';
// Determine what page the script is on.
$current_page = ($start/$display) + 1;

// If it's not the first page, make a Previous button.
if ($current_page!= 1) {
echo '<a href="searchresults3.php?s='.($start - $display).'&np='.$num_pages.'&words='.$searchwords .'">Previous</a> ';
}

// Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i!= $current_page) {
echo '<a href="searchresults3.php?s='.(($display * ($i - 1))).'&np='.$num_pages.'&words='.$searchwords.'">'.$i.'</a> ';
} else {
echo $i.' ';
}
}

// If it's not the last page, make a Next button.
if ($current_page!= $num_pages) {
echo '<a href="searchresults3.php?s='.($start + $display).'&np='.$num_pages.'&words='.$searchwords.'">Next</a> ';
}

echo '</p>';

}
// End of links section.
?>

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 8:30 am May 2, 2026