Linking tables?

[sser]

Hi group,

 

My MySQL server version is 6.

Basically, I've created a database with multiple tables and I'm trying to link/cross-reference some of the tables based on a specific index. All tables will have an ID associated with them and these IDs will be used to link them. Is it possible to achieve this?

 

Thanks!

[fenway]

Define "link" and "this".

[sser]

I'm pretty much stuck in the assigning ids to tables stages. So far, I have:

 

//I have all the files pushed to an array called $all_file

for($i=0; $i<count($all_file); $i++){

  $test_table = basename($all_file[$i], ".dat");

  $newstring = substr($test_table, 6, 9);

echo $newstring;

  $query = "CREATE TABLE `$test_table` (field1 INT, field2 CHAR(20) NOT NULL, field3 CHAR(50) NOT NULL)";

  mysql_query($query);

 

//each table needs to have a unique identifier based on the filename

  alter table '$test_table' add primary key ($newstring);

  $loadsql = "LOAD DATA LOCAL INFILE '$all_file[$i]' INTO table `$test_table` FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' ";

  mysql_query($loadsql);

}

 

I would like all tables to be associated with a key based on the filename of their associated files. But, of course I can't get the code working with the alter table function.

Any help would be greatly appreciated.

[fenway]

I'm sorry, I still don't understand.  You want to include a FK reference to the file from all other tables?

[sser]

Well basically, every table in the db will have a certain identifier such that the tables with the same identifiers can be cross-referenced. Does this make sense?

 

Thanks for the help

[beebum]

sser,

 

It sounds like you are asking strictly an SQL question so it may be easier to leave out the PHP for now and just focus on the database structure and queries.

[fenway]

Well basically, every table in the db will have a certain identifier such that the tables with the same identifiers can be cross-referenced. Does this make sense?

 

Thanks for the help

Sort of... can you give an example?

[sser]

As an example, I have tables such that:

a001

a002

b001

b003

b004

etc..

 

I'm trying to link tables that have similar last 3 digits, in this particular case  cross-reference a001 and b001.

I'm not sure if this is possible to make in php?

[fenway]

Forget about php for a minute... why do you have tables like this? What do they represent?

[sser]

they are person id numbers(ie a001), last digits in the table name (ie 001) representing a certain info about the people...

in every table there's more info regarding person id numbers.

[fenway]

I don't understand... what information does a001 contain vs. b001?

[sser]

a001 and b001 contain information from different people(person a and person b); but they have the same job hence 001.

I hope this makes more sense.

[beebum]

I think I'm starting to get an idea of your business logic but I think you've chosen the wrong db logic to support it. I don't think I've ever seen table names used like that. I've seen people use a third table to help handle a many to many relationship scenario. Maybe that is where you are wanting to go with this.

[fenway]

I agree... you need a single table with job_uid and person_uid fields.

[sser]

Sorry for my late reply,

Thanks everybody for your suggestions.