multiple tables - relavent info display

djmeesh

Wondering how to do this effectively:

I have 4 tables (EXAMPLE).

1] Users: ID | NAME | USERNAME

2] Project: ID | PROJNUMBER | CONTACT

3] Time: ID | START | STOP | USERNAME | PROJECTID

4] User2Project: USR | PRJ

Relative info is Tied through TABLE 4 (User2Project) & Table 3.

Where User2Project'USR' = Users'ID'
and User2Project'PRJ' = Project'ID'

also Time'USERNAME' = Users'ID'
and Time'PROJECTID' = Project'ID'

All INTEGERS.

I dont know if this is possible, but how can I get the php to dynamically translate those values, so when I list All entries in Table 3 or 4, my USERNAMES and Projects show the 'USERNAME' or 'PROJECTNUMBER' value of the corresponding ID instead of '1,2,3,etc."

<?php do {
echo $row_TABLE3['USERNAME']; #I want corresponding $row_TABLE1['USERNAME'];
echo $row_TABLE3['PROJECTID']; #I want corresponding $row_TABLE2['PROJNUMBER'];
}
?>

did I make this question more complicated than it should be?

edwardsbc

You do this with SQL joins. Here is how you would show the users assigned to a project

$sql="SELECT users.name FROM ((users JOIN user2project ON users.id=user2project.usr) JOIN project ON user2project.prj=project.id) WHERE project.id=$yourvar ORDER BY users.name";

Depending on your SQL server the syntax may be slightly different, so please look it up in the documentation for you db system.

If you want more than one field, just list them after the select:

users.name, projects.projectnumber,...

The only trick is if you want something that is used more than once you must alias them

user.id AS uid, projects.id AS pid, ...

If you need to join on the same table more than once you will usually have to alias the tables as well, but I sure you are drinking from the firehose at this point. 🙂

djmeesh

Thanks a bunch.
I will go try it out Asap.

Glug Glug Glug--swoosh.
😃