There are a number of WordPress plugins that provide some jQuery AutoComplete functionality. The problem is, they only access data that’s in the WordPress Database. I needed to be able to use autocomplete using a City/State database that is separate from the WordPress database in MySQL. Then, I needed to pull more data from that database and fill in other fields in a form based on the City/State selection:
There are just three components needed to create this autocomplete setup in WordPress apart from the data set that you want to access. The first is a *.php file to access the database, which in this example is ‘cities.php‘. (In this example, the ‘cities.php’ file must be located in the root directory of your website.) The second component is a jQuery file that connects the data query (in the *.php file) to the form field. Third, you need a form somewhere that you use to search for the record you want and also fill in the other fields with the additional data.
The *.php file needs to connect to your database and run a query where your search term (CityState in this case) is LIKE ‘%term%’ which is how the jQuery AutoComplete plugin does it’s magic. Then that search term (in this case ‘CityState‘ from the ‘NewCities‘ table) of the SQL query is tagged in an array as both the $row_array['label'] and $row_array['value'] as seen below.
<?php
$DATABASE = 'YourDatabase';
$HOST = 'localhost';
$USERNAME = 'username';
$PASSWORD = 'password';
// connect to the database server and select the appropriate database for use
$dblink = mysql_connect($HOST, $USERNAME, $PASSWORD) or die( mysql_error() );
mysql_select_db($DATABASE);
$return_arr = array();
$term=trim(strip_tags($_GET['term']));//retrieve the search term that autocomplete sends
$sql="SELECT * FROM NewCities WHERE CityState LIKE '%$term%' ORDER BY City";
$result=mysql_query($sql);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$row_array['label'] = $row['CityState'];
$row_array['value'] = $row['CityState'];
$row_array['TimeZone'] = $row['TimeZone'];
$row_array['Latitude'] = $row['Latitude'];
$row_array['Longitude'] = $row['Longitude'];
array_push( $return_arr, $row_array );
}
mysql_close($db_connection);
echo json_encode($return_arr),"\n";
?>
Now, you need to create a javascript file that will go into the /js directory of the current theme template directory (I called this one cities.js):
jQuery(document).ready(
function($) {
$('#citysearch').autocomplete({
selectFirst: true,
source:'/cities.php',
minLength:2,
autoFocus: true,
select:function(evt, ui)
{
// when a City is entered, populate related fields in the form
this.form.TimeZone.value = ui.item.TimeZone;
this.form.Latitude.value = ui.item.Latitude;
this.form.Longitude.value = ui.item.Longitude;
},
change: function (event, ui) {
if (ui.item == null || ui.item == undefined) {
$(this).val((ui.item ? ui.item.Id: ""));
alert('City Not Found');
}
},
});
});
Then you need to register the script (‘cities.js‘) in your theme’s function file:
function city_scripts()
{
wp_register_script( 'city', get_template_directory_uri() . '/js/cities.js', array( 'jquery', 'jquery-ui-core', 'jquery-ui-autocomplete' ) );
wp_enqueue_script('city');
}
add_action( 'wp_enqueue_scripts', 'city_scripts' );
Once the *.php file and the script are created and registered, then you can create a form:
<form>
<p style="padding-left: 30px;">City:<input id="citysearch" tabindex="13" title="Enter a City " name="Cities" required="" size="50" type="text" placeholder="City (e.g. Eugene, OR, US)" /></p>
<div id="city_form" style="padding-left: 30px;">
<table>
<tbody>
<tr>
<td>TimeZone: <input id="TimeZone" class="readonly" style="border: none; display: inline-block;" title="Enter a city to fill in this field automatically" maxlength="12" name="TZ" required="" size="12" type="text" placeholder="PST +8:00" /></td>
<td>Latitude: <input id="Latitude" class="readonly" style="border: none; display: inline-block;" title="Enter a city to fill in this field automatically" maxlength="5" name="Lat" required="" size="5" type="text" placeholder="43N94" /></td>
<td>Longitude: <input id="Longitude" class="readonly" style="border: none; display: inline-block;" title="Enter a city to fill in this field automatically" maxlength="6" name="Long" required="" size="6" type="text" placeholder="123W12" /></td>
</tr>
</tbody>
</table>
</div>
</form>
The important elements of the form are the id’s. #citysearch is the field that is searched on, while the #TimeZone, #Latitude and #Longitude are the form fields that are used to fill in the data related to the selected city.
Once you have a database to search, you’re there! It just works as you see below!
Leave a Comment