Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    How to get autocomplete data from MYSQL based on field searched

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •