Seraching inside all tables

Felixr · Jan 26th, 2008, 15:30

Hi,
I need to find the names of all the tables that have a value in a field.
For example, There are 5 tables: t1, t2, t3, t4, t5
and each one has 6 fieldsm, one of them is f4.
I need the name of all the tables that has "Felix" as a value in the f4 field.

How do I do that?

I basicly need something that will work like this incorrect piece of code:

SELECT * FROM users.* /* users is the database, * meand all tables */ WHERE `name`="Felix"

PHP MySQL.

Thank you.

Jack Franklin · Jan 26th, 2008, 15:35

Would:

Code: Select all

SELECT * FROM users WHERE name='Felix'

Not work? Have you tried that? I'm only just starting out with MySQL so I cannot be sure.

alexgeek · Jan 26th, 2008, 23:08

No Jack, that would only search one table.

Jack Franklin · Jan 27th, 2008, 09:11

Code: Select all

 
(Connect to your db) and then:
 
SELECT * FROM table1, table2, table3, etc (add your tables here) WHERE name='Felix'

So in PHP:

PHP: Select all


$sql = "SELECT * FROM table1, table2, etc WHERE name='Felix'";
if ($sql) {
echo 'all done';
} else {
echo 'error';
}

Try that?

Remembering first you need to connect to the database:

PHP: Select all


# DataBase Settings
$hostname_config = "localhost";     # 99% Leave Localhost
$database_config = "";   # Database Name
$username_config = "";   # Database Username
$password_config = "";    # Password 
$conn = mysql_connect($hostname_config, $username_config, $password_config) or trigger_error(mysql_error(),E_USER_ERROR); mysql_select_db($database_config)or die(mysql_error());

Rakuli · Jan 27th, 2008, 12:55

I think he's asking for a way to get the table name containing the value rather than get the results themselves.

The query might look something like this

Code: Select all

SELECT t1.f4 AS table1, t2.f4 AS table2, t3.f4 AS table3, t4.f4 AS table4, t5.f4 AS table5  
FROM t1 
LEFT JOIN t2 ON (t1.f4=t2.f4) 
LEFT JOIN t3 ON (t2.f4-t3.f4)
LEFT JOIN t4 ON (t3.f4=t4.f4)
LEFT JOIN t5 ON (t4.f4=t5.f4) 
WHERE t1.f4 = 'felix' OR t2.f4 = 'felix' OR t3.f4 = 'felix OR t4.f4 = 'felix' OR t5.f4 = 'felix'

That query above will give alarge result set but basically the columns it returns will be named table1-5 respectively. It's then a matter of checking if any of the rows return have a value of felix under table-15

Felixr · Jan 27th, 2008, 13:08

Thanks, but I meant that there are way to many tables for my to just enter their names...
I need to search inside all of them.