[SOLVED] PHP MySQL custom search engine issues with pagination

ncovill · October 31, 2009

A site of mine has now grown enough to where I needed to implement a search feature, so people can simply type a word/phrase into the search field and it will bring up every post with what they searched for.

I am having some issues with it. It does work, but the pagination is screwy. It seems to only be the pagination links (such as: Next, Previous, First, Last, etc.). Anyway, I am pretty sure you guys understand what the pagination is supposed to do so, here's some code:

The simple form:

<form method="post" action="search.php" id="searchform">
<input type="text" name="keywords" />
<input type="submit" name="searchsubmit" value="search" />
</form>

the search.php (this is what puts the functions together and displays the info):

// connect to the db
db_connect();
$keywords = $_POST['keywords'];
// And we remind them what they searched for
echo '<h1 style="padding: 0px 0px 8px; color: #F68A20; font-size: 24px;"><b>Searched For</b> 
<span style="font-size: 12px; font-weight: normal; color: #000000;">[ '. $keywords .' ]</span></h1>';
?>
<div class="pagination">
<?php
echo pagination_search();
?>
</div>
<?php
$submit = $_POST['searchsubmit'];

if ($submit)
{
echo "Results:<br />";
$keywords = strip_tags($keywords);
$keywords = trim($keywords);
if ($keywords == "")
	{
	echo "You forgot to enter a search term.<br />";
	}
// if they did not enter a search term, give them an error
$sql = "SELECT * FROM `posts` WHERE `content` LIKE '%$keywords%'";
$result = mysql_query($sql);
$anymatches = mysql_num_rows($result);
if ($anymatches > 0)
	{
	// now we search for our search term
	echo get_entries_search();
	}
else
	{
	echo "Sorry, but we could not find an entry to match your search.<br /><br />";
	}
}

and the functions, one to get the entries, one for pagination:

function get_entries_search()
{
$connection = db_connect();

/****** pagination ******/
// find out how many rows are in the table
$keywords = $_POST['keywords'];
$sql = "SELECT COUNT(*) FROM `posts` WHERE `content` LIKE '%$keywords%'";

$testsearch = $_REQUEST['testsearch'];
if ($testsearch != "")
	{
	$keywords = $testsearch;
	}

$result = mysql_query($sql, $connection);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
//find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage']))
	{
	// cast var as int
	$currentpage = $_GET['currentpage'];
	}
else
	{
	// default page num
	$currentpage = 1;
	}

// if current page is greater than total pages...
if ($currentpage > $totalpages)
	{
	// set current page to last page
	$currentpage = $totalpages;
	}

// if current page is less than first page...
if ($currentpage < 1)
	{
	// set current page to first page
	$currentpage = 1;
	}

// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;
/****** end pagination ******/

// get info from the db
$query = "SELECT posts.id, posts.content, posts.name, posts.datetime, posts.category, posts.comments, posts.votes1, posts.votes2, posts.votes3, categories.category
		  FROM `posts` LEFT OUTER JOIN `categories`
		  ON posts.category = categories.cat_id ";

if ($cat_id = $_REQUEST['cat_id'])
	{
	if (is_int($cat_id))
		{
		$query .= " WHERE categories.cat_id = ". $cat_id;
		}
	else
		{
		$query .= " WHERE categories.category = \"";
		$query .= mysql_real_escape_string($cat_id);
		$query .= "\"";
		}
	}

$query .= " WHERE posts.content LIKE '%$keywords%'";
$query .= " ORDER BY `id` DESC LIMIT ". $offset .", ". $rowsperpage;
$result = mysql_query($query, $connection);

return create_output($result);
}

function pagination_search()
{
$connection = db_connect();

/****** pagination ******/
// find out how many rows are in the table
$keywords = $_POST['keywords'];
$sql = "SELECT COUNT(*) FROM `posts` WHERE `content` LIKE '%$keywords%'";

$testsearch = $_REQUEST['testsearch'];
if ($testsearch != "")
	{
	$keywords = $testsearch;
	}

$result = mysql_query($sql, $connection);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
//find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage']))
	{
	// cast var as int
	$currentpage = $_GET['currentpage'];
	}
else
	{
	// default page num
	$currentpage = 1;
	}

// if current page is greater than total pages...
if ($currentpage > $totalpages)
	{
	// set current page to last page
	$currentpage = $totalpages;
	}

// if current page is less than first page...
if ($currentpage < 1)
	{
	// set current page to first page
	$currentpage = 1;
	}

// the offset of the list, based on current page
//$offset = ($currentpage - 1) * $rowsperpage;

/******  build the pagination links ******/
// if not on page 1, don't show back links

if ($currentpage > 1)
	{
	// show << link to go back to page 1
	echo "<div class='paginleft'><a href='search.php?action=get_entries_search&testsearch=$keywords&currentpage=1'>First</a></div>";
	}
else
	{
	echo "<div class='paginleft'>First</div>";
	}

// if not on last page, show forward and last page links
if ($currentpage != $totalpages)
	{
	// get next page
	$nextpage = $currentpage + 1;
	// echo forward link for next page
	echo "<div class='paginright'><a href='search.php?action=get_entries_search&testsearch=$keywords&currentpage=$nextpage'>Next</a></div>";
	}
else
	{
	echo "<div class='paginright'>Next</div>";
	}

// if not on page 1, don't show back links
if ($currentpage > 1)
	{
	// get previous page num
	$prevpage = $currentpage - 1;
	// show < link to go back to 1 page
	echo "<div class='paginright'>|</div>";
	echo "<div class='paginright'><a href='search.php?action=get_entries_search&testsearch=$keywords&currentpage=$prevpage'>Previous</a></div>";
	}
else
	{
	echo "<div class='paginright'>|</div>";
	echo "<div class='paginright'>Previous</div>";
	}

// range of num links to show
$range = 5;
$range2 = 6;

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range2) + 1); $x++)
	{
	// if it's a valid page number...
	if (($x > 0) && ($x <= $totalpages))
		{
		// if we're on current page...
		if ($x == $currentpage)
			{
			// highlight it but don't make a link
			echo "<div class='paginx'><b>$x</b></div>";
			}
		// if not current page
		else
			{
			// make it a link
			echo "<div class='paginx'><a href='search.php?action=get_entries_search&testsearch=$keywords&currentpage=$x'>$x</a></div>";
			}
		}
	}

if ($currentpage != $totalpages)
	{
	if ($totalpages > 7)
		{
		if ($totalpages < (($currentpage + $range2) + 1))
			{
			echo "";
			}
		else
			{
			echo "<div class='paginx'> ... </div> ";
			echo "<div class='paginx'><a href='search.php?action=get_entries_search&testsearch=$keywords&currentpage=$totalpages'>$totalpages</a></div> ";
			}
		}
	// echo forward link for lastpage
	echo "<div class='paginlast'><a href='search.php?action=get_entries_search&testsearch=$keywords&currentpage=$totalpages'>Last</a></div> ";
	}
else
	{
	echo "<div class='paginlast'>Last</div>";
	}
}

As I said, I believe the only problem is with the linkage in the pagination.. it's the only part I'm not quite sure about.

Iainzor · October 31, 2009

In your query you need to put SQL_CALC_FOUND_ROWS when you use a limit in order to get all of the rows found, like this:

SELECT
    SQL_CALC_FOUND_ROWS
    ...

Note that there is no comma after the SQL_CALC_FOUND_ROWS. After you execute the query, you execute another query:

SELECT FOUND_ROWS()

That will return all of the results found, which can be used to calculate how many pages there are.

Also, if you are going to be searching, I suggest you look into FULLTEXT searching with MySQL. It's quite powerful and easy to do. You'll have to set your content column to index for fulltext searching though.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

ncovill · October 31, 2009

Thank you for the reply.

Sorry about my explanation, I did it quickly before I went out... but the problem isn't showing the pages, it displays the proper amount of pages perfectly, and displays the results even. The only problem is that when clicking on any of the links in the pagination, it goes to a page with every single page number(starting at 1 ending at 26, every single post in the database, not just the searched phrase) in the pagination but nothing is displayed.

I am not sure how to make Next, Previous, Last, First, $x all link to the specified search term they have entered.

Iainzor · October 31, 2009

It looks like your problem is you are submitting your form as a POST. You should use GET and in the pagination, do something like this:

<a href="path/to/page?keyword=$_GET['keyword']&page=x">Page x</a>

ncovill · October 31, 2009

No go still doing the same thing, I even tried with REQUEST, grrrr.... this is frustrating me to no end!

ncovill · October 31, 2009

Well, made some headway anyway... using $_REQUEST['keywords'] and making the links this:

"search.php?action=get_entries_search&keywords=$keywords&currentpage=1"

Works to the sense as always showing the right # of pages lol. Except still.. when clicking on one of the pagination links, it doesn't display the results, even if i click back to the first page

EDIT: having the links "search.php?keywords=$keywords&currentpage=1" also does the same thing... hmm

ncovill · October 31, 2009

YES! I DID IT!!! lol i had to get rid of the if ($submit) statement since it was only executing that when the search button was clicked

Now... I gotta figure out how to make the keywords all bolded ;x

Sign In

[SOLVED] PHP MySQL custom search engine issues with pagination

Recommended Posts

ncovill

Link to comment

Share on other sites

Iainzor

Link to comment

Share on other sites

ncovill

Link to comment

Share on other sites

Iainzor

Link to comment

Share on other sites

ncovill

Link to comment

Share on other sites

ncovill

Link to comment

Share on other sites

ncovill

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information