I am trying to put a full text search together. I can search one table just fine, but I would like to be able to search multiple tables. Everytime I try to search multiple tables I end up with a ambiguos error. Heres my current sytax for searching one table.
Code:
$sql = mysql_query("SELECT *, MATCH(title, message) AGAINST('$keywords') AS score FROM news WHERE MATCH(title, message) AGAINST('$keywords') ORDER BY score DESC") or die(mysql_error());
Any ideas how I could expand this to search more than one table? Thanks for your time.
No the tables are not related. Basically there are 3 tables I want to search. News, Downloads, and Tutorials. However all the tables do have a "title" field and a "message" field. Any ideas?
i'm not 100% sure what you're trying to do.
you have select * in your query. does all information get displayed in some way? it seems like these three objects are completely different, and contain different things. how can you expect to merge 3 things that are very different?
my opinion would be to search separately, not in a single query, unless you can find some way of making them inherit a common item that makes them searchable.
I am using a while statement in php to display the info
PHP Code:
while($result = mysql_fetch_array($sql)) {
$title = $result['title'];
$message = $result['message'];
//display the info
}
Even thou the info is in different tables it is basically the same. A title of the news, download, or tutorial and a description of the news, download, or tutorial.
I thought I could do something like this
Code:
$sql = mysql_query("SELECT news.title, news.message, downloads.title, downloads.message, MATCH(title, message) AGAINST('$keywords') AS score FROM news, downloads WHERE MATCH(title, message) AGAINST('$keywords') ORDER BY score DESC") or die(mysql_error());
but that just gives me the error "Column: 'title' in field list is ambiguous". I plan on offering the user to search tables indivisualy but would like it possible to search the entire group of tables also.
don't do it like that. if the tables are not related, joining them will cause headaches.
you should use the UNION clause between multiple statements to link them.
Code:
SELECT title,message, MATCH(title, message) AGAINST('$keywords') AS score FROM news WHERE MATCH(title, message) AGAINST('$keywords') ORDER BY score DESC
UNION
SELECT title,message, MATCH(title, message) AGAINST('$keywords') AS score FROM downloads WHERE MATCH(title, message) AGAINST('$keywords') ORDER BY score DESC
...
Ahh thanks Chazzy thats what I was looking for. It works great now. I have a new problem thou. I am attempting to count the rows that match my search for pagination. I have it working fine when using just one table, but the UNION clause doesn't seem to be working when I use the COUNT clause. It does not return an error but it seems to be returning the count of only the first table.
Code:
$countresult = mysql_query("SELECT COUNT(*) as totalnum from news WHERE MATCH(title, message) AGAINST('$keywords') UNION SELECT COUNT(*) as totalnum from downloads WHERE MATCH(title, message) AGAINST('$keywords')") or die(mysql_error());
Been searching for an answer but haven't found anything on UNION and COUNT together.
SELECT COUNT(*) FROM
(SELECT.... the union goes here ) a;
since it looks like you're using PHP, i'd recommend doing something like
PHP Code:
$sql = "
SELECT * FROM
(SELECT title,message, MATCH(title, message) AGAINST('$keywords') AS score FROM news WHERE MATCH(title, message) AGAINST('$keywords')
UNION
SELECT title,message, MATCH(title, message) AGAINST('$keywords') AS score FROM downloads WHERE MATCH(title, message) AGAINST('$keywords')
...) a ORDER BY SCORE DESC";
$result = mysql_query($sql) or die("Unable to issue query: ".mysql_error());
$count = mysql_num_rows($result);
...
I'm trying to do the exact same thing here, but am experiencing errors.
The code i'm using is this:
PHP Code:
// Formulate Query
// This is the best way to perform a SQL query
// For more examples, see mysql_real_escape_string()
$query = ("SELECT * FROM
(SELECT title,description, MATCH(title, description) AGAINST('$product_search') AS score FROM tshirts WHERE MATCH(title, description) AGAINST('$product_search')
UNION
SELECT title,description, MATCH(title, description) AGAINST('$product_search') AS score FROM bandanas WHERE MATCH(title, description) AGAINST('$product_search')
...) a ORDER BY SCORE DESC");
// Perform Query
$results = mysql_query($query);
// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!results) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}
// Store item information
$chosen_item = mysql_fetch_array($results);
But I get the error message 'Error performing query: ', do you know where i'm going wrong?
I have a similar issue which this thread helped me figure out but now I have a problem: once I get the results, how can I tell from which table the result is from? What would be the best way to determine that? Would I need to create a temporary table and add the table name as a field so I can differentiate which records are from table A or table B or is there a simpler (or faster) way I can retrieve that in PHP?
I need that because once someone click on one of the results, I would need to load that particular record in the next screen and I don't have a field that would allow me to figure out if that is a table A record or a table B record.
[RESOLVED] full text search multiple tables?
Reply With Quote
Bookmarks