Retrieving posts and threads then sorting by date

[MySQL_Narb]

Hi guys!

 

I have a question on how I would go about doing something. I have a table called posts and a table called threads. When a profile page is loaded, I want to query both threads and posts. I then want to join the results together. But, this is the part I have trouble with. How do I join the results together, THEN order then by their creation dates? I can easily order everything by their dates within their own tables, but when they're joined together it looks something like this:

 

Just an example of my problem

 

 

As you can see, the results are shown separate. I want to join both threads and posts together, THEN sort them by their dates. How would I go about doing this?

 

Thanks!

[Psycho]

UNION - Look it up

[MySQL_Narb]

UNION - Look it up

 

It's hard to translate someone's tone over the internet, but I'm going to take it you have an attitude towards me.

 

I've already tried UNION, but I'm not experienced in SQL. I'm not even sure it can help in this case. I've tried working with UNION, but I didn't receive any luck.

[Barand]

What problems, out of interest?

 

When using UNION, the SELECT queries must have the same number of columns and they must be of the same types since they are all going to be combined into a single resultset.

 

I added the "type" column in case you need to know which is which.

 

UNION ALL is used in case you have thread and post with same date and title.

 

SELECT 
    thread_title as title, 
    date_started as date,
    'T' as type 
FROM thread

UNION ALL

SELECT 
    post_title as title, 
    date_posted as date,
    'P' as type 
FROM post
ORDER BY 
    date DESC

[Psycho]

UNION - Look it up

 

It's hard to translate someone's tone over the internet, but I'm going to take it you have an attitude towards me.

 

I've already tried UNION, but I'm not experienced in SQL. I'm not even sure it can help in this case. I've tried working with UNION, but I didn't receive any luck.

 

No attitude - I was merely providing the solution in a succinct manner. I have no idea what your level of ability is with SQL (Are you really using SQL and not MySQL?).  There are plenty of resources out there that do a much better job of explaining it than I could ever do in a forum post. So, I suggested you "look it up". FYI: If you had provided some information regarding the actual queries I probably would have provided a revised query for your problem. Plus, it was very late and I was on my way to bed. I though that a solution, no matter how brief, was better than none.

 

If Barand's sample query does not help, then provide the two queries you are currently using.

 

<< Moving to the MySQL forum >>

[MySQL_Narb]

What problems, out of interest?

 

When using UNION, the SELECT queries must have the same number of columns and they must be of the same types since they are all going to be combined into a single resultset.

 

I added the "type" column in case you need to know which is which.

 

UNION ALL is used in case you have thread and post with same date and title.

 

SELECT 
    thread_title as title, 
    date_started as date,
    'T' as type 
FROM thread

UNION ALL

SELECT 
    post_title as title, 
    date_posted as date,
    'P' as type 
FROM post
ORDER BY 
    date DESC

 

Like I said, I only know the basics of MySQL/SQL (if there's really a difference).

 

The post field only contains the ID of the thread, not the title. I'm curious on how to do this the most efficient way with my current setup (two queries) through the use of PHP as I'm not good with joining data in MySQL.

[Psycho]

Like I said, I only know the basics of MySQL/SQL (if there's really a difference).

 

The post field only contains the ID of the thread, not the title. I'm curious on how to do this the most efficient way with my current setup (two queries) through the use of PHP as I'm not good with joining data in MySQL.

 

Barand only provided a 'sample' query based upon what would be considered a common table structure. We can't help if you aren't going to provide the information for us to do so. A proper database query is going to be different based upon the actual table structures involved. If you are not able to modify the sample query for your particular need then provide examples of the queries you are currently using.

[MySQL_Narb]

Like I said, I only know the basics of MySQL/SQL (if there's really a difference).

 

The post field only contains the ID of the thread, not the title. I'm curious on how to do this the most efficient way with my current setup (two queries) through the use of PHP as I'm not good with joining data in MySQL.

 

Barand only provided a 'sample' query based upon what would be considered a common table structure. We can't help if you aren't going to provide the information for us to do so. A proper database query is going to be different based upon the actual table structures involved. If you are not able to modify the sample query for your particular need then provide examples of the queries you are currently using.

 

$query_threads = mysql_query("SELECT `id`,`parent` FROM `threads` WHERE `username` = '$username' AND `timestamp` <> '0' AND ". time() ." - `timestamp` < '$x' ORDER BY `timestamp` DESC LIMIT 20") or die(mysql_error());
$query_posts = mysql_query("SELECT `id`,`thread` FROM `posts` WHERE `username` = '$username' AND `timestamp` <> '0' AND ". time() ." - `timestamp` < '$x' ORDER BY `timestamp` DESC LIMIT 20") or die(mysql_error());

 

 

[Psycho]

The timestamp calculation in your WHERE clauses are poor implementations. Look into the date_add() and date_sub() methods within MySQL to do a proper comparison of timestamps.

 

Anyway, give this query a try to see if the results are what you need:

$query = "(SELECT `id`, `parent` AS `title`
           FROM `threads`
           WHERE `username` = '$username'
             AND `timestamp` <> '0'
             AND ". time() ." - `timestamp` < '$x'
           LIMIT 20)

          UNION

          (SELECT `id`, `thread` AS `title`
           FROM `posts`
           WHERE `username` = '$username'
             AND `timestamp` <> '0'
             AND ". time() ." - `timestamp` < '$x'
           LIMIT 20)

          ORDER BY `timestamp` DESC";