CODEWITHSUNDEEP
phpmysqljoin
Havihavi
Havihavi

Reputation: 672

join two tables order by date and display results

I'm trying to make a function kinda similar to facebook's notification window. Their function seems to combine any kind of event and display them after date.

So i have 2 tables : Article and comments. both have 2 similar rows : uniquepostowner and date_posted.

I want to join them up and display them one after another. Example : 

User A has posted a comment (04.05.2012 5:30)
User B Has posted a comment (04.05.2012 6:30)
User C has written an article (04.05.2012 7:30)
user D has posted a comment (04.05.2012 8:30)

However, i'm struggling with both joining these 2 and displaying the results. Reading others with similar scenarios ive tried this as a query:

$sesid = $_SESSION['user_id'];
$sql = "SELECT X.* FROM ( SELECT * FROM `article` WHERE uniquepostowner = {$sesid} UNION SELECT * FROM `comments`  WHERE uniquepostowner  = {$sesid} ) X ORDER BY X.`date_posted`";
$result = mysql_query($sql);

Then trying to fetch the results like this:

while($row = mysql_fetch_array($result))
{
echo $row['article.id'];
 }

Tables:

**article**:
id
title
content
uniqueuser
date_posted
full_name
uniquepostowner

**comments**:
id 
pid (same as article.id)
date_posted
content
full_name
uniquepostowner

there are more rows to these tables, but they are irrelevant. But no success. Any help is greatly appreciated as always! :)

Currently i'm trying this, but its not doing what i want.

$sql = "SELECT * FROM article a INNER JOIN comments c WHERE a.uniquepostowner = {$sesid} AND c.uniquepostowner = {$sesid} 
ORDER BY a.date_posted DESC , c.date_posted DESC ";

Upvotes: 1

Views: 1769

Answers (4)

Zagor23
Zagor23

Reputation: 1963

I think this is what you need:

UPDATED:

$sesid = $_SESSION['user_id'];
$sql = "
SELECT 
    X . *
FROM
    (SELECT 
        id,
        date_posted,
        content,
        full_name,
        uniquepostowner,
        'article' AS type
    FROM
        article
    WHERE
        uniquepostowner = {$sesid} UNION SELECT 
        id,
        date_posted,
        content,
        full_name,
        uniquepostowner,
        'comment' AS type
    FROM
        comments
    WHERE
        uniquepostowner = {$sesid}) X
ORDER BY X.date_posted
";
$result = mysql_query($sql);

And you can iterate with:

while($row = mysql_fetch_array($result))
{
        if($row['type']=='article'){
            echo "User " . $row['full_name'] . " has written an article (".$row['date_posted'].")<br>";
        } else {
            echo "User " . $row['full_name'] . " has posted a comment (".$row['date_posted'].")<br>";
        }
}

You can check it out at:

http://sqlfiddle.com/#!2/38778/8

Upvotes: 3

ZorleQ
ZorleQ

Reputation: 1437

Maybe I'm missing the point, but wouldn't this solve the problem:

SELECT  articles.*, comments.* FROM  articles, comments WHERE articles.uniqueuserid = $userId AND comments.postId = articles.Id ORDER BY articles.date_published DESC

Ofc all table and field names are would need adjustments to fit with your code. What it does, is basically it loads all articles with uniquepostowner or whatever your name is and corresponding comments sorted by publish date.

Is this what you are after?

EDIT Actually, comments and articles are spearate, and you want to display them side by side? In that case why don't you create 'events' table, which holds the date, the owner, the type (article/comment/like/photo/anything) and id of corresponding item and use that to generate your feed?

Upvotes: 0

Luis Specian
Luis Specian

Reputation: 104

You must make the collumns on each query of the union equal:

SELECT X.* FROM ( SELECT **uniquepostowner** FROM `article` WHERE uniquepostowner = {$sesid} UNION SELECT **uniquepostowner** FROM `comments`  WHERE uniquepostowner  = {$sesid} ) X ORDER BY X.`date_posted`

This is just a example, in the union, both queries must return the same amount of fields, I sugest that you return only the filds that both tables have in commom.

Upvotes: 0

Jan Prieser
Jan Prieser

Reputation: 1528

you should try to rephrase your sql to only use the columns both tables have in common:

SELECT uniquepostowner,date_posted FROM `article` WHERE uniquepostowner = {$sesid} 
UNION 
SELECT uniquepostowner,date_posted FROM `comments` WHERE uniquepostowner  = {$sesid} 
ORDER BY date_posted

there are some more examples in the mysql manual.

Upvotes: 2

Related Questions