如果只提供一个字母，PHP搜索结果会输出不同的结果

Question

The search code I am working works for the search if I put 2 or more letters but when I give it a query with one letter it gives me all of the lists of the data I have a where status like 'found' clause but it still gives me other results if I put a single letter. The search part works if I put two or more letters properly but if I put one letter it gives me different query result.

Here is my SQL query

> $searchtext = ''; if(isset($_GET['q'])) $searchtext =
> mysql_real_escape_string($_GET['q']); if($searchtext) {
>      $per_page =16;
>                         $pages_query = mysql_query("SELECT COUNT('PersonID') FROM persons where firstname like '$searchtext' or
> lastname like '$searchtext' and status like 'found' ");
>                         $pages = ceil(mysql_result($pages_query,0) / $per_page);
> 
>                         $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
>                         $start = ($page - 1) * $per_page;
> 
> 
> // And set the first page $first_page = "1";
> 
>      $num = mysql_num_rows($pages_query);
>     $last_page = ($num / $per_page);
>     $next_page = $page + 1;
>     $last_page=$pages;
> 
> 
> 

>     $query=mysql_query("select * from persons where status like 'found' and firstname like '%$searchtext%' or lastname like
> '%$searchtext%' order by date desc LIMIT $start,$per_page ");
> 
>    $numrows = mysql_num_rows($query);
> 
> 
> }
> 
> else  {
> 
>    $per_page =16;
>                         $pages_query = mysql_query("SELECT COUNT('PersonID') FROM persons where status like 'found' ");
>                         $pages = ceil(mysql_result($pages_query,0) / $per_page);
> 
>                         $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
>                         $start = ($page - 1) * $per_page;
>                         $last_page=$pages;
> 
> // And set the first page $first_page = "1";
> 
> 
>     $query=mysql_query("select * from persons where status like 'found'  order by date desc LIMIT $start,$per_page ");   
> $count=mysql_query("select * from persons where status like 'found'");
> $numrows = mysql_num_rows($count);
> 
> }

This screen shot is the default view. The query for this is $query=mysql_query("select * from persons where status like 'found' order by date desc LIMIT $start,$per_page ");

This screen shot is the search view when I search for dan in the default view for found and recieve this result this is the query I use for outputing. ` $query=mysql_query("select * from persons where status like 'found' and firstname like '%$searchtext%' or lastname like '%$searchtext%' order by date desc LIMIT $start,$per_page ");

$numrows = mysql_num_rows($query);`

The final picture shows that I searched letter a. The result that gave to me come from all fields. Also, I have a conflict that calls also the missing list I want to limit it only for the found only and that's also the problem with the query of the missing it also finds in whole fields of found.

The SQL command I use here is the one in picture 2

Answer 1

Your sql query needs to group your conditions properly

$query=mysql_query("select * from persons where status like 'found' and (firstname like '%$searchtext%' or lastname like '%$searchtext%') order by date desc LIMIT $start,$per_page ");

What you were doing is equivalent to something like this in code:

if (status == 'found' && firstname like 'a')
{
    return row;
}
else if (lastname like 'a')
{
    return row;
}

If you see this it may be a bit clearer, if the first condition fails (the only one that checks if status == 'found' then it will skip to the next condition (after the OR statement) and only check if lastname matches.

You will need to group your select statement condition so it checks it correctly:

...where status like 'found' and (firstname like '%$searchtext%' or lastname like '%$searchtext%') order by...

In this case, we group both firstname and lastname conditions together (in parentheses) so that the entire OR condition results to a single true/false answer, that answer is then applied to the status check after.