Search results matching whole word

[Robert_Craig]

Hello,

I have been trying for the last few days to figure out how to get my search results to match full keywords that people search. For example in my database I have the names: Adam, Michael, James, David etc.

What is currently happening is if a user types the letter A and hits search, all names display because they contain the letter A. What I would like instead is if the user searches for just A then nothing should show. If someone searches for Dav then David should not show. I want it so that only if the keyword matches what is in the database should the result show.

I am totally new to php and have been using it for about 3 weeks. I am very familiar with html. I hope I have explained myself properly.

This is the code that I have so far:

<?php
 if(!isset($q)){
  echo '';
   } else {
    $query = mysqli_query($con, "SELECT * FROM videos WHERE active='1' AND title LIKE '%$q%' OR tags LIKE '%$q%' ORDER BY title");
    $num_rows = mysqli_num_rows($query);
?>	
   <div class="search-wrap"><p><strong><?php echo $num_rows; ?></strong> results for <?php echo $q; ?></p></div>
<?php
    while($row = mysqli_fetch_array($query)){
     $id = $row['id'];
     $title = $row['title'];
     $date = $row['date'];
     $tags = $row['tags'];

 echo ' <h3>' . $title . '</h3> '
   
';
   }
 }
?>

[NogDog]

Maybe use rlike so that you can use a regular expression to leverage the word boundary markers:

title RLIKE '[[:<:]]$q[[:>:]]'

PS: I hope you are sanitizing $q first?

[dalecosp]

MySQL has LIKE and REGEXP (and "RLIKE", thanks, NogDog), but falls short in a lot of ways. Of course, if this is a small-scale project, I'd stick with that until I got a lot more familiar with programming (and, in theory, PHP).

For any real-world larger-scale project though, I'd recommend using/integrating a full-text search engine like Sphinxsearch or Elasticsearch/Lucene, but that's quite another level of work to get into.

[dalecosp]

And, of course, the really important question ... in your code, what's being passed as "$q" ... ?

[Robert_Craig]

Thank you for you replies and thank you NogDog. I have changed the code to RLIKE and it is working how I wanted it to. With regards to your and dalecosp's question regarding $q, I don't think I am doing it correctly but the code still works. As I said earlier I am very new to PHP and am struggling to get a grip of it.

I keep getting this error (but as mentioned before the script still works)

PHP Notice:  Undefined index: q  on line 2
PHP Notice:  Undefined index: search on line 6

Here is my full code:

<?php
  if($_GET['q'] !== ''){
     $con = mysqli_connect('localhost','root','','database-test');
?>
<?php
 $q = $_GET['search'];
 if(!isset($q)){
  echo '';
   } else {
    $query = mysqli_query($con, "SELECT * FROM videos WHERE title RLIKE '[[:<:]]{$q}[[:>:]]' ORDER BY title");
    $num_rows = mysqli_num_rows($query);
?>	
   <div class="search-wrap"><p><strong><?php echo $num_rows; ?></strong> results for <?php echo $q; ?></p></div>
<?php
    while($row = mysqli_fetch_array($query)){
     $id = $row['id'];
     $title = $row['title'];
     $date = $row['date'];
     $tags = $row['tags'];

 echo ' <h3>' . $title . '</h3> '
   

';
   }
 }
?>
<?php
   } else {
     header('Location: index.php');
   }
?>

[NogDog]

The "undefined index" errors will occur if either of those GET params is not present in the page request. You may want to add some conditions using isset() and/or empty() to control what happens based on whether or not they are there. For instance:

if(!empty($_GET['search'])) {
  $q = mysqli_real_escape_string($con, $_GET['search']); // sanitize and assign
  // continue with your query stuff...
}
else {
  // whatever you want to do if 'search' not supplied.
}

[Robert_Craig]

Thanks NogDog. I've made the changes and the errors have gone.