query search across entire database

I'm not exactly expert at php/mySQL, so forgive me if this is really
stupid. I need to create a search, whereby the user enters a search term,
and matches are returned anywhere in the database (for an internal website
search-engine)

I can create a search for data within a table ie.

select * from $table where content like '%$search%'

but how to get it to search across the entire database? or can you point to
any good tutorials for this kind of application?

TIA

--

Davémon
http://www.nightsoil.co.uk/

Re: query search across entire database

Davémon wrote:
> I'm not exactly expert at php/mySQL, so forgive me if this is really
> stupid. I need to create a search, whereby the user enters a search term,
> and matches are returned anywhere in the database (for an internal website
> search-engine)
>
> I can create a search for data within a table ie.
>
> select * from $table where content like '%$search%'
>
> but how to get it to search across the entire database? or can you point to
> any good tutorials for this kind of application?

You need to know which databases you have.
You will need to query each database to show all the tables.
You will need to query each table for columns.

You need to login/switch to each database, this can be done with a while/for loop.

Now query the tables included in the database
mysql_query("SQL SHOW TABLES")
Do the usual while-loop to get the results and do the query of tables
mysql_field_name(mysql_query("SELECT * FROM ".$row[$i]. "LIMIT 1"), $j)

When you have those, you can make a search per table for what you are looking
for, the where part has to be made with a dynamical code where you use the the
data about columns for the table as argument and which then makes the where
part of it (while or for loop can be used for this)


Better you know ahead which databases/tables/columns that is worth searching
for and make proper queries in them.


//Aho

Re: query search across entire database

J.O. Aho arranged shapes to form:

> Davémon wrote:
>> I'm not exactly expert at php/mySQL, so forgive me if this is really
>> stupid. I need to create a search, whereby the user enters a search term,
>> and matches are returned anywhere in the database (for an internal website
>> search-engine)
>>
>> I can create a search for data within a table ie.
>>
>> select * from $table where content like '%$search%'
>>
>> but how to get it to search across the entire database? or can you point to
>> any good tutorials for this kind of application?
>
> You need to know which databases you have.
> You will need to query each database to show all the tables.
> You will need to query each table for columns.
>
> You need to login/switch to each database, this can be done with a while/for loop.
>
> Now query the tables included in the database
> mysql_query("SQL SHOW TABLES")
> Do the usual while-loop to get the results and do the query of tables
> mysql_field_name(mysql_query("SELECT * FROM ".$row[$i]. "LIMIT 1"), $j)
>
> When you have those, you can make a search per table for what you are looking
> for, the where part has to be made with a dynamical code where you use the the
> data about columns for the table as argument and which then makes the where
> part of it (while or for loop can be used for this)
>
> Better you know ahead which databases/tables/columns that is worth searching
> for and make proper queries in them.
>

I thought there might be be a more straightforward SQL query that would
cover it, but thank you, it's a good indicator of what to do.

I'd better make sure that DB is designed semi-logically!

--

Davémon
http://www.nightsoil.co.uk/