Joining multiple tables returning 0 for specific column? PHP/MySQL

Question

I'm having a problem at the moment where I have a column called rating in the links table and there is definitely values other than 0 within the column but 0 is the only value which is returned foreach link. When I do a simple get for that column it then shows all the other values but not when I do an SQL Join.

I know the problem is my joining of the tables but I'm unsure how I would go about joining these specific tables.

Database Table Structure

The rating column is the one which is causing me problems.

'link_ratings' id | user_id | link_id | rated | date_created

Model:

public function get_latest(){

    $this->db->limit(100);
    $this->db->order_by('links.date_created', 'DESC');
    $this->db->select('*');
    $this->db->select('links.id as current_link_id');
    $this->db->from('links');
    $this->db->join('list', 'links.list_id = list.id'); 
    $this->db->join('users', 'links.user_id = users.id');
    $this->db->join('link_ratings', 'links.id = link_ratings.link_id','left');      

    $get_latest = $this->db->get();

    return $get_latest;

}

Any Help is appreciated.

Nil'z · Accepted Answer · 2013-11-01 14:29:48Z

1

You should try this:

function get_latest(){
    $this->db->select('list.*, users.*, links.id as current_link_id');
    $this->db->from('links');
    $this->db->join('list', 'links.list_id = list.id'); 
    $this->db->join('users', 'links.user_id = users.id');
    $this->db->join('link_ratings', 'links.id = link_ratings.link_id','left');      
    $this->db->order_by('links.date_created', 'DESC');
    $this->db->limit(100);
    $get_latest = $this->db->get()->result_array(); #fetch all rows here
    echo "<pre>";print_r( $get_latest );die; #print all rows and see if its fetching ratings corrctly or not.
    echo $this->db->last_query();die; #check the query generated
    return $get_latest;
}

edited Nov 1, 2013 at 14:29

answered Nov 1, 2013 at 14:05

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

learn Over a year ago

I maybe wasn't clear. I am retrieving data but it is the rating column within the links table which is causing me problems. Foreach time I loop through in my view it simply shows 0 for the rating index rather than the numbers which are in the database for that specific row. My joins are the problem.

learn Over a year ago

Any ideas on how I should construct my joins?

Nil'z Over a year ago

Have you seen the resultant array structure you are getting? see above and try it once.

learn Over a year ago

Yes I've already had a look and it shows all the correct keys/columns but link rating values are all set to 0. Any suggestions on what join queries may work?

learn Over a year ago

Any idea what it could be?

Matt Saunders · Accepted Answer · 2013-11-01 15:38:34Z

0

The reason will be purely logical, in that the join will be causing no results to be returned because there are no results. I've fallen into this many times.

I am not able to diagnose your particular problem but when faced with issues like this I:

1- turn on the CI profiler

2- var_dump the array so you can see what's going on

3- write a traditional SQL query and run it in PHPMyAdmin

One, or a combination of all three, will enable you to diagnose.

answered Nov 1, 2013 at 15:38

Matt Saunders

4,4307 gold badges35 silver badges49 bronze badges

Collectives™ on Stack Overflow

Joining multiple tables returning 0 for specific column? PHP/MySQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related