mysqli prepared statements - LIKE query won't return records with exactly matching word

[geno11x11]

Hello all,

 

I have the following working query:

$sql="SELECT * FROM Table WHERE Name LIKE CONCAT('%',?,'%')"
$library->connectDB(&$mysqli,&$stmt);  
if ($mysqli->connect_errno) {echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}
$stmt = $mysqli->prepare($sql); 
if (false === $stmt) {die('Query Prep Failed' . $mysqli->error . $mysqli->errno);}
$stmt->bind_param('s',$nameSearch);   //string
$stmt->execute();
if (false===$stmt) {die('execute() failed: '.$stmt->error);}
$stmt->store_result();

If I am looking for a value such as "Flashlight" I can search with partial matching characters (wildcards) for "flash", "light" or any matching character such as "F", "f", "L", etc. and get the desired results.

 

My problem arises when I search with the entire word:  Using "Flashlight" or "flashlight" (i.e., $nameSearch = "Flashlight"), the query returns zero records. 

 

MySQL documentation says this should work.  What is wrong with the query or the code?  

 

 

Additionally, using mySQL Workbench, I had success with the following query (can't get prepared statements to work with this utility):

SELECT Name FROM Table WHERE Name LIKE '%flashlight%';

Edited September 1, 2013 by geno11x11

[vinny42]

Have you tried using '%flashlight%' as input, without the CONCAT:

 

$sql = "SELECT * FROM table WHERE name like ?";

 

Just to see if the problem is in the combinaton of the CONCAT and the prepared statement (which I doubt, but this is bughunting so you neever know...)

[geno11x11]

I originally started there and it returned no records -- some research turned up the CONCAT() expression which got me to where I am so far...  But I tried your suggestion nevertheless, and no records were returned -- I also checked the php online manual for the proper syntax and, although your format is shown as an example, it does not work with my code. No errors thrown, just no results...

[geno11x11]

Running more searches, I have found that in one case, a 4-character name has some peculiar search characteristics:

 

The name is gbks: It will appear with a search of any matching single letter, and a 2-character ks, No other 2, 3, or 4 character combination works.

An 8-character name can be searched as far as 7-characters before it fails -- the more characters used the narrower the filter so fewer other records appear..

 

Very strange... any ideas?

Edited September 1, 2013 by geno11x11

[vinny42]

Weird.  Is your table UTF8 or Latin1? And what is your client-encoding setting? 

[geno11x11]

character_set_cleint = utf8

character_set_database = utf8

collationt_database = utf8_unicode_ci

[vinny42]

Exceedingly odd.

 

Can you reproduce the problem in a small standalone example so I can run it on my system to see what happens?

[geno11x11]

I realize this is more elaborate and error prone than it had to be, but I already had the code written with prepared statements and I didn't want to start new with legacy queries. Let me know if this is not sufficient. Thanks, GK

 

 

$nameSearch = "SomethingInYourDatabase-setupforString";

$mysqli = new mysqli($serverName, $userName, $password, $database) or die('There was a problem connecting to the database');

    if ($mysqli->connect_errno) {echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}

$sql="SELECT * FROM Table WHERE Name LIKE CONCAT('%',?,'%')"

$stmt = $mysqli->prepare($sql); 

    if (false === $stmt) {die('Query Prep Failed' . $mysqli->error . $mysqli->errno);}

$stmt->bind_param('s',$nameSearch);   //string

$stmt->execute();

if (false===$stmt) {die('execute() failed: '.$stmt->error);}

$stmt->store_result();

$rowCount = $stmt->num_rows;echo "<b>Rowcount: </b>",$rowCount;


$fieldList = array();

$meta = $stmt->result_metadata();  

print_r($meta);

while ( $field = $meta->fetch_field() ) 

    {  

       $fieldList[] = $field->name;   

    }

$x=call_user_func_array(array($stmt, 'bind_result'), &$fieldList);

    if (false === $x) {die('bind_param()error :' . $mysqli->error);}


while ( $stmt->fetch() )

    {

    $assocRow=$library->buildAssocRow(&$mysqli,&$stmt,&$fieldList,&$colNames);

    }

var_dump($assocRow);

Edited September 1, 2013 by geno11x11

[vinny42]

That code uses pass-by-reference, which has been abolished a *long* time ago, are you running an ancient version of PHP or do you have error-reporting disabled?

 

This works for me:

 

 

CREATE TABLE `mytable` (`myname` VARCHAR(50) NULL DEFAULT NULL)COLLATE='utf8_general_ci'ENGINE=InnoDB;

INSERT INTO `mytable` (`myname`) VALUES ('SomethingInYourDatabase-setupforString');

INSERT INTO `mytable` (`myname`) VALUES ('SomethingInYourDatabase-setupforString2');

INSERT INTO `mytable` (`myname`) VALUES ('3SomethingInYourDatabase-setupforString');

INSERT INTO `mytable` (`myname`) VALUES ('4SomethingInYourDatabase-setupforString5');

<?php

echo '<pre>';

$nameSearch = "SomethingInYourDatabase-setupforString";

$mysqli = new mysqli($serverName, $userName, $password, $database) or die('There was a problem connecting to the database');

if ($mysqli->connect_errno) {

    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;

}

mysqli_query($mysqli, "SET NAMES UTF8");

$sql = "SELECT myname FROM mytable WHERE myname LIKE CONCAT('%',?,'%')";

 

for ($i = 1; $i < 12; $i++) {

    $strSearchText = substr($nameSearch, 2, $i);

    echo 'Searching for "' . $strSearchText . '"<br/>';

 

    $stmt = $mysqli->prepare($sql);

    if (false === $stmt) {

        die('Query Prep Failed' . $mysqli->error . $mysqli->errno);

    }

    $stmt->bind_param('s', $strSearchText);

    $stmt->execute();

    if (false === $stmt) {

        die('execute() failed: ' . $stmt->error);

    }

    $stmt->store_result();

    $rowCount = $stmt->num_rows;

    if ($rowCount == 4) {

        echo '<b>OK</b><br/>';

    }

    else {

        echo '<b>FAIL!!</b><br/>';

 

 

        $meta = $stmt->result_metadata();

 

        $variables = array();

        while ($field = $meta->fetch_field()) {

            $variables[] = & $data[$field->name];

        }

 

        call_user_func_array(array($stmt, 'bind_result'), $variables);

 

        $intCountResults = 0;

        while ($stmt->fetch()) {

            var_dump($variables);

            $intCountResults++;

        }

    }

}

exit;

 

 

 

[mac_gyver]

i'm guessing that your searches that don't match anything have some white-space/non-printing characters in with the data - new-line, tab, space, null.

 

where are you getting/submitting the $nameSearch value from and are you doing any validation/filtering/trimming on it?

[geno11x11]

That code uses pass-by-reference, which has been abolished a *long* time ago, are you running an ancient version of PHP or do you have error-reporting disabled?

 

This works for me:

CREATE TABLE `mytable` (`myname` VARCHAR(50) NULL DEFAULT NULL)COLLATE='utf8_general_ci'ENGINE=InnoDB;

INSERT INTO `mytable` (`myname`) VALUES ('SomethingInYourDatabase-setupforString');

INSERT INTO `mytable` (`myname`) VALUES ('SomethingInYourDatabase-setupforString2');

INSERT INTO `mytable` (`myname`) VALUES ('3SomethingInYourDatabase-setupforString');

INSERT INTO `mytable` (`myname`) VALUES ('4SomethingInYourDatabase-setupforString5');

<?php

echo '<pre>';

$nameSearch = "SomethingInYourDatabase-setupforString";

$mysqli = new mysqli($serverName, $userName, $password, $database) or die('There was a problem connecting to the database');

if ($mysqli->connect_errno) {

    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;

}

mysqli_query($mysqli, "SET NAMES UTF8");

$sql = "SELECT myname FROM mytable WHERE myname LIKE CONCAT('%',?,'%')";

 

for ($i = 1; $i < 12; $i++) {

    $strSearchText = substr($nameSearch, 2, $i);

    echo 'Searching for "' . $strSearchText . '"<br/>';

 

    $stmt = $mysqli->prepare($sql);

    if (false === $stmt) {

        die('Query Prep Failed' . $mysqli->error . $mysqli->errno);

    }

    $stmt->bind_param('s', $strSearchText);

    $stmt->execute();

    if (false === $stmt) {

        die('execute() failed: ' . $stmt->error);

    }

    $stmt->store_result();

    $rowCount = $stmt->num_rows;

    if ($rowCount == 4) {

        echo '<b>OK</b><br/>';

    }

    else {

        echo '<b>FAIL!!</b><br/>';

 

 

        $meta = $stmt->result_metadata();

 

        $variables = array();

        while ($field = $meta->fetch_field()) {

            $variables[] = & $data[$field->name];

        }

 

        call_user_func_array(array($stmt, 'bind_result'), $variables);

 

        $intCountResults = 0;

        while ($stmt->fetch()) {

            var_dump($variables);

            $intCountResults++;

        }

    }

}

exit;

 

The references should have been removed (since corrected) - that was from a code example found online. Although it worked, I later learned more about references and that they are no longer needed.

 

I follow everything except the following snippet:

 

for ($i = 1; $i < 12; $i++) {
    $strSearchText = substr($nameSearch, 2, $i);
    echo 'Searching for "' . $strSearchText . '"<br/>';


    $stmt = $mysqli->prepare($sql);
    if (false === $stmt) {
        die('Query Prep Failed' . $mysqli->error . $mysqli->errno);
    }

Please explain the count to 12 and the function of this routine.

[geno11x11]

i'm guessing that your searches that don't match anything have some white-space/non-printing characters in with the data - new-line, tab, space, null.

 

where are you getting/submitting the $nameSearch value from and are you doing any validation/filtering/trimming on it?

The $nameSearch value comes from operator input - an input form. The only validation was to limit the field length. There are no extra characters in $nameSearch, but there is definitely white space within the queried field "Name". Since the legacy query using MySQL Workbench worked without any special processing, I assumed it would work the same with PHP. What do you suggest, and please provide a code example of validation/filtering/trimming.

[vinny42]

I use the loop to run the search query several times, using a different length searchstring every time. If there is a length that doesn't produce 4 results, this routine will find it.

It's a very basic unittest.

[geno11x11]

 

The references should have been removed (since corrected) - that was from a code example found online. Although it worked, I later learned more about references and that they are no longer needed.

 

I follow everything except the following snippet:

 

for ($i = 1; $i < 12; $i++) {
    $strSearchText = substr($nameSearch, 2, $i);
    echo 'Searching for "' . $strSearchText . '"<br/>';


    $stmt = $mysqli->prepare($sql);
    if (false === $stmt) {
        die('Query Prep Failed' . $mysqli->error . $mysqli->errno);
    }

Please explain the count to 12 and the function of this routine.

 

I pasted your routine starting with the for loop to the last curly bracket into my code -- The search string remained empty until I entered three characters or larger (the logical result of the substr($nameSearch, 2, $i) statement). Results "Failed" through all iterations. Then var_dump($variables) displayed a large array of values.

Edited September 2, 2013 by geno11x11

[geno11x11]

Update: I commented out all the prepared statement lines and substituted a legacy query and all search results are working correctly. That leads me to believe the problem may lie in the prepared statement query -- perhaps the CONCAT function is causing it. So I am open to all thoughts and suggestions on how to fix the unpredictable prepared statement query quandry...

[vinny42]

The way MySQLi deals with prepared statements is simply idiotic (I guess that is to be expected when you mix a weird language with a silly database) but did you try the code I used (which I took from the manual) because  I can't reproduce the issue with the code I posted and I am curious if you can.