MySQL PHP Search

[kumschick]

Hi,

I'm trying to create a search page for out website.
How can I search for a word in many tables & cells at the same time.

If I want to seach in $table1, $table2, $table3, $table4, etc... what do I have to do?

$table1 = "news";
$table2 = "products";
$table3 = "support";
$table4 = "content";

select * from $table1, $table2, $table3 where title='$searchafter' or model='$searchafter' or content='$searchafter'

But With this I get an error: [Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\www\terra.lu\users\beta\search.php on line 20]

[Sxooter]

Are these tables all basically the same? If so, then a union would be a good way to do it:

select from table1 where field like '%abc%'
UNION
select from table2 where field like '%abc%'
UNION
...

But I'm not sure if / when MySQL started supporting unions, and you didn't mention what version you're running.

The way you're trying to do it won't work because it will create a cartesian product (i.e. every row of every table multiplied by every row from each of the other tables...)

If you don't have unions, then select the data into a temporary table and use that:

select into temp_table1 from table1 where field like '%abc%';
select into temp_table1 from table2 where field like '%abc%';
select into temp_table1 from table3 where field like '%abc%';
select from temp_table1 order by some_field;