Martin  Dreech

Martin Dreech

1576035038

Autocomplete Textbox with Javascript, PHP and MySQL

Welcome to a step-by-step tutorial on how to create an autocomplete textbox with PHP, Javascript, and MySQL. In this guide, we will walk through how to add autocomplete to a textbox, with the help of jQuery and also an alternative that works with just vanilla Javascript. Read on to find out!

Quick notes

  • Download and unzip the file into a folder.
  • Create a dummy database and import 1-users.sql.
  • Change the database settings in 2-search.php to your own.
  • Access 3a-jquery.html in your browser for the jQuery version.
  • Access 3b-vanilla.html in your browser for the vanilla Javascript version.

1. Step 1: The Database

For the purpose of demonstration in this tutorial, I have included a simple user table and some sample data. You can skip this part if you already have an established database and want to work on that instead.

The User table and Sample data

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(24) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Jazz Doe', 'jazz@doe.com', '56973317'),
(2, 'Jane Doe', 'jane@doe.com', '40378541'),
(3, 'Rusty Terry', 'rusty@terry.com', '34614899'),
(4, 'Peers Sera', 'peers@sera.com', '13014383'),
(5, 'Jaslyn Keely', 'jaslyn@keely.com', '52154191'),
(6, 'Richard Breann', 'richard@breann.com', '58765281'),
(7, 'Imogene Thad', 'imogene@thad.com', '11753471'),
(8, 'Tillie Sharalyn', 'tillie@sharalyn.com', '33989432'),
(9, 'Drake Adelaide', 'drake@adelaide.com', '56539890'),
(10, 'Coby Kelleigh', 'coby@kelleigh.com', '83788228'),
(11, 'Frank Doe', 'frank@doe.com', '87074336'),
(12, 'John Doe', 'john@doe.com', '55163490'),
(13, 'Jan Doe', 'jan@doe.com', '45310572'),
(14, 'Joe Doe', 'joe@doe.com', '48938630'),
(15, 'Joanne Doe', 'joanne@doe.com', '44101677'),
(16, 'Johan Doe', 'johan@doe.com', '28278966'),
(17, 'Charles Doe', 'charles@doe.com', '45800899'),
(18, 'Charlie Doe', 'charlie@doe.com', '32644564'),
(19, 'Apple Doe', 'apple@doe.com', '76438908'),
(20, 'Orange Doe', 'orange@doe.com', '47648224');

This is nothing but a very simple users table with 4 fields – id is the primary key, name, email, and phone.

Index your Searchable Fields

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `name` (`name`);

ALTER TABLE `users`
MODIFY `id` int(21) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

One small suggestion for you guys who already have an existing project – Index the fields that you want to search. Just why do we need to index the fields? Imagine a library that does not have a catalog system… Searching for a book that you want will mean having to look through each and every book in the library until you find it.

It is the same here in a database. If you don’t index the field, the database will have to look through each and every entry to find the ones that you want. Yes, while indexing does take up a little more disk space, but it will also speed up your searches and consume fewer system resources in the long run. Especially if you have a massive database with thousands of entries.

2. Step 2: Server - side script

Now that we have established the dummy database, it is time to work on a server-side script that will deal with searching the database and output the results.

Server side search

<?php
// YOU MIGHT WANT TO ADD SOME SECURITY ON THIS PAGE
// E.G. CHECK IF VALID USER LOGIN
// if (isset($_SESSION['admin'])) { ... DO SEARCH AS BELOW ... }
 
// (1) CONNECT TO DATABASE
$host = '127.0.0.1';
$dbname = 'test';
$user = 'root';
$password = '';
$charset = 'utf8';
$pdo = new PDO(
	"mysql:host=$host;dbname=$dbname;charset=$charset", $user, $password, [
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
		PDO::ATTR_EMULATE_PREPARES => false,
	]
);

// (2) SEARCHING FOR?
$data = [];
switch ($_POST['type']) {
  // (2A) INVALID SEARCH TYPE
  default :
    break;
 
  // (2B) SEARCH FOR USER
  case "user":
    // You might want to limit number of results on massive databases
    // SELECT * FROM XYZ WHERE `FIELD` LIKE ? LIMIT 20
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[] = $row['name'];
    }
    break;
 
  // (2C) SEARCH FOR EMAIL
  case "email":
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `email` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[] = $row['email'];
    }
    break;
}

// (3) RETURN RESULT
$pdo = null;
echo json_encode($data);
?>
  • There are 2 things to POST to this script.
    • First, which type of data you want – user or email?
    • A search term should also be sent to this script.
  • Very straightforward. The script first opens up a database connection – Remember to change the database settings to your own.
  • Then runs a search query on the user table, depending on what you want to look for.
  • Finally, outputs the search results (if any) using JSON encoding.

The jQuery autocomplete plugin uses JSON, but if you want to work with XML (or some other format) in your own project, please feel free to modify the code to fit your own needs.

3. Step 3: Jquery Autocomplete

The final piece of the puzzle is the client-side HTML and Javascript, which I will give 2 alternatives for. This first alternative includes the use of jQuery UI, and it is as simple as attaching the autocomplete to an element.

The Script:

<!DOCTYPE html>
<html>
  <head>
    <title>
      jQuery Autocomplete Demo
    </title>
    <!-- (1) YOU NEED TO LOAD BOTH JQUERY AND JQUERY UI -->
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>ry-ui.min.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
      $(function () {
        // NAME AUTO-COMPLETE
        $('#user-name').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "user"
              },
              success: response,
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          }
        });

        // EMAIL AUTO-COMPLETE
        $('#user-email').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "email"
              },
              success: response,
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          }
        });
      });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
  </body>
</html>

The Explannation

Not much rocket science is required when it comes to jQuery:

  • You need to load both the main jQuery library and jQuery UI.
  • Just attach autocomplete to the input fields.

Please do also check out the jQuery autocomplete documentation on the settings that you can use.

4. Step 4: Vanila JavaScript

For you guys who do not wish to use jQuery, or think that it adds too much to the loading bloat, here is the other alternative written in pure Javascript.

The HTML:

<!DOCTYPE html>
<html>
  <head>
    <title>
      Vanilla Javascript Autocomplete Demo
    </title>
    <!-- (1) AUTOCOMPLETE SCRIPTS -->
    <link href="3b-autocomplete.css" rel="stylesheet">
    <script src="3b-autocomplete.js"></script>
 
    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
    window.addEventListener("load", function(){
      // NAME AUTO-COMPLETE
      suggest.attach({
        // Minimum required
        target : "user-name",
        url : "2-search.php",
        data : { type: "user" }
      });
 
      // EMAIL AUTO-COMPLETE
      suggest.attach({
        // Minimum required
        target : "user-email",
        url : "2-search.php",
        data : { type: "email" },
        // Optional
        delay : 200,
        min : 3
      });
    });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
  </body>
</html>
  • Text fields in HTML as usual.
  • We wait for the page to be fully loaded window.addEventListener("load") before attaching the autocomplete to the fields.
  • Use suggest.attach(OPTIONS) to attach the auto-suggest to the required fields.

The CSS

div.acWrap {
  position: relative;
}
div.acBox {
  position: absolute;
  top: 25px;
  left: 0;
  padding: 5px;
  z-index: 999;
  background: #fff;
  border: 1px solid #000;
  display: none;
}
div.acBox div:hover {
  cursor: pointer;
  background: #fea;
}

Now, the autocomplete script will “pad” the input field with some HTML `` wrappers, and it will look like this when fully initiated:

<div id="acWrapXXX" class="acWrap">
  <input type="text" id="FOO-BAR"/>
  <div id="acBoxXXX" class="acBox">
    <!-- SUGGESTIONS ARE PUT HERE DYNAMICALLY VIA AJAX -->
    <div>Option A</div>
    <div>Option B</div>
    <div>Option C</div>
  </div>
</div>

So feel free to change the CSS styles to suit your own project.

The JavaScript:

var suggest = {
  instance : {}, // attached instances
  focus : null, // current text field in focus

  attach : function (opt) {
  // suggest.attach () : attach autosuggest to input field
  // opt : options
  //  - target : ID of target input field, required
  //  - url : Target URL to fetch suggestions, required
  //  - delay : Delay before autocomplete fires up, optional, default 500ms
  //  - min : Minimum characters to fire up autocomplete, default 2
  //  - data : Additional data to post to the server
  //  - fetch : Manual fetch draw override
  //  - select : Manual select draw override

    // Create autocomplete wrapper and box
    var id = Object.keys(suggest.instance).length,
        input = document.getElementById(opt.target);
    input.outerHTML = "<div id='acWrap" + id + "' class='acWrap'>" + input.outerHTML + "<div id='acBox" + id + "' class='acBox'></div></div>";

    // Set the HTML references and options
    suggest.instance[opt.target] = {
      input : document.getElementById(opt.target),
      wrap : document.getElementById("acWrap" + id),
      box : document.getElementById("acBox" + id),
      delay : opt.delay ? opt.delay : 500,
      url : opt.url,
      min : opt.min ? opt.min : 2,
      data : opt.data ? opt.data : null,
      fetch : opt.fetch ? opt.fetch : null,
      select : opt.select ? opt.select : null,
      timer : null
    };
 
    // Attach key listener
    suggest.instance[opt.target].input.addEventListener("keyup", function (evt) {
      // Clear old timer
      if (suggest.instance[opt.target].timer != null) {
        window.clearTimeout(suggest.instance[opt.target].timer);
      }

      // Hide and clear old suggestion box
      suggest.instance[opt.target].box.innerHTML = "";
      suggest.instance[opt.target].box.style.display = "none";

      // Create new timer, only if minimum characters
      if (evt.target.value.length >= suggest.instance[opt.target].min) {
        suggest.instance[opt.target].timer = setTimeout(
          function () { suggest.fetch(evt.target.id); },
          suggest.instance[opt.target].delay
        );
      }
    });

    // This is used to hide the suggestion box if the user navigates away
    suggest.instance[opt.target].input.addEventListener("focus", function (evt) {
      if (suggest.focus != null) { suggest.close(null, true); }
      suggest.focus = opt.target;
    });
  },

  fetch : function (id) {
  // suggest.fetch() : AJAX get suggestions and draw
  // id : ID of target input field, automatically passed in by keyup event

    // Init AJAX
    var req = new XMLHttpRequest();
    req.addEventListener("load", function () {
      var data = JSON.parse(this.response);
      if (Object.keys(data).length > 0) {
        if (suggest.instance[id]['fetch']) {
          suggest.instance[id]['fetch'](id, data);
        } else {
          data.forEach(function (el) {
            suggest.instance[id].box.insertAdjacentHTML("beforeend", "<div onclick=\"suggest.select('" + id + "', this);\">" + el + "</div>");
          });
        }
        suggest.instance[id].box.style.display = "block";
        document.addEventListener("click", suggest.close);
      }
    });

    // Data
    var data = new FormData();
    data.append('term', suggest.instance[id].input.value);
    if (suggest.instance[id].data) {
      for (let key in suggest.instance[id].data) {
        data.append(key, suggest.instance[id].data[key]);
      }
    }

    // Send
    req.open("POST", suggest.instance[id].url);
    req.send(data);
  },

  select : function (id, el, more) {
  // suggest.select() : user selects a value from autocomplete
  // id : ID of text box
  // el : Selected element
  // more : Optional, more parameters
 
    if (suggest.instance[id]['select']) {
      suggest.instance[id]['select'](id, el, more);
    } else {
      suggest.instance[id].input.value = el.innerHTML;
      suggest.instance[id].box.innerHTML = "";
      suggest.instance[id].box.style.display = "none";
      document.removeEventListener("click", suggest.close);
    }
  },

  close : function (evt, force) {
  // suggest.close() : close the autocomplete box if the user clicks away from the input field
  // evt : click event
  // force : force close
 
    if (force || event.target.closest(".acWrap") == null) {
      suggest.instance[suggest.focus].box.innerHTML = "";
      suggest.instance[suggest.focus].box.style.display = "none";
      document.removeEventListener("click", suggest.close);
    }
  }
};

The Explannation:

This may look like a handful at first, but it is a stepped-down version of what jQuery has. Rather than repeating line-by-line (trace through the code on your own if you want)

5. Step 5: Jquery Complete Multiple

The autocomplete is done, and here is an extra section for you guys who want to populate many fields with one selection. For example, select the user name, and automatically fill up the email plus phone number.

Sever-Side Addition

Firstly, we are going to add an extra handler in the server-side search script:

<?php
switch ($_POST['type']) {
  // (X) SEARCH FOR USER WITH ALL INFORMATION
  // JQUERY VERSION
  case "user-all":
    // Data yoga
    $data = [
      "display" => [],
      "details" => []
    ];

    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data["display"][] = [
        "label" => $row['name'],
        "value" => $row['id']
      ];
      $data["details"][$row['id']] = [
        "email" => $row['email'],
        "phone" => $row['phone']
      ];
    }
    break;
}

Now, this is pretty much the same “search the database by user” as above, but we are returning a lot more data this time around. Take note of how the $data response is formed:

  • $data["display"] will hold a list of the user id and name.
  • $data["details"] will hold a list of the user id, email and phone.
  • Yep, I would have just packed everything flat inside $data, but jQuery works otherwise.
  • In jQuery autocomplete, it takes an array of label and value to generate the autocomplete list – This is why we have $data["display"], with the user name as the label, and id as the value.
  • Upon selection, as you can guess, we will then use the selected value (id) to tie to $data["details"] – Use it to populate the rest of the fields.

The HTML

<!DOCTYPE html>
<html>
  <head>
    <title>
      jQuery Autocomplete Demo
    </title>

    <!-- (1) YOU NEED TO LOAD BOTH JQUERY AND JQUERY UI -->
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
      // GLOBAL TEMP VAR TO HOLD AUTOCOMPLETE DATA
      var actemp = null;

      // AUTO-COMPLETE SETUP
      $(function () {
        $('#user-name').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "user-all"
              },
              success: function(data) {
                // Details to global temp
                actemp = data.details;

                // Return display
                response(data.display);
              },
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          },
          select : function(event, ui){
            // Fill up other fields
            $('#user-name').val(ui.item.label);
            $('#user-email').val(actemp[ui.item.value]['email']);
            $('#user-tel').val(actemp[ui.item.value]['phone']);
            return false;
          }
        });
      });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
    <br/><br/>
    Tel: <input type="email" id="user-tel"/>
  </body>
</html>

A little bit confusing in this one, but it is as described above. We use data["display"] to generate the selection list, use the selected value to tie back to data["details"], then it is possible to auto-complete the rest of the other fields.

6. Step 6: Vanilla Complete Multiple

Finally, the “auto-complete many” vanilla Javascript version.

Sever-Side Addition

Just like the jQuery version, we are going to add another request to the server-side script. But this time, it will be a lot more straightforward for the vanilla version.

<?php
switch ($_POST['type']) {
  // (X) SEARCH FOR USER WITH ALL INFORMATION
  // VANILLA VERSION
  case "user-a":
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[$row["id"]] = $row;
    }
    break;
}

Override Draw

Finally, all we have to do is to override the draw and select to pick up the additional data.

<!DOCTYPE html>
<html>
  <head>
    <title>
      Vanilla Javascript Autocomplete Demo
    </title>

    <!-- (1) AUTOCOMPLETE SCRIPTS -->
    <link href="3b-autocomplete.css" rel="stylesheet">
    <script src="3b-autocomplete.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
    // GLOBAL TEMP VAR TO HOLD AUTOCOMPLETE DATA
    var actemp = null;
    window.addEventListener("load", function(){

      // NAME AUTO-COMPLETE
      suggest.attach({
        target : "user-name",
        url : "2-search.php",
        data : { type: "user-a" },

        // MANUAL OVERRIDE FETCH
        fetch : function (id, data) {
          // User data to global temp
          actemp = data;

          // Suggestion draw override
          for (let uid in data) {
            suggest.instance[id].box.insertAdjacentHTML("beforeend", "<div onclick=\"suggest.select('" + id + "', this, " + uid + " );\">" + data[uid]['name'] + "</div>");
          }
        },

        // Selection draw override
        select : function (id, el, more) {
          // Name
          suggest.instance[id].input.value = el.innerHTML;
          // Email
          document.getElementById("user-email").value = actemp[more]['email'];
          // Phone
          document.getElementById("user-tel").value = actemp[more]['phone'];
          // Close suggestions
          suggest.instance[id].box.innerHTML = "";
          suggest.instance[id].box.style.display = "none";
          document.removeEventListener("click", suggest.close);
        }
      });
    });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
    <br/><br/>
    Tel: <input type="email" id="user-tel"/>
  </body>
</html>

That’s all folks. The vanilla version is simpler than jQuery for once.

What next?

Thank you for reading, and we have come to the end of this short tutorial. I hope it has given you a kick start to your own project, and if you decide to use my vanilla auto-complete, please do remember to do your own CSS styles with it… It is really nothing but a skeleton.

If there is anything that you like to add to this guide, please feel free to comment below. Good luck and happy coding!

#Javascript #PHP #MySQL #jQuery #HTML

What is GEEK

Buddha Community

Autocomplete Textbox with Javascript, PHP and MySQL
Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

I am Developer

1599478483

PHP Autocomplete Textbox from Database Example

php ajax autocomplete search from database example. IN this post, i will show you three steps to create php autocomplete textbox from database using ajax with jQuery ui.

Autofill PHP Form from mysql database using ajax

Use the below 3 steps to create autocomplete search PHP MySQL using ajax with jQuery UI from the database:

    • Connecting Database
    • Autocomplete/Autofill search form
    • PHP Code for Search to Database

https://www.tutsmake.com/autocomplete-search-box-in-php-mysql/

#auto fill form input fields from database using ajax #autocomplete search box in php mysql #autocomplete textbox using jquery, php and mysql #jquery ui autocomplete from database php

I am Developer

1614263355

Google Places Autocomplete In PHP with Example

PHP 8 google address autocompletes without showing the map. In this tutorial, i will show youhow to create a google autocomplete address web app using google address APIs in PHP.

Note that, Google autocomplete address API will return address and as well as latitude, longitude, place code, state, city, country, etc. Using latitude and longitude of address, you can show markers location in google map dynamically in php.

This tutorial guide to you step by step how to implement google places autocomplete address web application without showing google maps in PHP.

For implementing the autocomplete address in php, you will have to get the key from google console app. So, just go to the link https://cloud.google.com and get the google API key.

https://www.tutsmake.com/php-google-places-autocomplete-example/

#autocomplete address google api php #google places autocomplete example in php #google places autocomplete example without map in php #php google places autocomplete jquery #php google places autocomplete jquery example

I am Developer

1597487472

Country State City Dropdown list in PHP MySQL PHP

Here, i will show you how to populate country state city in dropdown list in php mysql using ajax.

Country State City Dropdown List in PHP using Ajax

You can use the below given steps to retrieve and display country, state and city in dropdown list in PHP MySQL database using jQuery ajax onchange:

  • Step 1: Create Country State City Table
  • Step 2: Insert Data Into Country State City Table
  • Step 3: Create DB Connection PHP File
  • Step 4: Create Html Form For Display Country, State and City Dropdown
  • Step 5: Get States by Selected Country from MySQL Database in Dropdown List using PHP script
  • Step 6: Get Cities by Selected State from MySQL Database in DropDown List using PHP script

https://www.tutsmake.com/country-state-city-database-in-mysql-php-ajax/

#country state city drop down list in php mysql #country state city database in mysql php #country state city drop down list using ajax in php #country state city drop down list using ajax in php demo #country state city drop down list using ajax php example #country state city drop down list in php mysql ajax

Martin  Dreech

Martin Dreech

1576035038

Autocomplete Textbox with Javascript, PHP and MySQL

Welcome to a step-by-step tutorial on how to create an autocomplete textbox with PHP, Javascript, and MySQL. In this guide, we will walk through how to add autocomplete to a textbox, with the help of jQuery and also an alternative that works with just vanilla Javascript. Read on to find out!

Quick notes

  • Download and unzip the file into a folder.
  • Create a dummy database and import 1-users.sql.
  • Change the database settings in 2-search.php to your own.
  • Access 3a-jquery.html in your browser for the jQuery version.
  • Access 3b-vanilla.html in your browser for the vanilla Javascript version.

1. Step 1: The Database

For the purpose of demonstration in this tutorial, I have included a simple user table and some sample data. You can skip this part if you already have an established database and want to work on that instead.

The User table and Sample data

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(24) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Jazz Doe', 'jazz@doe.com', '56973317'),
(2, 'Jane Doe', 'jane@doe.com', '40378541'),
(3, 'Rusty Terry', 'rusty@terry.com', '34614899'),
(4, 'Peers Sera', 'peers@sera.com', '13014383'),
(5, 'Jaslyn Keely', 'jaslyn@keely.com', '52154191'),
(6, 'Richard Breann', 'richard@breann.com', '58765281'),
(7, 'Imogene Thad', 'imogene@thad.com', '11753471'),
(8, 'Tillie Sharalyn', 'tillie@sharalyn.com', '33989432'),
(9, 'Drake Adelaide', 'drake@adelaide.com', '56539890'),
(10, 'Coby Kelleigh', 'coby@kelleigh.com', '83788228'),
(11, 'Frank Doe', 'frank@doe.com', '87074336'),
(12, 'John Doe', 'john@doe.com', '55163490'),
(13, 'Jan Doe', 'jan@doe.com', '45310572'),
(14, 'Joe Doe', 'joe@doe.com', '48938630'),
(15, 'Joanne Doe', 'joanne@doe.com', '44101677'),
(16, 'Johan Doe', 'johan@doe.com', '28278966'),
(17, 'Charles Doe', 'charles@doe.com', '45800899'),
(18, 'Charlie Doe', 'charlie@doe.com', '32644564'),
(19, 'Apple Doe', 'apple@doe.com', '76438908'),
(20, 'Orange Doe', 'orange@doe.com', '47648224');

This is nothing but a very simple users table with 4 fields – id is the primary key, name, email, and phone.

Index your Searchable Fields

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `name` (`name`);

ALTER TABLE `users`
MODIFY `id` int(21) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

One small suggestion for you guys who already have an existing project – Index the fields that you want to search. Just why do we need to index the fields? Imagine a library that does not have a catalog system… Searching for a book that you want will mean having to look through each and every book in the library until you find it.

It is the same here in a database. If you don’t index the field, the database will have to look through each and every entry to find the ones that you want. Yes, while indexing does take up a little more disk space, but it will also speed up your searches and consume fewer system resources in the long run. Especially if you have a massive database with thousands of entries.

2. Step 2: Server - side script

Now that we have established the dummy database, it is time to work on a server-side script that will deal with searching the database and output the results.

Server side search

<?php
// YOU MIGHT WANT TO ADD SOME SECURITY ON THIS PAGE
// E.G. CHECK IF VALID USER LOGIN
// if (isset($_SESSION['admin'])) { ... DO SEARCH AS BELOW ... }
 
// (1) CONNECT TO DATABASE
$host = '127.0.0.1';
$dbname = 'test';
$user = 'root';
$password = '';
$charset = 'utf8';
$pdo = new PDO(
	"mysql:host=$host;dbname=$dbname;charset=$charset", $user, $password, [
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
		PDO::ATTR_EMULATE_PREPARES => false,
	]
);

// (2) SEARCHING FOR?
$data = [];
switch ($_POST['type']) {
  // (2A) INVALID SEARCH TYPE
  default :
    break;
 
  // (2B) SEARCH FOR USER
  case "user":
    // You might want to limit number of results on massive databases
    // SELECT * FROM XYZ WHERE `FIELD` LIKE ? LIMIT 20
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[] = $row['name'];
    }
    break;
 
  // (2C) SEARCH FOR EMAIL
  case "email":
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `email` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[] = $row['email'];
    }
    break;
}

// (3) RETURN RESULT
$pdo = null;
echo json_encode($data);
?>
  • There are 2 things to POST to this script.
    • First, which type of data you want – user or email?
    • A search term should also be sent to this script.
  • Very straightforward. The script first opens up a database connection – Remember to change the database settings to your own.
  • Then runs a search query on the user table, depending on what you want to look for.
  • Finally, outputs the search results (if any) using JSON encoding.

The jQuery autocomplete plugin uses JSON, but if you want to work with XML (or some other format) in your own project, please feel free to modify the code to fit your own needs.

3. Step 3: Jquery Autocomplete

The final piece of the puzzle is the client-side HTML and Javascript, which I will give 2 alternatives for. This first alternative includes the use of jQuery UI, and it is as simple as attaching the autocomplete to an element.

The Script:

<!DOCTYPE html>
<html>
  <head>
    <title>
      jQuery Autocomplete Demo
    </title>
    <!-- (1) YOU NEED TO LOAD BOTH JQUERY AND JQUERY UI -->
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>ry-ui.min.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
      $(function () {
        // NAME AUTO-COMPLETE
        $('#user-name').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "user"
              },
              success: response,
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          }
        });

        // EMAIL AUTO-COMPLETE
        $('#user-email').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "email"
              },
              success: response,
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          }
        });
      });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
  </body>
</html>

The Explannation

Not much rocket science is required when it comes to jQuery:

  • You need to load both the main jQuery library and jQuery UI.
  • Just attach autocomplete to the input fields.

Please do also check out the jQuery autocomplete documentation on the settings that you can use.

4. Step 4: Vanila JavaScript

For you guys who do not wish to use jQuery, or think that it adds too much to the loading bloat, here is the other alternative written in pure Javascript.

The HTML:

<!DOCTYPE html>
<html>
  <head>
    <title>
      Vanilla Javascript Autocomplete Demo
    </title>
    <!-- (1) AUTOCOMPLETE SCRIPTS -->
    <link href="3b-autocomplete.css" rel="stylesheet">
    <script src="3b-autocomplete.js"></script>
 
    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
    window.addEventListener("load", function(){
      // NAME AUTO-COMPLETE
      suggest.attach({
        // Minimum required
        target : "user-name",
        url : "2-search.php",
        data : { type: "user" }
      });
 
      // EMAIL AUTO-COMPLETE
      suggest.attach({
        // Minimum required
        target : "user-email",
        url : "2-search.php",
        data : { type: "email" },
        // Optional
        delay : 200,
        min : 3
      });
    });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
  </body>
</html>
  • Text fields in HTML as usual.
  • We wait for the page to be fully loaded window.addEventListener("load") before attaching the autocomplete to the fields.
  • Use suggest.attach(OPTIONS) to attach the auto-suggest to the required fields.

The CSS

div.acWrap {
  position: relative;
}
div.acBox {
  position: absolute;
  top: 25px;
  left: 0;
  padding: 5px;
  z-index: 999;
  background: #fff;
  border: 1px solid #000;
  display: none;
}
div.acBox div:hover {
  cursor: pointer;
  background: #fea;
}

Now, the autocomplete script will “pad” the input field with some HTML `` wrappers, and it will look like this when fully initiated:

<div id="acWrapXXX" class="acWrap">
  <input type="text" id="FOO-BAR"/>
  <div id="acBoxXXX" class="acBox">
    <!-- SUGGESTIONS ARE PUT HERE DYNAMICALLY VIA AJAX -->
    <div>Option A</div>
    <div>Option B</div>
    <div>Option C</div>
  </div>
</div>

So feel free to change the CSS styles to suit your own project.

The JavaScript:

var suggest = {
  instance : {}, // attached instances
  focus : null, // current text field in focus

  attach : function (opt) {
  // suggest.attach () : attach autosuggest to input field
  // opt : options
  //  - target : ID of target input field, required
  //  - url : Target URL to fetch suggestions, required
  //  - delay : Delay before autocomplete fires up, optional, default 500ms
  //  - min : Minimum characters to fire up autocomplete, default 2
  //  - data : Additional data to post to the server
  //  - fetch : Manual fetch draw override
  //  - select : Manual select draw override

    // Create autocomplete wrapper and box
    var id = Object.keys(suggest.instance).length,
        input = document.getElementById(opt.target);
    input.outerHTML = "<div id='acWrap" + id + "' class='acWrap'>" + input.outerHTML + "<div id='acBox" + id + "' class='acBox'></div></div>";

    // Set the HTML references and options
    suggest.instance[opt.target] = {
      input : document.getElementById(opt.target),
      wrap : document.getElementById("acWrap" + id),
      box : document.getElementById("acBox" + id),
      delay : opt.delay ? opt.delay : 500,
      url : opt.url,
      min : opt.min ? opt.min : 2,
      data : opt.data ? opt.data : null,
      fetch : opt.fetch ? opt.fetch : null,
      select : opt.select ? opt.select : null,
      timer : null
    };
 
    // Attach key listener
    suggest.instance[opt.target].input.addEventListener("keyup", function (evt) {
      // Clear old timer
      if (suggest.instance[opt.target].timer != null) {
        window.clearTimeout(suggest.instance[opt.target].timer);
      }

      // Hide and clear old suggestion box
      suggest.instance[opt.target].box.innerHTML = "";
      suggest.instance[opt.target].box.style.display = "none";

      // Create new timer, only if minimum characters
      if (evt.target.value.length >= suggest.instance[opt.target].min) {
        suggest.instance[opt.target].timer = setTimeout(
          function () { suggest.fetch(evt.target.id); },
          suggest.instance[opt.target].delay
        );
      }
    });

    // This is used to hide the suggestion box if the user navigates away
    suggest.instance[opt.target].input.addEventListener("focus", function (evt) {
      if (suggest.focus != null) { suggest.close(null, true); }
      suggest.focus = opt.target;
    });
  },

  fetch : function (id) {
  // suggest.fetch() : AJAX get suggestions and draw
  // id : ID of target input field, automatically passed in by keyup event

    // Init AJAX
    var req = new XMLHttpRequest();
    req.addEventListener("load", function () {
      var data = JSON.parse(this.response);
      if (Object.keys(data).length > 0) {
        if (suggest.instance[id]['fetch']) {
          suggest.instance[id]['fetch'](id, data);
        } else {
          data.forEach(function (el) {
            suggest.instance[id].box.insertAdjacentHTML("beforeend", "<div onclick=\"suggest.select('" + id + "', this);\">" + el + "</div>");
          });
        }
        suggest.instance[id].box.style.display = "block";
        document.addEventListener("click", suggest.close);
      }
    });

    // Data
    var data = new FormData();
    data.append('term', suggest.instance[id].input.value);
    if (suggest.instance[id].data) {
      for (let key in suggest.instance[id].data) {
        data.append(key, suggest.instance[id].data[key]);
      }
    }

    // Send
    req.open("POST", suggest.instance[id].url);
    req.send(data);
  },

  select : function (id, el, more) {
  // suggest.select() : user selects a value from autocomplete
  // id : ID of text box
  // el : Selected element
  // more : Optional, more parameters
 
    if (suggest.instance[id]['select']) {
      suggest.instance[id]['select'](id, el, more);
    } else {
      suggest.instance[id].input.value = el.innerHTML;
      suggest.instance[id].box.innerHTML = "";
      suggest.instance[id].box.style.display = "none";
      document.removeEventListener("click", suggest.close);
    }
  },

  close : function (evt, force) {
  // suggest.close() : close the autocomplete box if the user clicks away from the input field
  // evt : click event
  // force : force close
 
    if (force || event.target.closest(".acWrap") == null) {
      suggest.instance[suggest.focus].box.innerHTML = "";
      suggest.instance[suggest.focus].box.style.display = "none";
      document.removeEventListener("click", suggest.close);
    }
  }
};

The Explannation:

This may look like a handful at first, but it is a stepped-down version of what jQuery has. Rather than repeating line-by-line (trace through the code on your own if you want)

5. Step 5: Jquery Complete Multiple

The autocomplete is done, and here is an extra section for you guys who want to populate many fields with one selection. For example, select the user name, and automatically fill up the email plus phone number.

Sever-Side Addition

Firstly, we are going to add an extra handler in the server-side search script:

<?php
switch ($_POST['type']) {
  // (X) SEARCH FOR USER WITH ALL INFORMATION
  // JQUERY VERSION
  case "user-all":
    // Data yoga
    $data = [
      "display" => [],
      "details" => []
    ];

    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data["display"][] = [
        "label" => $row['name'],
        "value" => $row['id']
      ];
      $data["details"][$row['id']] = [
        "email" => $row['email'],
        "phone" => $row['phone']
      ];
    }
    break;
}

Now, this is pretty much the same “search the database by user” as above, but we are returning a lot more data this time around. Take note of how the $data response is formed:

  • $data["display"] will hold a list of the user id and name.
  • $data["details"] will hold a list of the user id, email and phone.
  • Yep, I would have just packed everything flat inside $data, but jQuery works otherwise.
  • In jQuery autocomplete, it takes an array of label and value to generate the autocomplete list – This is why we have $data["display"], with the user name as the label, and id as the value.
  • Upon selection, as you can guess, we will then use the selected value (id) to tie to $data["details"] – Use it to populate the rest of the fields.

The HTML

<!DOCTYPE html>
<html>
  <head>
    <title>
      jQuery Autocomplete Demo
    </title>

    <!-- (1) YOU NEED TO LOAD BOTH JQUERY AND JQUERY UI -->
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
      // GLOBAL TEMP VAR TO HOLD AUTOCOMPLETE DATA
      var actemp = null;

      // AUTO-COMPLETE SETUP
      $(function () {
        $('#user-name').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "user-all"
              },
              success: function(data) {
                // Details to global temp
                actemp = data.details;

                // Return display
                response(data.display);
              },
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          },
          select : function(event, ui){
            // Fill up other fields
            $('#user-name').val(ui.item.label);
            $('#user-email').val(actemp[ui.item.value]['email']);
            $('#user-tel').val(actemp[ui.item.value]['phone']);
            return false;
          }
        });
      });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
    <br/><br/>
    Tel: <input type="email" id="user-tel"/>
  </body>
</html>

A little bit confusing in this one, but it is as described above. We use data["display"] to generate the selection list, use the selected value to tie back to data["details"], then it is possible to auto-complete the rest of the other fields.

6. Step 6: Vanilla Complete Multiple

Finally, the “auto-complete many” vanilla Javascript version.

Sever-Side Addition

Just like the jQuery version, we are going to add another request to the server-side script. But this time, it will be a lot more straightforward for the vanilla version.

<?php
switch ($_POST['type']) {
  // (X) SEARCH FOR USER WITH ALL INFORMATION
  // VANILLA VERSION
  case "user-a":
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[$row["id"]] = $row;
    }
    break;
}

Override Draw

Finally, all we have to do is to override the draw and select to pick up the additional data.

<!DOCTYPE html>
<html>
  <head>
    <title>
      Vanilla Javascript Autocomplete Demo
    </title>

    <!-- (1) AUTOCOMPLETE SCRIPTS -->
    <link href="3b-autocomplete.css" rel="stylesheet">
    <script src="3b-autocomplete.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
    // GLOBAL TEMP VAR TO HOLD AUTOCOMPLETE DATA
    var actemp = null;
    window.addEventListener("load", function(){

      // NAME AUTO-COMPLETE
      suggest.attach({
        target : "user-name",
        url : "2-search.php",
        data : { type: "user-a" },

        // MANUAL OVERRIDE FETCH
        fetch : function (id, data) {
          // User data to global temp
          actemp = data;

          // Suggestion draw override
          for (let uid in data) {
            suggest.instance[id].box.insertAdjacentHTML("beforeend", "<div onclick=\"suggest.select('" + id + "', this, " + uid + " );\">" + data[uid]['name'] + "</div>");
          }
        },

        // Selection draw override
        select : function (id, el, more) {
          // Name
          suggest.instance[id].input.value = el.innerHTML;
          // Email
          document.getElementById("user-email").value = actemp[more]['email'];
          // Phone
          document.getElementById("user-tel").value = actemp[more]['phone'];
          // Close suggestions
          suggest.instance[id].box.innerHTML = "";
          suggest.instance[id].box.style.display = "none";
          document.removeEventListener("click", suggest.close);
        }
      });
    });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
    <br/><br/>
    Tel: <input type="email" id="user-tel"/>
  </body>
</html>

That’s all folks. The vanilla version is simpler than jQuery for once.

What next?

Thank you for reading, and we have come to the end of this short tutorial. I hope it has given you a kick start to your own project, and if you decide to use my vanilla auto-complete, please do remember to do your own CSS styles with it… It is really nothing but a skeleton.

If there is anything that you like to add to this guide, please feel free to comment below. Good luck and happy coding!

#Javascript #PHP #MySQL #jQuery #HTML