Multiple Tables, Same Field Names (Array)

[Sp4rkyM4rk]

Having linked a few tables together I have come across issues where field names are the same, when using mysql_fetch_assoc(). I have a query which collects all the data from three tables, to list the articles in each category relative to their Category ID and User ID.

mysql_query('SELECT * FROM ' . USER_CATEGORY_TABLE . ', ' . USER_GROUP_TABLE . ', ' . USER_TABLE . ' WHERE ' . USER_GROUP_TABLE . '.user_group_id = ' . $user_group_id_filter . ' AND ' . USER_GROUP_TABLE . '.user_group_id = ' . USER_TABLE . '.user_group_id AND ' . USER_TABLE . '.user_id = "' . $uid_session . "')

The User Table includes the fields: 'user_group_id' and 'user_category_id', and the Group and Category tables include the same field names where approprate. However, I am unable to declare which table I want to pick information up from, when using mysql_fetch_assoc() to prepare the fields for echo'ing later on (user_category_name and user_group_name). Is there a way to do this without having to change the field names to be unique?

Just calling the table in the query results in all fields being duplicated on the page, depending on the the amount of catagories there are in the Category Table. Sadly the following doesn't work:

$variable = ['table.field'];

Thanks in advance. :)

[edited by: Sp4rkyM4rk at 7:38 pm (utc) on Aug 20, 2010]

[Anyango]

print_r your result and see what field names are being used, then pick from there.

or in the SELECT you can identify like this

SELECT table.fieldName ....

[Sp4rkyM4rk]

Aha, found the solution. I missed out an AND statement from the end, which compares the fields. This got rid of the duplicate records.

mysql_query('SELECT * FROM ' . USER_CATEGORY_TABLE . ', ' . USER_GROUP_TABLE . ', ' . USER_TABLE . ' WHERE ' . USER_GROUP_TABLE . '.user_group_id = ' . $user_group_id_filter . ' AND ' . USER_GROUP_TABLE . '.user_group_id = ' . USER_TABLE . '.user_group_id AND ' . USER_TABLE . '.user_id = "' . $uid_session . " AND ' . USER_CATEGORY_TABLE . '.user_category_id = ' . USER_TABLE . '.user_category_id')

Thanks though.