ajax user suggest

intermediate ajax, css, js, mysql, php, jquery, knockout 1038 misterhaan

it’s been a few years since my ajax suggest guide, and since i’ve rewritten my ajax user suggestion code i figured an updated guide is in order. this guide covers using ajax with jquery and knockout to suggest users when typing in a text input field and support selection with the keyboard or mouse. familiarity with php, mysql, javascript, jquery, knockout, html, and css will make this guide more useful. specific css examples are not given since the best look will depend on the website using it. differences from the previous guide are including jquery and knockout, showing avatar and friend status with suggested usernames, and a single mysql query instead of two that get combined in php.

  1. find matching users
  2. define a view model
  3. create the search field
  4. support mouse selection
  5. support keyboard selection
  6. all 5 pages

find matching users

i’ll start with the php and mysql that generates a list of suggestions. this piece can be tested on its own by putting the request in the browser’s address bar, so i can see i got it right before having to write the other pieces.

for each user in the list i’ll want their id, avatar url, username, display name, and whether the user asking for the list considers them a friend. it’s possible to send this data in plain text or xml as well as json, but json fits into client-side scripts best. i’ll have an array of matching users as objects with all five of those items as properties.

since i have both username (used for the profile url) and display name, i want to send back all users who contain the search text in either username or display name. leave out the requesting user since it probably won’t make sense to let users select themselves. i’ll limit the list to the top eight; if whoever you’re looking for isn’t in there then get more specific to shorten the list. some of those matches are more likely to be the one that will get chosen though, so those should be listed first. users who the requesting user considers friends come first, then users whose username or display name matches the search text exactly, then username or display name starts with the search text, then contains the search text, and finally alphabetical order. most of those sort criteria actually look at the username and/or displayname columns, but mysql ignores all order by criteria that involve columns already used in an earlier criteria. i got around that by actually selecting the criteria and giving them names, then ordering by those names. here’s what that query looks like:

select u.id, coalesce(nullif(u.avatar, ''), 'DEFAULT_AVATAR') as avatar, u.displayname, u.username, f.fan as isfriend, u.username='MATCH' or u.displayname='MATCH' as exact, u.username like 'MATCH%' or u.displayname like 'MATCH%' as start from users as u left join users_friends as f on f.fan='USER_ID' and f.friend=u.id where u.id!='USER_ID' and (u.username like '%MATCH%' or u.displayname like '%MATCH%') order by isfriend desc, exact desc, start desc, coalesce(nullif(u.displayname, ''), u.username) limit 8

the all-caps stuff gets set by php code. also, since usernames can contain underscore characters and display names can contain percent signs but the sql like operator treats them as wildcards, i had to escape them with backslashes to use them as literal characters. the coalesce for the avatar uses a constant i have defined for the path to the default avatar if there’s no avatar defined for the user. the isfriend column will either be null (not a friend) or the requesting user’s id based on whether there’s an entry in the friends table. then i set up the columns for use in order by, which are all 1 or 0 for true or false. there’s a quick left join looking for the friends table row saying the signed-in user marked this user as a friend, then the order by clause which is mostly desc because i want ones that match first. finally i sort alphabetically by displayname, or username if there’s no displayname, because that’s how users are displayed.

the php code needs to make sure the search text is present, put it into the query and run it, then copy the query results into an array and output it in json format. here’s that section of code:

if(isset($_GET['ajax'])) {
  $ajax = new t7ajax();
  switch($_GET['ajax']) {
    case 'suggest':
      if(isset($_GET['match']) && strlen($_GET['match']) >= 3) {
        $matchsql = $db->escape_string(trim($_GET['match']));
        $matchlike = $db->escape_string(str_replace(['_', '%'], ['\\_', '\\%'], trim($_GET['match'])));
        // some columns aren't needed except to make the order by use unique columns
        if($us = $db->query('select u.id, coalesce(nullif(u.avatar, \'\'), \'' . t7user::DEFAULT_AVATAR . '\') as avatar, u.displayname, u.username, f.fan as isfriend, u.username=\'' . $matchsql . '\' or u.displayname=\'' . $matchsql . '\' as exact, u.username like \'' . $matchlike . '%\' or u.displayname like \'' . $matchlike . '%\' as start from users as u left join users_friends as f on f.fan=\'' . +$user->ID . '\' and f.friend=u.id where u.id!=\'' . +$user->ID . '\' and (u.username like \'%' . $matchlike . '%\' or u.displayname like \'%' . $matchlike . '%\') order by isfriend desc, exact desc, start desc, coalesce(nullif(u.displayname, \'\'), u.username) limit 8')) {
          $ajax->Data->users = [];
          while($u = $us->fetch_object()) {
            // remove ordering columns
            unset($u->exact, $u->start);
            $ajax->Data->users[] = $u;
          }
        } else
          $ajax->Fail('error looking for user suggestions.');
      } else
        $ajax->Fail('at least 3 characters are required to suggest users.');
      break;
  }
  $ajax->Send();
  die;
}

there are other cases in the switch statement but they’re for other ajax calls. track7 uses a class to generate ajax responses (which defaults to json) named t7ajax. feel free to look at its code. the essentials for json output from php are sending the header Content-Type: application/json and formatting a php data object into json with json_encode(). t7ajax does those with its Send function at the end, encoding its Data property.

with that code in place and access to the t7ajax class and the mysqli connection object $db it’s ready to be tested in a browser. visit the url to the script and pass ajax=suggest and at least three characters for match in the query string. for example http://www.track7.org/user/?ajax=suggest&match=jam currently finds two users who match “jam.” most likely you’ll just see raw json code like this (t7ajax always includes a true or false fail value):

{"fail":false,"users":[{"id":"9","avatar":"\/user\/avatar\/jameaghe.jpg","displayname":"","username":"jameaghe","isfriend":null},{"id":"21","avatar":"\/images\/user.jpg","displayname":"","username":"jamiemae","isfriend":null}]}
  1. find matching users
  2. define a view model
  3. create the search field
  4. support mouse selection
  5. support keyboard selection
  6. all 5 pages

how was it?

comments

there are no comments on this guide so far. you could be the first!

*