SQL Code With LIKE WildCard Doesn't Work Properly

Question

This is my code. This SQL Query works properly in phpmyadmin, it returns 2 hotels as I wanted, but in the php code, it returns all of my hotels. Why is this not working?

    $search_text = "%".  $search_text ."%";

    $query = "SELECT name,country,province,town FROM Hotel WHERE country LIKE ? OR province LIKE ? OR town LIKE ? OR name LIKE ? ORDER BY name DESC";

    $stmt = Database::$connection->prepare($query);

    $stmt -> bind_param('ssss',$search_text , $search_text, $search_text, $search_text);
    $stmt -> execute();
    $result = $stmt -> get_result();

    $searched = null;

    $count = 0;
    while($row = $result -> fetch_array(MYSQLI_NUM)){
        $searched[$count][0] = $row[0];
        $searched[$count][1] = $row[1];
        $searched[$count][2] = $row[2];
        $searched[$count][3] = $row[3];
        $count++;
    }
    $stmt-> close();
    return $searched;

what's this first parameter 'ssss' about? and why 5 params but only 4 placeholders in the query? — RobP, Dec 09 '14 at 10:55
@RobP http://php.net/manual/en/mysqli-stmt.bind-param.php it represents 4 strings. — DickieBoy, Dec 09 '14 at 10:56
@RobP I count 4 params? ssss is 'string,string,string,string' as per the [Docs](http://php.net/manual/en/mysqli-stmt.bind-param.php) and [Convention](http://stackoverflow.com/questions/18426172/what-does-bind-param-accomplish) — StuartLC, Dec 09 '14 at 10:59
ah, indeed. I'm so used to PDO I'd quite forgotten the formatter part in mysqli. Thx. — RobP, Dec 09 '14 at 11:00
As to the actual problem, you might first check the value of $search_text, which must be set somewhere prior to this code. If that were not set properly it would explain getting all your rows. — RobP, Dec 09 '14 at 11:01
So thanks to RobP, I've found the error. SearchText was empty, because I couldn't set it properly. Thank you man. — Nerzid, Dec 09 '14 at 11:29

score 0 · Answer 1 · answered Dec 09 '14 at 11:15

0

Only problem that I see is that you've got whitespace in your string. Try removing it.

$search_text = "%".$search_text."%";

It may also be because you need to include apostrophes.

$search_text = '"%'.$search_text.'%"';

answered Dec 09 '14 at 11:15

EternalHour

8,308
6
38
57

Whitespace around concatenation `.` characters does not matter. `$str1 . $str2` equals `$str.$str2`. – ojrask Dec 09 '14 at 11:23
Tried first one. Didn't work. Tried second one. It return no results now. – Nerzid Dec 09 '14 at 11:24

score 0 · Accepted Answer · answered Dec 09 '14 at 11:20

0

check your code that their set the value of $search_text it might be blank so it returns all rows .

answered Dec 09 '14 at 11:20

Ajit Kumar

345
3
9

SQL Code With LIKE WildCard Doesn't Work Properly

2 Answers2