0

So my problem is simple. I'm a PHP beginner (noob programmer in general, started 3 months ago) and I've been following some tutorials on how to build a "search suggestion" system, that checks in the database if what you're typing is like anything in it and displays the results real time with AJAX.

It is working partially. When I type in a full username, for example, it displays the result. But not when I type half of it, or the first letter.

<?php

    require 'connect.php';

    if (isset($_GET['searchText'])) {
        $searchText = $_GET['searchText'];
    }

    if($query = $db->prepare("SELECT user_name FROM users WHERE user_name LIKE ?")) {
        $query->bind_param('s', $searchText);
        $query->execute();

        $query->bind_result($searchTextResult);

        while ($query->fetch()) {
            echo $searchTextResult;
        }
    }

?>

Also, since I'm a total noob I'd love to have any suggestions on the code just to know if I'm employing good practice in general. I heard binding and mysqli are recommended, so I'm trying to stick to them and I rarely get any criticism at all.

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
SexualPotatoes
  • 78
  • 10
  • You need to use the `%` wildcard. http://stackoverflow.com/questions/1352002/using-wildcards-in-prepared-statement-mysqli – Digital Chris Apr 16 '15 at 15:42
  • For the last part of your question, you can ask on [CodeReview ](http://codereview.stackexchange.com/) which is a stackexchange site for that purpose. – FabienAndre Apr 16 '15 at 15:43
  • 1
    Make `ajax` call to your server file to get full name using `onkeyup` event handler, this will be called whenever you key up your keyboard. Try to google more about it, see simple demonstration http://stackoverflow.com/questions/10968419/ajax-call-on-keyup – A l w a y s S u n n y Apr 16 '15 at 15:43
  • You should call php and check results on keyup event - could you show javascript/ajax part of your script? – sinisake Apr 16 '15 at 15:43
  • To follow up from @DigitalChris comment - you'll need to append the wildcard to your `$searchText` variable like : `$query->bind_param('s', $searchText . '%');` ... but you'd need to use `bind_value` rather than param in that case, thinking about it... as `bind_param` expects the variable to be passed by reference... or you could add it when you define the var : `$searchText = $_GET['searchText'] . '%';` – CD001 Apr 16 '15 at 15:44
  • Thank everyone! Adding "%" to the var definition worked, CD001. Fabien, I didn't know about that website, I bookmarked it! Nevermind and Sunny, thanks for the tips too :) – SexualPotatoes Apr 16 '15 at 15:58

2 Answers2

0

The sql query which search a parameter partially would be like this:

"select user_name FROM users WHERE user_name LIKE CONCAT('%',?,'%')"
Ali Sheikhpour
  • 10,475
  • 5
  • 41
  • 82
  • Not only won't this work, he's already using bind params, which is superior to inline. – Digital Chris Apr 16 '15 at 15:50
  • You know, I don't even know if that would work with a bound parameter... it might but it's just a bit too weird for me... I'd just concat in the wildcards at the application level. I'll remove the downvote though since I'm not about to test this any time soon. – CD001 Apr 16 '15 at 15:56
0

Thanks everyone for all the answers. This was my first time on StackOverflow and I loved it. I hope I can return the favor someday.

All I had to do was add "%" to the $searchText var right in the beginning and everything ran smoothly.

The correct code is now as follows:

<?php

require 'connect.php';

if (isset($_GET['searchText'])) {
    $searchText = $_GET['searchText'] . '%';
}

if($query = $db->prepare("SELECT user_name FROM users WHERE user_name LIKE ?")) {
    $query->bind_param('s', $searchText );
    $query->execute();

    $query->bind_result($searchTextResult);

    while ($query->fetch()) {
        echo $searchTextResult . "<br />";
    }
}

?>

SexualPotatoes
  • 78
  • 10