CODEWITHSUNDEEP
mysqlmariadb
smallpants
smallpants

Reputation: 490

Using MySQL for autocomplete fields

I'm new to web development and I've been creating a website for the family business. We are a compounding pharmacy and I'd like to implement a feature for our client doctors to submit new prescriptions online as well as get price quotes on our formulas. I have the page for the orderform already made, however I want to make the "Drug" field autocomplete using our database of formulas.

I have a MySQL (MariaDB on Fedora Server) database already set up and ready to be queried. I'm not sure how to go about making the field autocomplete. I have seen implementations of Twitter's Typeahead javascript system but I couldn't follow the tutorial since I have no experience with JS.

Upvotes: 2

Views: 544

Answers (1)

Patrick Locke
Patrick Locke

Reputation:

If you are using bootstrap (you should if you aren't) You can use typeahead. (google typeahead.bundle.js)
Call this javascript file after bootstrap.

Then you need a little CSS

<style>
.typeahead,
.tt-query,
.tt-hint {

  font-size: 12px;

}

.typeahead {
  background-color: #fff;
}

.typeahead:focus {
  border: 2px solid #0097cf;
}
.tt-query {
  -webkit-box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
     -moz-box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
          box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
}

.tt-hint {
  color: #999
}

.tt-menu {
  width: 422px;
  margin: 12px 0;
  padding: 8px 0;
  background-color: #fff;
  border: 1px solid #ccc;
  border: 1px solid rgba(0, 0, 0, 0.2);
  -webkit-border-radius: 8px;
     -moz-border-radius: 8px;
          border-radius: 8px;
  -webkit-box-shadow: 0 5px 10px rgba(0,0,0,.2);
     -moz-box-shadow: 0 5px 10px rgba(0,0,0,.2);
          box-shadow: 0 5px 10px rgba(0,0,0,.2);
}

.tt-suggestion {
  padding: 3px 20px;
  font-size: 18px;
  line-height: 24px;
}

.tt-suggestion:hover {
  cursor: pointer;
  color: #fff;
  background-color: #0097cf;
}

.tt-suggestion.tt-cursor {
  color: #fff;
  background-color: #0097cf;

}

.tt-suggestion p {
  margin: 0;
}

.gist {
  font-size: 14px;
}

Then you need the input

<input id="mytextquery" name="mytextquery" type="text" size="71" maxlength="128" value="" placeholder="Carrier (type to search)" class="form-control typeahead"/>

Then a little javascript on the page.

<script>
  $(function () {
    $('#mytextquery').typeahead({
      hint: true,
      highlight: true,
      minLength: 1
    },
    {
      limit: 12,
      async: true,
      source: function (query, processSync, processAsync) {

        return $.ajax({
          url: "typeTest.php", 
          type: 'GET',
          data: {query: query},
          dataType: 'json',
          success: function (json) {
            // in this example, json is simply an array of strings
            return processAsync(json);
          }
        });
      }
    }); 

  });
</script>

Then some php or whatever you are using to get the data from your database

$query = $_GET['query'];
//Do your SQL query here
$query = "SELECT * from table where field LIKE '$query'";
//Get results and format like below
$data1 = [ 'Item 1', 'Item 2', 'Item 3' ,'etc', 'etc'];

//Export it so typeahead can read it.
header('Content-type: application/json');

  echo json_encode( $data1 );

Upvotes: 1

Related Questions