homepage Welcome to WebmasterWorld Guest from 54.163.72.86
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Multiple Tables, Same Field Names (Array)
Sp4rkyM4rk




msg:4189981
 7:29 pm on Aug 20, 2010 (gmt 0)

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




msg:4189983
 7:33 pm on Aug 20, 2010 (gmt 0)

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




msg:4189985
 7:44 pm on Aug 20, 2010 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved