CODEWITHSUNDEEP
phpsqlcodeigniter
Luke Bream
Luke Bream

Reputation: 865

Codeigniter SQL Join query is not returning the data I want

I have the following query using CodeIgniter. I want a list that includes a single row for each group and I want the row to inlcude the liveBlogGroupRecord status if it is "1".

Right now, I either get only rows for groups that have liveBlogGroupRecord=1 or, if I comment that line out, I get multiple group rows from the same group since the blogGroups table has multiple old values in it that have liveBlogGroupRecord=0.

$this->db->select('*');
$this->db->from('groups');
$this->db->join('blogGroups', 'groups.groupID = blogGroups.groupID', 'left');
$this->db->where('blogGroups.blogID', $blogID);
$this->db->where('blogGroups.liveBlogGroupRecord', 1);
$query = $this->db->get();

Upvotes: 1

Views: 508

Answers (2)

Seabass
Seabass

Reputation: 1983

Be careful with SELECT *. Assuming your blogGroups and groups tables both have an id (or another column that with the same name) only the first one will be returned when you're JOINing another table.

Selectively choose which columns you need. Try something like this:

$this->db->select('groups.*');

/* And more selectively, whichever elements you have from `blogGroups` that you need (I have no idea what those are) */
$this->db->select('blogGroups.name');
$this->db->select('blogGroups.description');
$this->db->select('blogGroups.category');
/* etc */

Upvotes: 1

dejitaru
dejitaru

Reputation: 41

how about adding $this->db->limit(1); before $query = $this->db->get();

Upvotes: 0

Related Questions