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

Code:

    <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

Code:

<?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:

Code:

	$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

Code:

		$('[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.