find table name from a given record

[jgentes]

I am in the process of normalizing and re-creating a database that was a total maintanence mess. Currently the database has 640 tables. each table is an assembly and contains the data of its sub parts. Now I have created a new database that will incorporate only a hand full of tables.
1 for the part info
1 for assembly reference
1 for category info
1 for category reference
etcetc...

What I would like to do create a query that will search the old database for the table assembly part numbers, given a sub-assembly part number.

so for instance I have the part LH1890 I would like to find all of the assemblies that part belongs to, I would need the table name.

I read on the mysql forums that you can do this according to a specific column, but I need it to search the specific value of a given column....

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

[Atli]

Hi.

As far as I can see (atm) your best bet would be to create a script to search each table for the part.

Something like:

<?php
# Connect to database
$db = new mysqli("host", "user", "pword", "db");
if(mysqli_connect_errno()) 
  echo "Connection failed: ". mysqli_connect_error();
 
# Find all table names
$sql = "SHOW TABLES";
$result = $db->query($sql);
$tables = array();
while($row = $result->fetch_row()) {
  $tables[] = $row[0];
}
 
# Search each table
$searchResults = array();
foreach($tables as $table) {
  $sql = "SELECT id FROM $table WHERE part = 'partno'";
  $result = $db->query($sql);
  if($result and $result->num_rows == 1) {
    $searchResults[] = $table;
  }
}
 
# Do something else with results
print_r($searchResults);
?>