[SOLVED] Question on getting/using info from 2 tables (part 2)

[smerny]

http://www.phpfreaks.com/forums/index.php/topic,256921.0.html

 

I'm new to these forums and I see that was marked as solved so I guess I'm supposed to make a new topic?

 

Anyway, I was given a code that worked great at what I wanted... until I clicked into a different board and noticed that the topics existed there when they shouldn't have.

 

This is what I originally had:

 

$search = "SELECT * FROM topics WHERE ID_board='".$board_id."' ORDER BY ID DESC";

 

But I wanted to be able to search by the time of the last post within the topic... so that the topics with new posts would be at the top.

 

$search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime
FROM topics
LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub
ON topics.ID_board = postSub.ID_board
ORDER BY postSub.LatestPostTime DESC";

 

This is the code that works great except that it doesn't exclude topics where the ID_board is not equal to $id_board... I don't fully understand the JOINs and Sub and such, otherwise I might be able to make that work myself.

[adamlacombe]

Im not really sure how to solve your problem exactly but you should probably be using:

$search = "SELECT * 
FROM topics
LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub
ON topics.ID_board = postSub.ID_board
ORDER BY postSub.LatestPostTime DESC";

 

the asterisk(*) selects all rows, makes it a little bit easier to understand.. I think.

[smerny]

that selects all the columns rather than certain ones, which i don't need

 

i need to weed out all the topics that don't have $id_topic = ID_topic

[adamlacombe]

so something like:

WHERE $id_topic='0'

Maybe... Im really new to SQL.

[adamlacombe]

oh never mind i used a $ in the query, that wont work.

sorry

[smerny]

I'm sorry, I meant $id_board = ID_board in the topics db table...

 

my original code did that

$search = "SELECT * FROM topics WHERE ID_board='".$board_id."' ORDER BY ID DESC";

 

but I don't know how to incorporate that with the new code that works for sorting by the last post to have the ID_topic of each topic

[kickstart]

Hi

 

You can add a WHERE clause to check the board ID. Something like this:-

 

$search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime
FROM topics
LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub
ON topics.ID_board = postSub.ID_board
WHERE ID_board='$board_id'
ORDER BY postSub.LatestPostTime DESC";

 

As to using SELECT *, this is not really a good idea for maintenance reasons. If the php code after it referred to a column by its number then if someone modified the table in the future, using SELECT * might well make the column number change.

 

All the best

 

Keith

[smerny]

I tried that also, go this error:

 

SQL Error: Column 'ID_board' in where clause is ambiguous

[kickstart]

Hi

 

Ooops:-

 

$search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime
FROM topics
LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub
ON topics.ID_board = postSub.ID_board
WHERE topics.ID_board='$board_id'
ORDER BY postSub.LatestPostTime DESC";

 

All the best

 

Keith

[smerny]

ok, that works... i've never done more than basic mysql queries really, can you explain the process/order that the computer handles your query so that I will be able to apply this in the future?

[kickstart]

Hi

 

OK, I will try.

 

Firstly it should do the subselect:-

 

SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board

 

This will get each ID_board and the max posttime for each one. The GROUP BY means that the MAX returned is for each group of ID_board.

 

It will then get all the topics from the post table.

 

It will then join these topics with the number of posts from each topic, so giving one row for each topic with also the matching fields for the number of posts. It is the ON clause that is used to join the sets together.

 

The WHERE clause narrows the results down to only be for the matching ID_board.

 

The ORDER BY then sets the order of the returned records.

 

All the best

 

Keith

[smerny]

thanks again