Searching, And Removing Next Page Link On Last Results Page.

[strago]

<?php
mysql_connect("localhost", "root", "PASSWORD") or die(mysql_error());
mysql_select_db("DATABASENAME") or die(mysql_error());

$pageresults = $_GET['pageresults'];
$query = $_GET['q'];

if(!empty($_GET['q'])){
$query=mysql_real_escape_string(trim($_GET['q']));
$searchSQL="SELECT * FROM memes WHERE `title` LIKE '%{$query}%' OR `source` LIKE '%{$query}%' OR `category` LIKE '%{$query}%' OR `body` LIKE '%{$query}%' OR `facebook` LIKE '%{$query}%' ORDER by `title` LIMIT $pageresults,10";
$back=($pageresults-10);
$next=$pageresults+10;

$searchResult=mysql_query($searchSQL);
if(mysql_num_rows($searchResult) <= 0)
{
echo "No results";
} else {
while ($row=mysql_fetch_assoc($searchResult)){
 $results[]="<BR><a href='file.php?id={$row['id']}'>{$row['title']}</a> - {$row['source']}";
}
echo implode($results);
 echo "<P>";
if ($pageresults > 1)
echo "<P><a href='file.php?q=$query&pageresults=$back'>Back</a> - ";
if(mysql_num_rows($searchResult) < $next+1)
echo " <a href='file.php?q=$query&pageresults=$next'>Next</a>";
}
}
?>

 

if(mysql_num_rows($searchResult) < $next+1)

 

is giving me trouble. How do you get rid of the 'Next' link on the last page of the results?

 

And how do you remove the requirement of having in the URL

 

&pageresults=0

 

I would like Page one to be like

 

file.php?q=keyword

 

then Page two and so on, as it should be...

 

file.php?q=keyword&pageresults=0

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/site1/public_html/file.php on line 15

 

get's generated with the pageresults gone, which is

 

if(mysql_num_rows($searchResult) <= 0)

 

At

 

$searchSQL="SELECT * FROM memes WHERE `title` LIKE '%{$query}%' OR `source` LIKE '%{$query}%' OR `category` LIKE '%{$query}%' OR `body` LIKE '%{$query}%' OR `link` LIKE '%{$query}%' ORDER by `title` LIMIT $pageresults,10";

 

how do you get it to just search everything with out having to include everything in it like that?

[Psycho]

There are a ton of tutorials out there on pagination and yours is missing many of the very basics. The code below solves almost all of the issues you asked about except how to simplify the search. Searching multiple fields using the same search term is a basic part of what is known as "full text" searching. To do that requires setting up the database table in a specific way and changing the queries. Do a Google search for "mysql full text search" and see what you will need to do. There is too much to explain to try and explain in a forum post.

 

<?php

$records_per_page = 10;

mysql_connect("localhost", "root", "PASSWORD") or die(mysql_error());
mysql_select_db("DATABASENAME") or die(mysql_error());

//Get the search string
$search = isset($_GET['q']) ? mysql_real_escape_string(trim($_GET['q'])) : '';

if(!empty($search))
{
   //Create the BASE query
   $FROM = "FROM memes
		 WHERE `title` LIKE '%{$query}%'
		    OR `source` LIKE '%{$query}%'
		    OR `category` LIKE '%{$query}%'
		    OR `body` LIKE '%{$query}%'
		    OR `facebook` LIKE '%{$query}%'";

   //Create and execute query to get total count of records
   $query = "SELECT COUNT(*) {$FROM}";
   $result = mysql_query($query) or die(mysql_error());
   $total_records = mysql_result($result, 0);
   //Determine total pages for search term
   $total_pages = ceil($total_records / $records_per_page);

   //Get selected page (or set to page 1)
   $page = isset($_GET['page']) ? intval($_GET['page']) : 1;
   //Ensure page is between 1 and total pages
   $page = max(min($total_pages, $page), 1);
   //Calc limit start value
   $limit = ($page - 1) * $records_per_page;

   //Create and execute query to get current page's records
   $query = "SELECT id, title, source
		  {$FROM}
		  ORDER by `title`
		  LIMIT {$limit}, {$records_per_page}";
   $result = mysql_query($query) or die(mysql_error());

   //Determine if there were results
   if(!mysql_num_rows($result))
   {
    echo "No results";
   }
   else
   {
    //Create results output
    while ($row = mysql_fetch_assoc($result))
    {
		 $results[]="<a href='file.php?id={$row['id']}'>{$row['title']}</a> - {$row['source']}";
    }
    echo implode("<br>\n", $results);

    //Create navigation links
    echo "<p>";
    if ($page > 1)
    {
	    $prev = $page - 1;
	    echo "<p><a href='file.php?q={$search}&page={$prev}'>Back</a>";
    }
    if($page > 1 && $page < $total_pages) { echo " - "; }
    if($page < $total_pages)
    {
	    $next = $page + 1;
	    echo " <a href='file.php?q={$search}&page={$next}'>Next</a>";
    }
   }
}
?>

[strago]

Thanks. I'm a total php n00bie so I was using Google and another php script to try to get it working. That's perfect. I just had to add back the

 

$query = $_GET['q'];

 

because the search results will actually be an index by topic, with each page based on the query in the URL.