CODEWITHSUNDEEP
mysqlsql
Johnny Catsville
Johnny Catsville

Reputation: 55

Ordering two tables by one datetime with PHP, MYSQL (PDO)

Here are some crazy issues I'm fighting with for a very long time now: I have a two tables with identical structure, let's assume they are dogs_table and cats_table

dogs_table:
-id
-dog_posts
-dog_date

cats_table:
-id
-cat_posts
-cat_date

I would like to create a timeline from dog_posts and cat_posts, similar to this one: enter image description here dog_posts should go to the left side and cat_posts to the right but they must be joined as it's one table, ordered by datetime of each one. (Either way the timeline crushes). Until now I tried using UNIONsimilar to this:

SELECT cat_posts as posts, cat_date as date, 'cats' as type from cats_table
UNION
SELECT dog_posts as posts, dog_date as date, 'dogs' as type from dogs_table
ORDER BY date desc

But this or similar UNION ways absolutely are not working to me, because the make both dogs and cats posts as one and I need to be able output a row either with dog_posts or cat_posts. So, I'm wondering if there is some better way than using UNION or maybe there is a way to achieve this result with UNION too, but I just don't know the right syntax. And I have no idea how to use JOIN too... So, any help will be highly appreciated, thanks a lot to everyone for attention.

Upvotes: 0

Views: 301

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

An alternative way of what I wrote in the comments is a full outer join. Usually I don't use full outer joins, but when I do, I use the following method: Select all distinct values from the join columns of both tables (which are dog_date and cat_date in your case). Use it in a subquery and left join the two tables on that columns.

select dogs.dog_post, dates.date, cats.cat_post
from (
    select dog_date as date from dogs_table
    union 
    select cat_date as date from cats_table
) dates
left join dogs_table dogs on dogs.dog_date = dates.date
left join cats_table cats on cats.cat_date = dates.date
order by dates.date

However - I don't know what you are going to do, if multiple rows have the same date.

Upvotes: 1

Related Questions