48707

Codeigniter join repeats returned values

Question:

Okay first sorry if its a stupid question but im a really big beginner.

I have 3 database tables, videos, events, and users.

My problem is if i join these 3 tables and showing the results in my view it duplicates.

For example if i echo out the users name in my profile , and i uploaded 7 videos it shows the name seven times.

function get_profile($id) { $this->db->select('*'); $this->db->from('pf_users'); $this->db->join('pf_videos', 'pf_videos.uid = pf_users.uid'); $this->db->join('pf_events', 'pf_events.uid = pf_users.uid'); $this->db->where('pf_users.uid', $id); $q = $this->db->get(); if($q->num_rows() > 0) { foreach($q->result() as $row) { $data[] = $row; } return $data; } else { show_404(); } }

i even repeats the menus

<ul class="profile_menu"> <?php foreach ($results as $res): ?> <li><a href="#" class="profile_info">Információ</a></li> <li><a href="#" class="profile_event">Eseményei (<?php echo count($res->title) ?>)</a></li> <li><a href="#" class="profile_photos">Party képei (0)</a></li> <li><a href="#" class="profile_video">Videói (<?php echo count($res->url); ?></a></li> <?php endforeach; ?>

could please someone point out what i am missing? and sorry if its a stupid question.

thank you

Answer1:

It is normal that you will have duplicate content as you are missing the group by statement. In your situation you need to add:

$this->db->group_by("pf_users.uid");

Now you have both joint pf_videos and pf_events and the normal thing is to have duplicate content as you have both information about pf_events and pf_videos . If you though don't need both information a simple DISTINCT will do the job for you, so you can simply have

$this->db->select('DISTINCT pf_users.*',false);

without using the group_by statement. To learn more about DISTINCT and GROUP BY you can simply read the following links:

<a href="http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html</a>

<a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html</a>

Recommend

  • PHP code displaying as comments in browser using WAMP [closed]
  • How to call mysqli_stmt with call_user_func_array?
  • How to read email using Python 3
  • SecurityException Permission Denial MediaProvider READ_EXTERNAL_STORAGE
  • Kubernetes 1.7 on Google Cloud: FailedSync Error syncing pod, SandboxChanged Pod sandbox changed, it
  • Displaying Data From Multiple MySQL Tables
  • GCC Inconsistent compilation error 'has no member named ' [duplicate]
  • How to Update model and database with code first approach in Asp.net MVC
  • How to calculate total across columns but one?
  • firebase, how to update data at a key
  • Attemping to change attributes of $(this) inside success function
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • XSLT foreach repeating nodes to flat
  • List images(01.png) and descriptions(01.txt) from directory
  • rspec simple example getting error on request variable in integration test
  • How can I extract results of aggregate queries in slick?
  • Needing to do .toArray() to get output of mongodb .find() on key name not value
  • D3 nodes and links from JSON with nested arrays of children
  • MongoDB in PHP using aggregate to group by _id is null not working
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • align graphs with different xlab
  • php design question - will a Helper help here?
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • AngularJs get employee from factory
  • How do you join a server to an Active Directory (domain)?
  • embed rChart in Markdown
  • Turn off referential integrity in Derby? is it possible?
  • How does Linux kernel interrupt the application?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4
  • Can't mass-assign protected attributes when import data from csv file
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • How can I use `wmic` in a Windows PE script?
  • UserPrincipal.Current returns apppool on IIS
  • Unable to use reactive element in my shiny app
  • Conditional In-Line CSS for IE and Others?