loading...
Cover image for Add google-like search query to your website or Database

Add google-like search query to your website or Database

trinly01 profile image Trinmar Boado Updated on ・3 min read

Without a fancy introduction, let's get straight to the point

How to implement google-like search query to your existing website or database

Features we want to mimic

  1. Search keywords from your database
  2. Support misspelled words
  3. Auto Suggest keywords (Did you mean...)

Alt Text

The code we will be producing here is backend agnostic.

Meaning, we can use any server-side programming language. But on this tutorial, we will use PHP and MySQL to simulate the features we want to imitate.

Todos

  • [x] Create a search bar and a list
  • [x] Suggest kewords automatically
  • [x] Connect to a database
  • [x] Fetch the data
  • [x] Display the result

The search bar and result list

we don't need complicated and fancy CSS designs so we will just use plain HTML

<!-- index.html -->
<input  id="search"  type="text"  placeholder="Search">
<button  id="btnSearch">Search</button>
<br/>
<span  id="didyoumean"></span>
<ul  id="result">
    <li>1</li>
    <li>2</li>
    <li>3</li>
    <li>4</li>
    <li>5</li>
</ul>

Auto-Suggest Keywords

we need to install a library called puzzy-search thru NPM or CDN

npm install puzzy-search

or include this inside your <head> tag

<script  src="https://unpkg.com/puzzy-search/dist/index.js"></script>

If you chose CDN, a global variable named puzzySearch will be available. In my case, I used NPM

inside your script prepare the elements you want to control

// javascript

const { search, suggest, regex } = require('puzzy-search')

let  str = document.querySelector('#search')
let  btnSearch = document.querySelector('#btnSearch')
let  suggestion = document.querySelector('#didyoumean')
let  resultList = document.querySelector('#result')

str.addEventListener('input', () => {
    if (str.value)
        suggestion.innerHTML = `Did you mean: ${suggest(str.value)}`
    else
        suggestion.innerHTML = ''
})

the above code listens to input events from search input and changes the content of suggestion element (#didyoumean) returned by the suggest(str.value) function.

function params return type Definition
search (keywords, sentence) Boolean determines whether sentence includes the keywords among its entries, returning true or false as appropriate supporting misspelled words using a stemming algorithm
suggest (keywords) String returns suggested correction for the misspelled words
regex (keywords) RegEx returns a RegEx String that can be tested to any String

Connect to a database

Create a Web API for our database using the code below with your credentials (hostname, username, password, dbname)

// api.php

header("Access-Control-Allow-Origin: *");
header('Content-Type: application/json');
// echo json_encode($_GET['regex']);
$hostname='localhost';
$username='root';
$password='';

try {
    $dbh = new  PDO("mysql:host=$hostname;dbname=ulimslab",$username,$password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line
    $sql = "SELECT  *  FROM `sample` WHERE  CONCAT(`sampleName`, ' ', `description`, ' ', `remarks`) REGEXP ? LIMIT  10";

    $stmt = $dbh->prepare($sql);
    $stmt->execute([str_replace('/' , '', $_GET["regex"])]);
    $result = $stmt->fetchAll();

    echo  json_encode($result);

    $dbh = null;

} catch(PDOException  $e) {
    echo  json_encode($e->getMessage());
}

this code will receive a regex from the client, query the database and returns the result.

Fetch the data and Display the result

going back to our client. we need to trigger an HTTP request to fetch the data from the server when the btnSearch was clicked.

// javascript

btnSearch.addEventListener('click', () => {
    let  regex = regex(str.value)
    fetch('api.php?regex=' + regex)
        .then(res  =>  res.json()).then(data  => {
            result.innerHTML = data.map(r  =>  `<li>${r.sampleName}</li>`).join('')
        })
        .catch(e  =>  console.log(e))
})

the code above listens to the btnSearch click event and generates the Regular Expression based on regex(str.value) as the user's input then trigger a HTTP Get Method request with regex as query param and change the content of resultList.innerHtml

TäˈDä!!! Voilà! There it is; made in 🇵🇭

Alt Text

Summary

That was quite a lot in one article. Guess what though, there's a lot more to learn.

Let's recap on our learnings, we learned:

  • [x] How to create a search bar and a list using simple html
  • [x] Explored puzzy-search library capabilities to search, suggest` and generate regex
  • [x] Create a RESTful Web API in JSON format connected to the database
  • [x] Consume data from backend thru HTTP Get Method Request
  • [x] Display the result from the API

That was a lot but hopefully, you are now so comfortable with puzzy-search that you see its value and can find out the rest you need in the docs.

Posted on by:

trinly01 profile

Trinmar Boado

@trinly01

An ICT Advocate, Trainer and Consultant. He is giving talks and facilitating Hands-on Trainings to schools and other IT

Discussion

markdown guide
 

That's great! But do not do this in production 😬
You should always sanitize the user input and never ever use a query param in your SQL, as that's injection 101.

 

What is not safe on the code?
the $_GET['regex'] was bound thru $stmt->execute()

There's no difference in safety between passing all the parameters as an array to execute, or using bindParam or bindValue.

Tried this simple injection and it doesn't work
api.php?regex='or''='

 

nice, but some things are missing - no db structure, it would be also nice if you put the source code files under the document, or at least an abstract of complete code.