search engine on multiple tables?

[deiv82]

Hello everyone,

I'm trying to do a little search engine for my website.
Like often, I've the problem to operate on multiple tables.
This is the part of code that interests my problem. It works but I need to search in more tables than one:

for ($x = 0; $x < count($keys); $x++) {
$querystr = "SELECT img,link,numclick,description,ordine FROM $table1 WHERE description = \"$keys[$x]\" OR description LIKE \"%$keys[$x]%\" OR ordine = \"$keys[$x]\" OR ordine LIKE \"%$keys[$x]%\"";
$result = mysql_query($querystr);
(...)
 

I've tried with this method that was working in another situation but not now. Maybe because it is in a for cycle?

(SELECT *
FROM $table1)
UNION ALL
(SELECT *
FROM $table2)
UNION ALL
etc.

How can I do? :'(

[Atli]

If you want a PHP solution, it could be as simple as:

<?php
$tables = array("table1", "table2");
$keywords = array("php", "mysql", "shoelaces");
 
foreach($tables as $_table) {
    foreach($keywords as $_keyword) {
        $sql = "SELECT stuff FROM `{$_table}` WHERE other_stuff...";
    }
}
?>

You should also be able to use UNION ALL to execute those queries in larger batches. I can't see a reason why something like this wouldn't work:

(   SELECT  stuff FROM {$table1} 
    WHERE   description LIKE '%{$key}%'
    OR      ordine LIKE '%{$key}%')
UNION ALL
(   SELECT  stuff FROM {$table2} 
    WHERE   description LIKE '%{$key}%'
    OR      ordine LIKE '%{$key}%')

Which is something you could easily build using something similar to the above PHP loops.

There may be a better way to search both tables at once. If you show us how they look like, we could check it out.

Also, isn't this a tad redundant?

WHERE description = \"$keys[$x]\" OR description LIKE \"%$keys[$x]%\"

The LIKE clause would also catch what the first boolean search is looking for, which makes it a duplicate. Unless you've got some performance reasons for it? (Although, seeing as this is a description, and thus unlikely to contain only a single keyword, and the performance gain from avoiding the LIKE is doubtfully reason enough anwyas... unless your running a Google-size search engine :P).

[deiv82]

Hi thank you very much for your answer, unluckly i've seen just now...
i was just trying to use your first advice, the one of the array... it seems to be good and i don't get any error, just i don't understand why i get always only the last table in the array, for example like this

$tables = array("table1", "table2");

i get only the records of "table2".