Incorrect search query result

PHP
1

I will be glad for helps on this search query…

I have Queen View Hotel in the business_name column and I search for Queen Hotel the result will be no match found, when search for Queen View Hotel it will return value…below is the code am running…

$bquery = "(SELECT business_name
, business_title
, business_description
,company_logo
, email_add FROM business WHERE 
business_name LIKE :business_name 
OR business_title LIKE :business_title 
OR business_description LIKE :business_description)";

$s = $pdo->prepare($bquery);
$placeholders[':business_name'] = '%' . $keyword . '%';
$placeholders[':business_title'] = '%' . $keyword . '%';
$placeholders[':business_description'] = '%' . $keyword . '%';
$s->execute($placeholders);

I want it to return value Queen View Hotel when Queen Hotel or Queen or Hotel is enter in the search box.

Thanks

2

In your application language transform the value using a simple string comparison.

3

am using php…please how can I do that?..this is how I get the keyword entered in the search box//

if(isset($_GET['keyword'])) { 
$keyword = filter_var($_GET['keyword'], FILTER_SANITIZE_STRING);
4

Take a look at this demo, scroll to the bottom and view _Ajax_form.php and db-results.php source files.

5

The way your wildcards are in the code example,
“Queen”, “Queen View”, “View”, “View Hotel” and “Hotel” will all match
“Queen View Hotel”

“Queen Hotel” will not match.

Depending on how many "Queen something Hotel"s you have, you might get away with replacing the space with a wildcard.

6

@Mittineague pls help me with the php sample code to replace the space with wildcard

7

I guess the space between the words is the issue…how can I take care of the space

8

@John_Betong I was unable to find the answer from the source file there…pls kindly help me with code sample relating to the code I post to solve the problem

9

Sure, basic PHP string function
http://php.net/manual/en/function.str-replace.php

After you trim() " " is the $search and “%” is the $replace

10

@Mittineague pls kindly help with written php code to take care of the space with wildcard…back on the project now and I need to solve this now, not getting it yet…thanks.

11

What about the str_replace don’t you understand or are having trouble with?

12

@Mittineague I dont understand it…pls help with the code I posted with the php code on how I can replace the space with wildcard…when I enter Queen the result should include Queen View Hotel…

13

http://php.net/manual/en/function.str-replace.php

mixed str_replace ( mixed $search , mixed $replace , mixed $subject [, int &$count ] )

$search would be the space " "
$replace would be the percent symbol %
$subject is the original string

14

Take another look at my demo, try entering a b c d e and notice how the string is dynamically built.

Online Demo

You query will need to concat three fields and pass the parameters as shown.

15

@Mittineague

I try it like

      $keyword = filter_var($_GET['keyword'], FILTER_SANITIZE_STRING);
    $keyword = str_replace (" ", "%", $keyword);

$placeholders[':business_name'] = '%' . $keyword . '%';

and am still getting no match found. the % appear between the words when I echo it.

16

this is my code…I will be glad for any help with the corrected code…just help me modify the code to search better…

if(isset($_GET['keyword'])) 
$keyword = filter_var($_GET['keyword'], FILTER_SANITIZE_STRING);

$keyword = str_replace (" ", "%", $keyword);

include db.php';

$bquery = "(SELECT business_name, business_title, business_description,
company_logo, email_add FROM business WHERE 
(business_name LIKE :business_name 
OR business_title LIKE :business_title 
OR business_description LIKE :business_description)   )";

$s = $pdo->prepare($bquery);
$placeholders[':business_name'] = '%' . $keyword . '%';
$placeholders[':business_title'] = '%' . $keyword . '%';
$placeholders[':business_description'] = '%' . $keyword . '%';
$s->execute($placeholders);

pls take this code and correct it for me and if the code have to be re written kindly help me out…thanks for your time.

17

@John_Betong I try to follow what you have on you page but am having issue getting it done…pls help me write out the code…

18

If you temporarily do this, what do you see?

$placeholders[':business_description'] = '%' . $keyword . '%';
var_dump($placeholders);
exit;
$s->execute($placeholders);
19

Try replacing your SQL script with this:

//=================================
//
//  Validate and clean input text
//    
//=================================
function getParams( string $params=NULL)
// :array
{
  $result = NULL;

  while (strpos($params, '  ') ) {
    $params = str_replace('  ',  ' ', $params);
  }
  $result = explode(' ', $params);

  if( empty($result) ):
    $result[] = $params;
  endif;

  return $result;
}//

//=================================
//
// dynamic build SQL statement
//
//=================================
function getSqlPdo(array $aParams=NULL)
// :string
{
  $sql = 'SELECT * FROM business WHERE concat( business_name, ' ', business_title, '  ' , business_description )'; 
  foreach($aParams as $i2 => $param):
    if($i2==0):
      $sql = $sql .' LIKE ? ';
    else:  
      $sql = $sql .' AND concat( business_name, ' ', business_title, '  ' , business_description ) LIKE ? ';
    endif;  
  endforeach;  
  // echo DEBUG, '$sql = ',$sql, '<br />';

  return $sql;
}//

$aParams = getParams( $_POST['keyword'] );

$sql = getSqlPdo( $aParams );

    try {
        $result   = $db->prepare( $sql );
        echo '<pre>';
           print_r( $result );
        echo '</pre>';
      
 }catch (PDOException $e){
      print "Error!: " . $e->getMessage() . "<br/>";
      die();
    }

If on the off-chance that does not work then please zip and send me a partial copy of your “business” table with the following fields: business_name, business_title and business_description. On receipt of the zip file I will create an online demo,

Edit:
No doubt there are better ways to create the SQL statement that contains an unllimited number of partial space separated words. If anyone can supply an alternative solution I would be very grateful.

20

Messy looking, but it works

Build the query

if ( ($extreme_mode_chkd) && (is_array($variants_arr)) ) {
  for ($i = 0; $i < count($variants_arr); $i++) {
    if ($cities_chkd) {
	  $search_query .= " OR (`cities`.`phoneme` LIKE :variant_phoneme_" . $i . " OR `cities`.`transliteration_phoneme` LIKE :variant_phoneme_" . $i . " OR `cities`.`transliteration` LIKE :variant_name_" . $i . " OR `cities`.`name` LIKE :variant_name_" . $i . ")";
    }

prepare it

$pdo_stmt = $pdo_mysql->prepare($search_query);

bind variables

if ( ($extreme_mode_chkd) && (is_array($variants_arr)) ) {
  for ($i = 0; $i < count($variants_arr); $i++) {
    $pdo_stmt->bindParam(':variant_name_' . $i, $variants_arr[$i], PDO::PARAM_STR);
    $search_phoneme_{$i} = metaphone($variants_arr[$i]);
    $pdo_stmt->bindParam(':variant_phoneme_' . $i, $search_phoneme_{$i}, PDO::PARAM_STR);
  }
}

execute

    if ($pdo_stmt->execute()) {
      $search_result = "";
      while ($row = $pdo_stmt->fetch()) {
1 Like