SELECT from 3 tabels

[phpDave]

Hi,
I've been trying to SELECT from 3 tables but not sure how.
Here is the code for selecting from 2 tables

$query_Recordset3 = sprintf("SELECT * FROM mystuff.users JOIN mystuff.contact USING(user_id) WHERE contact.rec_id = '$id'", GetSQLValueString($colname_Recordset3, "text"));
$Recordset3 = mysql_query($query_Recordset3, $connAdmin) or die(mysql_error());
$row_Recordset3 = mysql_fetch_assoc($Recordset3);
$totalRows_Recordset3 = mysql_num_rows($Recordset3);

Any suggestions on how to add a 3rd table?

[diafol]

I reckon you should show your schemas and then say which fields you need to display.

[phpDave]

Here is more specific:

$query_Recordset3 = sprintf("SELECT *, contact.contact_id, contact.user_id, contact.user_url, contact.users_name, contact.other_user, contact.request, contact.rec_id FROM mystuff.users JOIN mystuff.contact USING(user_id) WHERE contact.rec_id = '$id'", GetSQLValueString($colname_Recordset3, "text"));
$Recordset3 = mysql_query($query_Recordset3, $connAdmin) or die(mysql_error());
$row_Recordset3 = mysql_fetch_assoc($Recordset3);
$totalRows_Recordset3 = mysql_num_rows($Recordset3);

I would like to add:

("SELECT image.name FROM mystuff.image");

[diafol]

We need to know the common keys in the tables so we can join then with the INNER JOIN syntax. This isn't strictly necessary (to use the INNER JOIN), but it does help when creating the correct overall statement.

So, could you include you table structure here?
e.g.

TABLE USERS
user_id | username | password

TABLE TESTS
test_id | test_name | test_max

TABLE RESULTS
test_id | user_id | result [foreign keys = test_id and user_id]

[phpDave]

TABLE USERS
user_id| first| last|email| passwd

TABLE CONTACT
contact_id| user_id| first| last| other_first| other_last| request| recId

TABLE PAGE
page_id| user_id| content| date| name| img_path |status

I've been using 'user_id' to join.

Thanks

[diafol]

OK, my example was just that, an example.

Can a user have multiple pages? If not, could the data fields be incorporated into USERS?

The CONTACT table doesn't make much sense to me. Can the contacts be outside the DB or must they be users. If they are DB users only, you don't need first/ last/ other_first/ other_last. What is the relevence of request/rec_id?

If you could include further explanation, it would help. I can't help thinking that there is a lot of duplication in your table setup.

[phpDave]

Not sure what you mean but I will try to explain a little more.
CONTACT table is to allow users of the site to connect with one and other.
first_name, last_name is of user1 and other_first, other_last is user2. User1 and user2 connect through this table. As for rec_id, that is just user2's user_id.
I got most everything to work the way I want it I just need to select from 3 tables
Hope this clears things up a little.

[diafol]

Ok, so it looks like all contacts must be members of the site. In which case, you don't need all that stuff:

TABLE USERS
user_id| first| last|email| passwd

TABLE CONTACT**
user_id| contact_id

TABLE PAGE
page_id| user_id| content| date| name| img_path |status

The contact details for a user should be taken from the USERS table.