Joining Tables

2 posts by 2 authors in: Forums > CMS Builder
Last Post: December 3, 2012   (RSS)

By ILLUME-MEDIA-LABS - December 3, 2012

Hello...

Need some help.

I have 2 tables - User_Profiles & User_Submissions. The common denominator is user_id, which can be used to link both tables.

The way the sections function:

I have an iphone app that uses fb authentication to register users if they want to submit content. On initial sign on, a web service checks to see if the user has an account. If there is not account, it creates on and pulls all user data from fb and writes to the use_profiles table.

The user can then post content, and it writes that data to the user_submissions table. In both tables, the unique identifier & link is the user_id.

What I need to do is link both tables with the user_id, and then pull the user thumbnail image from the user_profiles section and display is when the content from the user_submissions is pulled.

I hope that makes sense. Thanks

Re: [Illume Magazine] Joining Tables

By gregThomas - December 3, 2012

Hi,

I think you can use the leftJoin setting in the getRecords function to retrieve the data from the user_submissions table and user_profiles table at the same time, something like this should work:

// load records from 'shop_categories'
list($user_profiles, $profilesMetaData) = getRecords(array(
'tableName' => 'user_profiles',
'loadUploads' => true,
'allowSearch' => false,
'leftJoin' => array(
'user_submissions' => 'ON user_profiles.user_id = user_submissions.user_id'
)
));

So the leftJoin array pulls secondary data from the user_submissions table, where the user_profiles user_id value matches the user_submissions user_id value.

Alternatively a simpler way to do it might be to have two getRecords functions that pull the data from each table individually, using something like this:

$userID = 43; //Set the user_ID value to this variable

// load records from 'user_submissions'
list($user_submissions, $user_submissionsMetaData) = getRecords(array(
'tableName' => 'user_submissions',
'loadUploads' => true,
'allowSearch' => false,
'where' => "user_id = '$userID'"
));

// load records from 'user_profiles'
list($userProfiles, $userProfilesMeta) = getRecords(array(
'tableName' => 'user_profiles',
'loadUploads' => true,
'allowSearch' => false,
'where' => "user_id = '$userID'"
));


Thanks

Greg
Greg Thomas







PHP Programmer - interactivetools.com