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:
[code=php]<?php
$tables = array("table1", "table2");
$keywords = array("php", "mysql", "shoelaces" );

foreach($tables as $_table) {
foreach($keywor ds as $_keyword) {
$sql = "SELECT stuff FROM `{$_table}` WHERE other_stuff..." ;
}
}
?>[/code]

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:
[code=sql]( 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}%')[/code]
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?
[code=sql]WHERE description = \"$keys[$x]\" OR description LIKE \"%$keys[$x]%\"[/code]
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".