Connecting two tables

[shuban]

I have two tables that contain two pieces of information that I need.

Currently, my query looks like this:

Code: [Select]

$limit = 8;

$dbresult = db_query("
SELECT
subject, posted_time, blog_category_id, article_id, blog_id
FROM smf_blog_articles
        ORDER BY posted_time DESC
LIMIT {$limit}", __FILE__, __LINE__);
$i = 1;
while ($row = mysql_fetch_row($dbresult))
Notice only smf_blog_articles table is being used above. smf_blog_articles, the first image, doesn't possess information about categories name, but does provide information about the blog_id. Category names that correspond to the blog_id's can be found in the table smf_blog_categories (the second image). I want to combine smf_blog_categories with smf_blog_articles into the query above so that they are joined so that the category name appears.

How do I do this?

[SMFHacks]

Something like this for join. Assumed blog_id is the category that was are joningin on

Code: [Select]

SELECT
a.subject, a.posted_time, c.blog_category_id, a.article_id, a.blog_id, c.name
FROM smf_blog_articles as a, smf_blog_categories as c 
WHERE b.blog_category_id = a.blog_id
        ORDER BY a.posted_time DESC
LIMIT {$limit}


[shuban]

Thank you, SMFHacks. Here's the final code that works

Code: [Select]

$limit = 8;

$dbresult = db_query("
SELECT
a.subject, a.posted_time, a.article_id, a.blog_id, a.blog_category_id, c.name, c.blog_category_id, c.blog_id
FROM smf_blog_articles as a, smf_blog_categories as c 
WHERE a.blog_category_id = c.blog_category_id
        ORDER BY a.posted_time DESC
LIMIT {$limit}", __FILE__, __LINE__);

$i = 1;
while ($row = mysql_fetch_row($dbresult))
{
    list ($subject, $posted_time, $article_id, $blog_id, $blog_category_id, $name) = $row;

echo '
<span class="logreg">
<a href="http://biology-forums.com/index.php?article=',$article_id,'">
<div class="midtext" style="background: url(http://biology-forums.com/Themes/default/images/post/xx.gif) left center no-repeat; padding-left: 4ex; font-weight: bold;">
',$subject,'
</div>
</a>
</span>
<div style="text-align: right;">
<a class="capsule round5px smalltext" href="http://biology-forums.com/index.php?blog=',$blog_id,';category=',$blog_category_id,'">',$name,'</a>
</div>

<div style="', $i != $limit ? 'width: 80%; margin: 0 auto 1ex auto; border-bottom: 1px dashed #C0C0C0; padding-bottom: 1ex;':'padding-bottom: 0.5ex;','"></div>';
$i++;
}

[shuban]

I have another case study I'm having trouble with!

Here's the code that works perfectly fine, but as the code depicts, it orders the messages based on ID number.

Code: [Select]

$request = db_query("
SELECT
b.ID_BOARD, b.name AS bname, c.ID_CAT, c.name AS cname, m.ID_TOPIC, m.ID_MEMBER, m.ID_MSG, IFNULL(mem.realName, m.posterName) AS posterName,
t.ID_MEMBER_STARTED, t.ID_FIRST_MSG, t.ID_LAST_MSG, m.body, m.smileysEnabled,
m.subject, m.posterTime
FROM ({$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}boards AS b)
LEFT JOIN {$db_prefix}categories AS c ON (c.ID_CAT = b.ID_CAT)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
WHERE m.ID_TOPIC = t.ID_TOPIC
AND m.thank_you_post = 1
AND t.ID_BOARD = b.ID_BOARD" . (empty($range_limit) ? '' : "
AND $range_limit") . "
AND $user_info[query_see_board]
ORDER BY m.ID_MSG " . ($reverse ? 'ASC' : 'DESC') . "
LIMIT $start, $maxIndex", __FILE__, __LINE__);

What I would like to do is join a new table called smf_thank_you_post, which also has a column called ID_MSG and a column called thx_time (thx_time possesses the time). Ultimately, I want to sort these messages by their time, but I'm having so much trouble doing it What can I do to this code to ORDER BY by thx_time?

[SMFHacks]

Do you want messages that only have thk_time?

[shuban]

Actually, the table smf_thank_you_post doesn't contain the message, but it contains the msg_id, among other things. In fact, the msg_id found in that table correspond to the msg_id's found in the messages table. I just want to connect them in such a way that I can use their thx_time column to sort everything out.

[shuban]

Due to the natural of the mod that uses smf_thank_you_post, there are multiple msd_id's that are the same (since more than one person can thank you the same message). How will this affect the joining of tables?