[RESOLVED] full text search multiple tables?

[ToeBee]

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.
$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.

[chazzy]

are the tables related at all?

[ToeBee]

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?

[chazzy]

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.

[ToeBee]

I am using a while statement in php to display the info

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
$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.

[chazzy]

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.


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
...

[ToeBee]

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.
$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.

[chazzy]

you shouldn't call this union directly.

SELECT COUNT(*) FROM
(SELECT.... the union goes here ) a;

since it looks like you're using PHP, i'd recommend doing something like


$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);
...

[ToeBee]

Thanks Chazzy I read something similair to this but it didn't make sense. Makes sense now. Thanks again.

[sjwdavies]

Hi.

Sorry to bring back an old topic.

I'm trying to do the exact same thing here, but am experiencing errors.

The code i'm using is this:

// 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?

[chazzy]

no because the text "Error performing query: " is not in your code.

[zosorock]

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.

Thanks in advance.

Fabiano

[chazzy]

you could add a column and for each table make the value of this column the table's name.

something like


select 'tablea' as table, other_columns.... from tablea where....
union
select 'tableb' as table, other_columns... from tableb where....

[zosorock]

Thanks a million Chazzy! That worked like a charm.