Joining with mysql

pinehead18

I have two tables, i'm trying to pull information from. One named topics and one named threads. I want my results to display the last 10 rows between the two tables. How might i go about that?

Thank you
Anthony

tsinka

Hi,

do you have an auto_increment column in topics ? How do the tables look like ?

Thomas

pinehead18

Topics:
tid - autoicniment
forum_id
subject
body
author
date

Threads:
id - autoincriment
tid (ties the topic to the thread)
subject
author
body
forum_id
date

Thank you
Anthony

pinehead18

any ideaS?

Roger_Ramjet

I was about to answer this, then realised that what you are probably after is the last 10 threads for EACH topic. Am I right?

If that is what you are after then I don't think it is possible with MySQL, unless you are using ver 5 and can therefore use a stored procedure.

pinehead18

No, i need the last 10 out of both of the two table's put together. So, their will only be 10 results in my script from the two tables put together. Not 10 from each so their would be 20 results.

Thank you again.

Anthony

Roger_Ramjet

I meant: do you need the last 10 entries for each value in topics, eg if there are 5 topics you would get back 50 rows, 10 threads for each topic.

If not then a simple 'LIMIT 10' at the end of you query will return just 10 rows regardless of how many topics and threads there are.

SELECT * FROM topics, threads
WHERE topics.tid=threads.tid
LIMIT 10

pinehead18

right, all i need is the total of last 10 rows out of the two tables. basical the topics and threads but the last ten things that where posted on my forum.

Roger_Ramjet

That is what the LIMIT 10 does. Use ORDER BY threads.id DESC to get the last 10 entries.

pinehead18

Thanks man, i'll give that a try when i get home. You have been A TON of help.

Anthony

pinehead18

Wont this script only show the recent posts where their is a reply to? So where their is a result with the same tid in threads and a result with the same tid in topics?

tsinka

Hi,

use a left join instead:

SELECT * FROM topics LEFT JOIN threads 
ON topics.tid=threads.tid 
ORDER BY topics.tid DESC, threads.id DESC
LIMIT 10

In the result set the threads columns will be NULL if no matching record could be found in threads for a given topic.

Thomas

tsinka

Hi,

reading this a second time that might not be the correct one since it doesn't take in account older threads that have more recent replies. A UNION query might do the trick.

Thomas

pinehead18

Well, do you have any suggestions how to use a union or a link to some examples? I dont' exactly have any clue how to make it LOL

Thank you again for your help

tsinka

Hi,

try something like this:

(SELECT tid,forum_id,subject,body,author,date FROM threads)
UNION ALL
(SELECT tid,forum_id,subject,body,author,date FROM topics)
ORDER BY date DESC
LIMIT 10

You might need to tweak it a little bit but something like this should work.

If you need the threads id:

(SELECT tid,id,forum_id,subject,body,author,date FROM threads)
UNION ALL
(SELECT tid,0 AS id,forum_id,subject,body,author,date FROM topics)
ORDER BY date DESC
LIMIT 10

The results with id=0 are the ones taken from topics.

Thomas

pinehead18

Normally i woulnd't post this much but since i'm flying in the dark i need a little extra help this time.

function recent_forum_posts() {

db_connect('pinehead');

$sql = "(SELECT author,subject,tid FROM threads) UNION ALL (SELECT tid,0 AS id,forum_id,subject,author FROM topics) LIMIT 8";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {

    $subject = $row['subject'];
    $tid = $row['tid'];  
$author = $row['author'];
       if (strlen($subject) > 15) {
                    $filler = "...";
                    $subject = substr($subject, 0, 15 - $filler) . $filler;
            }
    print "&nbsp;&nbsp;<a href=/forums/viewtopic.php?tid=$tid&fid=$fid>$subject</a><br>&nbsp;&nbsp;&nbsp;By: $author";   
    print "<br><br>";
 }
}

That is my code,

The used SELECT statements have a different number of columns

that is the error.

what does that tid,0 mean as well?

Thank you so much for your help on this one.

This is the last bit of code till my site is completed and its a pain lol

tsinka

Hi,

the number of fields selected should be the same for both queries.

More important:
The selected fields should be of the same type in both queries so make sure that you select the threads fields in the same order as the topic fields.

I used 0 AS id because there is no id column in topics.

This query will select rows from both tables (without ALL it would be a DISTINCT select) and order the combined result set by date (descending) and fetch the 10 (or 8 in your case) top most records.

So basically this query selects all topics and all threads and sorts them by date. This query will fetch both topics with and topics without matching records in threads.

Note:

It will fetch the threads without the data of the corresponding topic record. If you want to get that data, too, you need to use LEFT JOINS in both SELECT queries.

Thomas

pinehead18

works great, thank youi