I have integrated auto-complete search in my site where user enter keyword and view the records based on it.
Suppose, if I write zip code it only filter data from zip field. Next, if user types address like 121 North West, so script automatically pick records matches 121 North West. Moreover, same for street, if user type street name like cedar lane, it then fetches records from database field street only.
I am having problem that it searches the records from all fields and so I am unable to get proper listing from database.
Referense site I am following is http://www.trulia.com here this is working as expected. I want the same on my site and it works but not like I expects.
My PHP Code:
<?php
$searchKeyword = $_REQUEST['keyword'];
$searchQ = "SELECT zip, ste, st, st_num, town, addr FROM tbl_property WHERE (zip = '$searchKeyword' OR ste like '%$searchKeyword%' OR town like '%$searchKeyword%' OR addr like '%$searchKeyword%') GROUP BY zip LIMIT 0,5";
$queryRec = mysql_query($searchQ);
$recSet = mysql_num_rows($queryRec);
echo "<div id='fetchRecs'><ul>";
if($recSet>0){
while($row = mysql_fetch_array($queryRec)) {
echo '<li>'.$row['addr'].', '.$row['town'].', '.$row['ste'].', '.$row['zip'];?></li>
<?php
}
}else{
echo "<li>No Records Found</li>";
}
echo "</ul></div>";
I simply want if user writes 123, list shows like 1234,1245, and same like listing that show only zip codes from zip column.
Same, If user write 123 cedar it picks listing similar to 123 cedar only means it will come from street column.
P.S: I am having only one text field in which I type keyword like: http://www.trulia.com
P.S: My question is still not resolved, please share if anybody have any useful information. May be it will work for some other as well.
First of all, perdon me for my mistakes.
Ok, i changed a few things and now is working:
//Filter:
<?php
if (isset($_REQUEST['field']) && isset($_REQUEST['keyword'])) {
$field = $_REQUEST['field'];
$keyword = $_REQUEST['keyword'];
}else{
$field = null;
$keyword = null;
}
$query = "SELECT * FROM tbl_property";
$where = "";
switch ($field) {
case 'zipCode':
$where = " WHERE zip like '" . $keyword . "%'";
break;
case 'addr':
$where = " WHERE addr like " . $keyword . "%";
break;
case 'city':
$where = " WHERE city like " . $keyword . "%";
break;
}
$query = $query . $where;
$db = new mysqli('localhost', 'user', 'password', 'db');
if ($db->connect_errno > 0){
die($db->connect_error);
}
$result = $db->query($query)
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Searcher</title>
<link rel="stylesheet" href="">
</head>
<body>
<section id="searcher">
<form action="" method="post">
<input type="text" name="keyword" id="keyword" />
<input type="hidden" name="field" id="field" value="all" />
<div id="lists">
<ul></ul>
</div>
</form>
</section>
<section id="results">
<table>
<caption>Results</caption>
<thead>
<tr>
<th>Zip</th>
<th>Address</th>
<th>City</th>
</tr>
</thead>
<tbody>
<?php while($row = $result->fetch_assoc()){ ?>
<tr>
<td><?php echo ($row['zip']); ?></td>
<td><?php echo ($row['addr']); ?></td>
<td><?php echo ($row['city']); ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</section>
<script src="http://code.jquery.com/jquery-2.1.3.js" type="text/javascript" charset="utf-8"></script>
<script type="text/javascript">
$( "#keyword" ).keyup(function() {
$.ajax({
type: "POST",
url: "req.php",
data:{ key: $(this).val() },
success: function(data){
//Show the data to the clients
//(in a div or a "select2" plugin)
if (data.locations.length > 0){
$('#lists ul').html('');
$.each(data.locations, function( key, location ) {
//iterate over locations
$('#lists ul').append( '<li data-field="' + location._type + '">' + location.value + '</li>' );
});
//SetUp hidden default value while keyword change
//Form the JSON Response
$('#field').val(data.locations[0]._type);
}else{
$('#lists ul').html('No Results!');
$('#field').val('all');
}
}
});
});
$('#lists ul li').click(function(){
$('#keyword').val($(this).html());
$('#field').val($(this).parent().data('field'));
});
</script>
</body>
</html>
<?php
$db->close();
?>
and
//req.php
<?php
$key = $_POST['key'];
$data = array(
'locations' => array(),
'errors'=>array(),
'success'=> true
);
header('Content-Type: application/json');
$db = new mysqli('localhost', 'user', 'password', 'db');
if($db->connect_errno > 0){
$data['errors'][] = $db->connect_error;
die(json_encode($data));
}
$query = "SELECT zip FROM tbl_property WHERE zip LIKE '" . $key . "%';";
$zips = $db->query($query);
$query = "SELECT city FROM tbl_property WHERE city LIKE '" . $key . "%';";
$cities = $db->query($query);
if($db->connect_errno > 0){
$data['errors'][] = $db->connect_error;
die(json_encode($data));
}
$index = 0;
if ($zips){
while($row = $zips->fetch_assoc()){
$data['locations'][] = array(
"value"=> $row['zip'],
"altValue"=> null,
"display"=> $row['zip'],
"_type"=> "zipCode",
"propertyIndex"=> "",
"index"=> $index
);
$index = $index + 1;
}
}
if ($cities){
while($row = $cities->fetch_assoc()){
$data['locations'][] = array(
"value"=> $row['city'],
"altValue"=> null,
"display"=> $row['city'],
"type"=> "city",
"propertyIndex"=> "",
"index"=> $index
);
$index = $index + 1;
}
}
$db->close();
header('Content-Type: application/json');
echo json_encode($data);
i did not run a test to the click event on the lists ul li element, because i don't know what would you do, and is the less important part.
I hope that was what you was looking for. keep in touch.