--- Tedit kap <[EMAIL PROTECTED]> wrote:

> Thanks. Two more questions based on this:
>  
> 1) I have to select from eight similar tables (table1, table2 table3
> etc..) like this. How do you select form that many table, what is the syntax?
> Do you repeat the whole select queary and use UNION eight times or how? I
> tried commas in the query but it didnt work. (table1, table2, table3) If
> there is a shortcut of writing instead of joining eight queries with UNION,
> is one way more efficient than the other?
>  
> 2) Can you use OR statement if you were to select from more than two columns?
> Such as article title, articlebody and additionalcolumn? If so how? Do you
> just place another OR so there are two ORs now? I tried and it looks like it
> works, but dont have enough data in my db yet to be sure....

If you have an arbitrary number of tables which must have the same query
applied and the result collated together, you should use UNION.  If you build
the query in PHP, you can make a loop through an array of table names and keep
the code fairly clean.  Of course, you have to ask yourself why there are 8
similar tables instead of having the data in 1.  Poor database design can
really kill your performance and increase the difficulty of your programming. 
Sometimes there are reasons for this complexity.  Keep in mind that in your
loop, you will want to keep track of which data had the result so you can go
back to it.

$tables = array("table1", "table2", "table3");
$q      = array();
foreach ($tables as $table)
{
 $q[] = "SELECT * FROM $table WHERE articletitle LIKE '%$search%' OR " 
      . "articlebody LIKE '%search%' OR additionalcolumn LIKE '%search%'";
}
if (is_array($q))
{
 $query = join(" UNION ", $q);
 # Add any ORDER BY or LIMIT clauses needed.  May need parens around each
SELECT
 # send the query, etc.
}

And, yes, you can multiple OR statements per my example.

With 8 tables and 3 search columns, this no longer fits most people's
definition of a "basic search."

James

Reply via email to