...

View Full Version : How to get autocomplete data from MYSQL based on field searched



treigh
01-25-2013, 09:07 AM
Hi there,

I'm using Twitter Bootstrap framework for autocomplete data to suggest a city names to users as they're filling up a contact form. .after
watching a a tutorial on how to pull remote MYSQL data using Bootstrap typeahead and JSON, I'm now able to make city names available to the user. Now I'd like to take this a step further. My solution only works with one field. I'd also like to suggest postal codes to the user the same way I do it with city names.

Here are my codes
Frontend

<script>
$(function(){
$('[rel=cities]').typeahead({
source: function (query, process) {
$.ajax({
url: 'data.php',
type: 'POST',
data: 'query=' + query,
dataType: 'JSON',
async: 'true',
success: function(data) {
//console.log(data);
process(data);
}
});

}

});
});
</script>


Backend

<?php

if (isset($_POST['query'])) {

// Database connect
mysql_connect("localhost", "root", "pass");
mysql_select_db ("data");

// Retrieve the query
$query = $_POST['query'];

//search the database for unique city names based on query
$sql = mysql_query ("SELECT DISTINCT asciiname FROM cities WHERE asciiname LIKE '%{$query}%'");
$array = array();

while ($row = mysql_fetch_assoc($sql)) {

$array[] = $row['asciiname'];
}
//Return the JSON Array

echo json_encode ($array);

}

?>

I'm very new to PHP and I apologize already if the question barely makes sense.

Thanks for any help.

Old Pedant
01-25-2013, 08:36 PM
Though you *could* use the same "data.php" page to do this, you'd likely find it easier to create another page specifically for the zip codes. Maybe "datazip.php".

Change the query and the fetch line:

$sql = mysql_query ("SELECT DISTINCT zip FROM cities WHERE zip LIKE '{$query}%'");
...
$array[] = $row['zip'];

Notice that I removed the first % after LIKE. If you don't do that, and the user types "9", then you will find "90105", yes, but you will also find "10019". That is, then "9" can be anywhere in the zip code. By removing that first %, at least the zip code must *START* with what the user typed in. (It's possibly a change you might want to make with your cities query...up to you.)

And then of course create another jQuery function, this one with

$('[rel=zip]').typeahead({

I don't use jQuery, so I don't know if you need to make other changes there (ask in the jQuery forum), but I don't think so.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum