merging results from 2 queries

[nickyfraggle]

Hello,

I am running a full text search on a database, but I want to limit the records it returns.

The full text search is run across all fields in my database, but I want it to filter the results returned by active or inactive entries.

I have put together a form and a user types their full text search word into one box,and picks to filter by active or inactive results in a dropdown. They then submit the form.


$everything=$_POST['everything'];
$active=$_POST['everythingactive'];

$results=mysql_query("SELECT id from info where match(name, surname, addr_1, addr_2, addr_town, addr_town2, addr_county, addr_pc, home, work, mobile, email, info1, info2, info3, info4) against('$everything') UNION DISTINCT select id FROM info WHERE active='$active'", $link) or die("Error finding fulltext stuff: ".mysql_error());
$rowsall=mysql_num_rows($results);


So, if active = "Yes" I want it to return all matches for the full text search where the active field is Yes, or if it's NO, I want it to filter the results so only the non active people are returned.

This is my attempt, but it's giving me back all the active or inactive clients and seems to be completely ignoring the fulltext search. The fulltext results are shown first, then it just carries on to list everything.

Can anyone point me in the right direction?

Thanks,

Nicola

[Fumigator]

I don't know why you're doing a UNION... you really just want one query, using the WHERE clause to retrieve what you need.


SELECT id
FROM info
WHERE match(name, surname, addr_1, addr_2, addr_town, addr_town2, addr_county, addr_pc, home, work, mobile, email, info1, info2, info3, info4) against('$everything')
AND active='$active'

[nickyfraggle]

Haha! Can't believe doing that never even crossed my mind! I figured that because I was doing the full text search I couldn't just add an AND bit on the end. Feeling a bit stupid now!!

Thanks for your help!

[Fumigator]

Don't feel stupid... most of the time the hangup is something simple overlooked and an extra pair of eyes does the trick :thumbsup: