Incorrect search query result

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

@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…

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

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.

@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.

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.

@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…

If you temporarily do this, what do you see?

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

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.

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

Many thanks for the alternative solution:

I was hoping for a solution which used IN without having to duplicate the AND statements.

Test Results

// Mittineague TEST START =======================
SELECT * FROM cities WHERE name LIKE %XXX%  

  OR (`cities`.`name`    LIKE :variant_name_0 
  OR `cities`.`state_name`   LIKE :variant_state_name_0)

  OR (`cities`.`name`    LIKE :variant_name_1 
  OR `cities`.`state_name`   LIKE :variant_state_name_1)

  OR (`cities`.`name`    LIKE :variant_name_2 
  OR `cities`.`state_name`   LIKE :variant_state_name_2)

   OR (`cities`.`name`    LIKE :variant_name_3 
   OR `cities`.`state_name`   LIKE :variant_state_name_3)

// Mittineague TEST END =======================

// John_Betong:
$sql:   SELECT * 
      FROM cities2 
      WHERE concat(name, " ", state_name) LIKE ?  
      AND concat(name, " ", state_name) LIKE ?  
      AND concat(name, " ", state_name) LIKE ?  
      AND concat(name, " ", state_name) LIKE ? 

Params:   array( %a%",   "%b%",   "%c%",   "%d% )

@John_Betong

I have tired to run the code within my script and am getting error…the first error occur when I add “string” to getParams( string $params=NULL) ( Catchable fatal error: Argument 1 passed to getParams() must be an instance of string, string given, called in C:**\index.php on line 64 and defined in C:*\index.php on line 25 )

without "string " getParams( $params=NULL)
Error!: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

please kindly help me correct the code…Thanks in advance for your time.

//=================================
//
//  Validate and clean input text
//    
//=================================
function getParams(  $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 {

include 'db.php';

$result = $pdo->prepare( $sql );

foreach($aParams as $aParamsValue ) {
$result->bindParam(':business_name', $aParamsValue, PDO::PARAM_STR);
$result->bindParam(':business_title', $aParamsValue, PDO::PARAM_STR);
$result->bindParam(':business_description', $aParamsValue, PDO::PARAM_STR);
        $result->execute();
}

foreach ($result as $row){ 
$bizDetails[] = array( 
'business_name' =>$row['business_name'],
'business_title' =>$row['business_title'],
'business_description' =>$row['business_description'],
'company_logo' =>$row['company_logo'],
'email_add' =>$row['email_add'] ); }



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

It looks s though you are not running PHP7?

You could try this which will get you over the first hurdle.

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

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

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

  return $result;
}//

If the above does not solve your problem please forward the complete script and I will make sure it runs on my computer before returnig the script.

@John_Betong

am not on PHP 7. am running 5.5 version.

I try again with the code you post but getting this error…
Catchable fatal error: Argument 1 passed to getParams() must be
an instance of string, string given, called in
C:****index.php on line 67 and defined
in C:***t\index.php on line 25

below link is the php and the sql file…

phpAndSQL.zip (1.7 KB)

I have uploaded a copy of your database file and have hopefully eliminated all PHP 7 Strict Mode calls.

Online Demo - source included.

@John_Betong

Thanks so much for your time… the script is now working on my end…am so happy, you really safe my day…thanks once again.

Am thinking if I can improve the script to search for “car” if the user entered “cars” in the search box. From the online demo when I search for “berlin” it return result but when I search for “berlins” no result…

1 Like

Hi @aosworks

I am glad I was able to help.

As far as searching and not finding “berlins” you can go up one level to the menu and try a live search.

When using the live search There are 15 results fo “bet” and only one result for “Berl”

Try the following joke search, there are over 3,000 rows and it quickly finds partial matches separated by spaces.

Live search demo

I will try to convert your demo to a live search.

Okay.

Check the following:

Live Search - Oniine Demo (Source included)

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.