[Resolved] Combining results.

SureFire

I have a homemade search engine that I made because I couldn't figure any of the other ones out..For key words I have 10 seperate fields with one key word per field. when someone does a search everything works, but I have 10 different queries(one for each keyword field) so they don't show up in alphabetical order..

Is there a way combine all the results into one result and ORDER BY ASC?

Or is there a way to make a single keywords field and have a simple query that can seperate the words?

Thanks in Advance, you guys have already been an immense help!

http://www.digitalvideofuel.com is where this is at if you need to see it, go to sounds then do a search..

LordShryku

Well, you could have them seperate the keywords in one field with the word AND, then do something like this.

$sql = "SELECT id, description FROM table WHERE id IS NOT NULL";
$keys = strtoupper($Keywords_required_minlength3);
$akeys = explode(" AND ", $keys);
for($a=0;$a<count($akeys);$a++) {
    if($a == 0) {
        $results = "\"".substr($akeys[$a],0,1).strtolower(substr($akeys[$a],1))."\"";
    }
    else {
        $results .= " and \"".substr($akeys[$a],0,1).strtolower(substr($akeys[$a],1))."\"";
    }
    $sql .= " AND (upper(description) LIKE '%".$akeys[$a]."%')";
}
mysql_query($sql);

Or, you could do all the queries, push them into a new array, and use the sort function to re-sort them.

Sxooter

You probably want to write code something like this:

select *
from table
where
(field1 like '%$token1%' OR field2 like '%$token1%')
AND
(field1 like '%$token2%' OR field2 like '%$token2%')
order by field1;

If you have multiple fields to search.

If it's just one field, then just make it a big ORed set:

select *
from table
where
field1 like '%$token1%' OR field1 like '%$token2%';

SureFire

Looks like two good responses..Very much appreciated guys!

SureFire

thanks guys! I ended up using scooters for the sound effect search, but in another search I used the first reply to go through a whole document for keywords! Thanks alot!