Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Query using WHERE LIKE returning bizaare results

    Hello all,

    I have the following working query:

    Code:
    $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();
    My problem arose when I searched with the entire word: Using "Flashlight" or "flashlight" (i.e., $nameSearch = "Flashlight"), the query returns zero records. If I query with partial matching characters (wildcards) for "flash", "light" or any matching character such as "F", "f", "L", etc. the desired results are returned.

    But that's not the extent of the problem... Running more searches, I have found that in one case, a 4-character word has some peculiar search characteristics: The value within the database is "gbks": It will appear with a search of any matching single letter, and a 2-character ks, but no other 2, 3, or 4 character combination works.

    In another search, an 8-character value can be searched as far as 7-characters before it fails on the 8th character, the full word. As expected, the more characters used in the query, the narrower the filter so fewer other records appear.

    So the results seem inconsistent at best and perhaps random at worst. I have checked the data being searched. The fields are all strings, the length is proper, and there are no hidden or corrupt characters that I can find. I also entered fresh data to run the query and got the same weird results.

    However, I have successfully run the queries in MySQL workbench without prepared statements. In that utility, all variations of the searches work as they should. I presume that means there is something wrong with my PHP code. MySQL and PHP documentation say the query should work...

    So what is wrong? The query, the code, MySQL or PHP settings, or perhaps Apache2?
    Last edited by geno11x11; 09-02-2013 at 05:28 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Well, MySQLWorkbench has ZERO special properties, meaning that anything it can do you SHOULD be able to do with PHP.

    So my first quess would be that the prepared statement has a problem.

    Easy to test: Don't use a prepared statement.
    Code:
    $library->connectDB($mysqli,$stmt);  
    if ($mysqli->connect_errno) {echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}
    
    $sql="SELECT * FROM Table WHERE Name LIKE '%$nameSearch%')";
    
    $mysqli->query($sql) or die($mysqli->error);
    
    etc. ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Correct again, Old Pedant.....

    I substituted a legacy query for the prepared statement query and it worked perfectly. So that has certainly narrowed down the scope of the problem.

    I now suspect the CONCAT() function. But I have found numerous forum examples of it that reportedly work properly, so why the fail in my project?

    I tried the textbook version of:

    Code:
    $sql="SELECT * FROM Table WHERE Name LIKE ?";
    and

    Code:
    $sql="SELECT * FROM Table WHERE Name LIKE '%?%'";

    They return zero records, while the CONCAT() version does, albeit with unreliable results.

    Suggestions?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    One thing to try, in case the prepared statement is somehow interfering with the CONCAT:

    Use a Stored Procedure.
    Code:
    delimiter //
    
    CREATE PROCEDURE getStuffFromTable( search VARCHAR(100) )
    BEGIN
        SELECT * FROM table WHERE name LIKE CONCAT('%',search,'%');
    END
    //
    
    delimiter ;
    And then use $sql = "CALL getStuffFromTable(?)";
    and use that with a prepared statement.

    But quite frankly, if you only have one parameter, then the heck with the prepared statement. Use real_escape_string and be happy.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You should know that I don't use PHP.

    But I can read docs.

    And according to the docs, you are doing this wrong:
    Code:
    $sql="SELECT * FROM Table WHERE Name LIKE CONCAT('%',?,'%')"
    $stmt = $mysqli->prepare($sql); 
    $stmt->bind_param('s',$nameSearch);
    http://php.net/manual/en/pdo.prepared-statements.php

    Those docs say you should either do
    Code:
    $sql="SELECT * FROM Table WHERE Name LIKE CONCAT('%',?,'%')"
    $stmt = $mysqli->prepare($sql); 
    $stmt->bind_param(1,$nameSearch);
    or use names and do
    Code:
    $sql="SELECT * FROM Table WHERE Name LIKE CONCAT('%',:search,'%')"
    $stmt = $mysqli->prepare($sql); 
    $stmt->bind_param(':search',$nameSearch);
    I don't know where you got the idea to use a ? and then bind to 's'. I don't see it in those docs.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Feb 2013
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Old Pedant,

    Reviewing your link, it appears you were looking at an INSERT query which is a very different animal. The SELECT query uses a different format as below, from the online PHP manual: http://php.net/manual/en/mysqli-stmt.bind-param.php


    A string that contains one or more characters which specify the types for the corresponding bind variables:

    Type specification chars
    Character Description
    i corresponding variable has type integer
    d corresponding variable has type double
    s corresponding variable has type string
    b corresponding variable is a blob and will be sent in packets



    I agree it is tempting to chuck the prepared statements (what migraines they have caused!) but this application must be secure and I am the stubborn sort... I want to understand the problem, it's solution, and then document it all in my personal library for the next time I face it and my senior cranium fails to remember the fix. I will try the stored procedure suggestion tomorrow. Thanks for your time and attention; for not being a PHP programmer, you have done a remarkable job.

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    From the link in Old Pedants post:

    Example #6 Invalid use of placeholder

    <?php
    $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
    $stmt->execute(array($_GET['name']));

    // placeholder must be used in the place of the whole value
    $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
    $stmt->execute(array("%$_GET[name]%"));
    ?>

    So in your case it should be:
    $sql="SELECT * FROM Table WHERE Name LIKE CONCAT(?)"
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('s', '%'.$nameSearch.'%'); // don't know exact php syntax here
    I am the luckiest man in the world

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,009
    Thanks
    2
    Thanked 312 Times in 304 Posts
    Starting with post #5 in this thread, that's the PDO documentation, not the mysqli documentation.

    However, why don't you use PDO instead of mysqli. PDO is much easier to dynamically build an arbitrary prepared query, which would let you write a db class that you can call any time you need to run a query, without duplicating all the nuts-and-bolts code each time you put a query into your code.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •