jQuery PHP MySQL AJAX Search

jQuery PHP MySQL AJAX Search

Add Comment , Edit

Today we will make an AJAX search using jQuery, PHP and MySQL. This will be able to detect what you type in searchbar. Let's start coding.

Files Needed

  • index.html
  • data-fetcher.php
  • main.js
  • style.css
Create database called ajaxsearch. We will just create a simple table structure.
users table

Let's create table called Users and paste the code below:

CREATE TABLE Users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

INSERT INTO Users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Fred', 'Holister', 'fred@example.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Mary', 'Molly', 'mary@example.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Alvin', 'Fabian', 'alvin@gmail.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Ben', 'Curry', 'bencurry@yahoo.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Carl Lister', 'Abia', 'carllister@example.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Chris', 'Forbs', 'cforbs@hotmail.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Danny', 'Web', 'dan@example.com');
Now that we already have the database and sample data, let's proceed to index.html.

<html>
<head>
    <title>jQuery PHP AJAX Search</title>
    <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
    <div class="container">
        <h1>jQuery PHP AJAX Search</h1>
        <input type="text" id="search" placeholder="Search.." autocomplete="off">
        <div id="result">
            <p class="no-result">-- No result yet --</p>
        </div>
    </div>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
    <script type="text/javascript" src="main.js"></script>
</body>
</html>
Next, let's create main.js to handle ajax request.

$(document).ready(function() {
    $('#search').keyup(function() {
        $.ajax({
            url: 'data-fetcher.php',
            data: { 'search': $(this).val() },
            type: 'post',
            success: function(result) {
                $('#result').html(result);
            }
        })
    });
});
This is how it looks like.
initial view

Let's create the data-fetcher.php to handle data query to the database.

<?php
if (isset($_POST['search'])) {
 
 $search = $_POST['search'];

 // configurations
 $servername = "localhost";
 $username = "root";
 $password = "";
 $dbname = "ajaxsearch";

 // Create connection
 $conn = new mysqli($servername, $username, $password, $dbname);
 
 // Check connection
 if ($conn->connect_error)
 {
     die("Connection failed: " . $conn->connect_error);
 }
 
 if (empty($search))
 {
  echo "<p class='no-result'>-- No result found --</p>";
 }
 else
 {
  $sql = sprintf("SELECT * FROM Users WHERE firstname LIKE '%s%%' OR lastname LIKE '%s%%' OR email LIKE '%s%%'",mysql_real_escape_string($search), mysql_real_escape_string($search), mysql_real_escape_string($search));
  $result = $conn->query($sql);

  if ($result->num_rows > 0)
  {
      while($row = $result->fetch_assoc())
      {
          echo "<p>".$row['firstname']. " " . $row['lastname'] . "</p>";
      }
  }
  else
  {
      echo "<p class='no-result'>-- No result found --</p>";
  }
 }

 $conn->close(); 
}
?>
Lastly, let's make a simple styling. Let's create style.css

/* http://meyerweb.com/eric/tools/css/reset/ 
   v2.0 | 20110126
   License: none (public domain)
*/

html, body, div, span, applet, object, iframe,
h1, h2, h3, h4, h5, h6, p, blockquote, pre,
a, abbr, acronym, address, big, cite, code,
del, dfn, em, img, ins, kbd, q, s, samp,
small, strike, strong, sub, sup, tt, var,
b, u, i, center,
dl, dt, dd, ol, ul, li,
fieldset, form, label, legend,
table, caption, tbody, tfoot, thead, tr, th, td,
article, aside, canvas, details, embed, 
figure, figcaption, footer, header, hgroup, 
menu, nav, output, ruby, section, summary,
time, mark, audio, video {
 margin: 0;
 padding: 0;
 border: 0;
 font-size: 100%;
 font: inherit;
 vertical-align: baseline;
}
/* HTML5 display-role reset for older browsers */
article, aside, details, figcaption, figure, 
footer, header, hgroup, menu, nav, section {
 display: block;
}
body {
 line-height: 1;
}
ol, ul {
 list-style: none;
}
blockquote, q {
 quotes: none;
}
blockquote:before, blockquote:after,
q:before, q:after {
 content: '';
 content: none;
}
table {
 border-collapse: collapse;
 border-spacing: 0;
}
/* end css reset */
body {
 background-color: #f5f5f5;
 font-family: Verdana,sans-serif;
 min-height: 100%;
}
h1 {
 font-size: 36px;
}
.container {
 background-color: #fff;
    height: 100%;
    margin-left: auto;
    margin-right: auto;
    padding: 30px 15px;
    width: 970px;
}

#search {
    border: 1px solid #ccc;
    height: 42px;
    padding: 8px 10px;
    width: 100%;
    margin-top: 25px;
}

#result {
 border-top: 1px solid #eee;
    margin-top: 15px;
    min-height: 50px;
    padding: 15px;
}

#result p {
 color: #888;
    padding: 8px 0;
}

#result .no-result {
 color: red;
 text-align: center;
}
Now, when you start typing it will automatically search users for you.
search result view

search result
It will also display a message when the query is not found.
result not found
Add caption

That's it! You now have a fully functional ajax search.
SHARE
    Blogger Comment
    Facebook Comment

0 comments :

Post a Comment

Subscribe to: Post Comments ( Atom )