I created this query to display my auto complete box with jQuery. It is working perfectly.
<?php
require_once('database.php');
if(isset($_POST['queryString'])) {
$queryString = $dbc->real_escape_string($_POST['queryString']);
if(strlen($queryString) >0) {
$q = "SELECT keyword
FROM (
SELECT tname AS keyword FROM t
UNION
SELECT sname AS keyword FROM sub
UNION
SELECT cname AS keyword FROM c
UNION
SELECT iname AS keyword FROM i
) s
WHERE keyword LIKE '%$queryString%'
LIMIT 10";
$r = mysqli_query ( $dbc, $q);
if($q) {
while ($row = mysqli_fetch_array($r, MYSQL_ASSOC)) {
echo '<li onClick="fill(\''.$row['keyword'].'\');">'.$row['keyword'].'</li>';
}
} else {
echo 'ERROR: There was a problem with the query.';
}
} else {
}
} else {
echo 'There should be no direct access to this script!';
}
?>
with this query display auto complete list with subjects, tutor names, cities according the keyword. Now I need to modify auto complete list with more values. For an example there is a tutor name in the list I want to display his/her current city, his/her profile image etc. It is something similar to Facebook search.
this is my city and address tables
# --------------
# Address Table
# --------------
CREATE TABLE address (
address_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
address_one VARCHAR(40) NOT NULL,
address_two VARCHAR(40) DEFAULT NULL,
city_id INT(4) UNSIGNED NOT NULL,
PRIMARY KEY (address_id),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
# --------------
# City Table
# --------------
CREATE TABLE city(
city_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
city_name VARCHAR(30) NOT NULL,
district_id INT(2) UNSIGNED NOT NULL,
PRIMARY KEY (city_id),
UNIQUE (city_name)
) ENGINE=MyISAM;
How can I do this?
I think there are 2 options.
I believe you should adjust your query, because now you look in the t table and in the sub table checking if the searchword occurs. However, your query is so short that you dont have available the matching profile info, just the column on which you perform the search.
Ofcourse you dont want to query a lot of data from rows that dont even match your criteria. On the other hand, its not so convenient to do a lot of queryies after you performed the search. However, since you have limit to 10, it should be okay.
Change your query to select name, birthday, gender, imgdata from t etc..