Databases
StackOverflow2013
Postsdbo
POCodeigniter active record join with 3 tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCodeigniter active record join with 3 tables
    primarykey
    data
    text
    <p>I have 3 tables,</p> <pre><code>user - contains user_id | username | fullname | email etcc user_profile - contains entry_id |user_id| profile_image_id| user_location etcc user_profile_images - contains image_id| user_id | file_path | file_thumb | date etcc </code></pre> <p>When a user sign's up, all the details go to the users table, the user_id is also added to the user_profile table with the rest set default as NULL till the user add's some profile info.</p> <p>I have a query in model_users to get all user's data from these three table and it goes like this;</p> <pre><code>$this-&gt;db-&gt;where('users.user_id', $user_id)-&gt;select('*')-&gt;from('users'); $this-&gt;db-&gt;join('user_profile', 'user_profile.user_id = users.user_id', 'left'); $this-&gt;db-&gt;join('user_profile_images','user_profile_images.image_id = user_profile.profile_image_id','left'); </code></pre> <p>It work's fine only when the profile_image_id field in user_profile is not null, that is when the user has uploaded a pic. In the case where the field is null, the user user_id is not returned even when all the other data are returned.</p> <p>I can see why this is the case, as my join query requires the field profile_image_id but currently my way around it was to set profile_image_id in user_profile as 1(default), which is the default image and has image_id as 1 and user_id as 0 as it's the general default image. But i still can't get over the fact that i need to update that query to make it less of a hack. Do you guys have any ideas? </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScomplex857
    1. USSaff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCodeigniter active record join with 3 tables
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCodeigniter active record join with 3 tables
      1. USMatt Swanson
      1. VTFavorite
    3. VO
      singulars
      1. POCodeigniter active record join with 3 tables
      1. USSaff
      1. VTFavorite
    1. COtry changing the user_profile_images join to a 'right' join?
      singulars
      1. POCodeigniter active record join with 3 tables
      1. USLaurence
    2. COThanks for the reply, it still didn't work :(
      singulars
      1. POCodeigniter active record join with 3 tables
      1. USSaff
    3. COYour best bet is to close this question, turn the query into a raw MySQL query, then post that query here on SO. There are alot of mySQL experts here - but I doubt they will know the answer to this question in its current format.
      singulars
      1. POCodeigniter active record join with 3 tables
      1. USLaurence
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload