jQuery Autosuggest PHP 2 tables

[dmservers]

Hello all,

I've ran into a bit of a problem and I can't figure out how to get out of it.

Basically, I have an autosuggest input field on my website and I would like to get data from 2 mysql tables.

It is similar to yell.com where you search for your "keyword" or "company name" and it all gets returned.

www.whatslocaltome.co.uk is the website. You can see my autosuggest box at the top (Keyword)... I want it to return either towns or company names...

Code so far:

$sql_res=mysql_query("select * from directory_categories where `category` like '%$q%' order by id LIMIT 5");
if($sql_res > 0){
	while($row=mysql_fetch_array($sql_res))
	{
	$kw=$row['category'];
?>
		<div class="display_box" align="left">
		<a href="" onClick="document.forms['searchform'].keyword.value = '<?php echo $kw; ?>'; return false;" id="resultsclose" onclick="fill();" >
<?php 
		echo $kw; 
?>
		</a>
		</div>
<?php
	}
}
elseif(!$sql_res) {
?>
	<div class="display_box" align="left">
<?php
	echo "No suggestion";?>
		</div>
<?php
	}
}
else
{
}
?>

[cwarn23]

I would send you a video about jQuery but I'm not allowed according to happygeek so I can't answer that question for now.

[dmservers]

I don't have a problem with jQuery. It's the PHP coding I am struggling with a bit. I need to know how to retrieve the data from both tables on the fly and display them in 1 output.

[cwarn23]

Don't you need an ajax page for the autosuggest to do a query on your database, fetch and put onto the page.

[dmservers]

I've got the ajax sorted for it. It is all in place and works as it is at the moment. But at the moment it's only setup to query the categories table. I want to be able to query the categories AND towns table at the same time. No data is the same in either tables.

[cwarn23]

could you please post ajax.php

[dmservers]

The code at the top of the topic is the php file that is called via ajax.

This is the ajax code that calls it:

$(document).ready(function(){
$(".search").keyup(function() 
{
var searchbox = $(this).val();
var dataString = 'keyword='+ searchbox;
if(searchbox=='')
{}
else
{
	$.ajax({
	type: "POST",
	url: "results.php",
	data: dataString,
	cache: false,
	success: function(html)
{
$('#display').fadeIn();
}
});
}return false; 
});

[cwarn23]

jQuery again :(
I don't know much about jQuery but know enough to say that jQuery isn't a good tool to use particularly for styling and functions. I dunno about you but I find ajax a mind bobling experience and a lot simpler and more efficient to deal with raw Javascript. As per how to do this job, the question isn't exactly clear although I do get that you want to select two tables at the same time. You haven't said what you need to do with them I'm kinda guessing here. My guess would by you want like a Join on the query. The best way to do a join in that case would be to have a third table which has 2 columns where column one is in table one and column two is in table two. This way you can do a 3 way join. If however you want to retrieve the data with one ajax request then you will probably want a separator of some sort and get javascript to do the php equivalent of explode(). I'm about to go to bed but I'll be back online tomorrow