Incorrect search query result

PHP
21

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% )
22

@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();
    }
23

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.

24

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

25

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

Online Demo - source included.

26

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

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.

28

Okay.

29

Check the following:

Live Search - Oniine Demo (Source included)

30

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