CODEWITHSUNDEEP
phpmysqlsqlinner-join
captainrad
captainrad

Reputation: 3830

SQL JOIN returns query with wrong ids for each row

In my database I have a table named posts and a table named topics.

Each post has a parent topic.

In this case posts.parent_topic is a foreign key to the associated topics.id

I am attemping to do a JOIN so that my query will return the correct topics.topic_title from the topics table. I have been able to successfully do this, however I am noticing that I am no longer getting the correct posts.id for anything that is returned.

My query:

$posts = $db->query("
    SELECT
      *
    FROM
      posts
    JOIN topics ON
      posts.post_parent_topic = topics.id
    ORDER BY
      posts.created_on DESC
  ")->fetchALL(PDO::FETCH_ASSOC);

After doing a var_dump on the $posts variable I am seeing these results:

array (size=2)
  0 => 
    array (size=12)
      'id' => string '1' (length=1) // this id is CORRECT for the post
      'post_parent_topic' => string '1' (length=1)
      'created_on' => string '2015-11-03 09:30:40' (length=19)
      'post_title' => string 'Development Standards' (length=21)
      'post_content' => string 'These are the development specifc standards.' (length=44)
      'topic_title' => string 'Code Standards' (length=14)
      'topic_content' => string 'These are the company programming standards.' (length=44)
      'topic_parent_organization' => string '1' (length=1)
  1 => 
    array (size=12)
      'id' => string '1' (length=1) // this id is INCORRECT for the post, it should be an id of 2 (as it appears in the database)
      'post_parent_topic' => string '1' (length=1)
      'created_on' => string '2015-11-03 09:30:40' (length=19)
      'post_title' => string 'Design Standards' (length=16)
      'post_content' => string 'These are the design specific standards.' (length=40)
      'topic_title' => string 'Code Standards' (length=14)
      'topic_content' => string 'These are the company programming standards.' (length=44)
      'topic_parent_organization' => string '1' (length=1)

Why would each post return the same id? I need the id of the post as it is in my database.

Upvotes: 2

Views: 325

Answers (1)

Yeldar Kurmangaliyev
Yeldar Kurmangaliyev

Reputation: 34199

If you have id column in both posts and topics tables, you may need to give them aliases to make it unambigious:

Try changing the query to the following:

SELECT p.*, t.id AS TopicId, t.topic_title, t.topic_content, t.topic_parent_organization
FROM posts AS p
JOIN topics AS t 
    ON posps.post_parent_topic = t.id
ORDER BY p.created_on DESC

Upvotes: 5

Related Questions