[SOLVED] wednesday teaser

[jonniejoejonson]

Hello this is going to seem a little complicated...

becuase it is...

 

I am trying to create a forum: My posting tables is as follows:

 

postId | threadId | catId | userId | postSubject | post | dateTimePosted

 

 

I need to retrieve the most recent posts from the above table.

However they must be the most recent posts that are grouped by threadId and have a catId of say '3'

 

Of the rows that are selected i also want to a select the rows that have a postId that equals the threadId of the originally selected rows... 

 

... Oh yes and i also need to join these results to my users table based on the userId

 

... any ideas much apreciated...

what i have done so far doesn't work so i wont even post it... unles anyoneis interested in how not to do it!...

regards to any respionders... and congratulatiuons to anyone that can visualise what i am trying to do. J

[Zane]

what i have done so far doesn't work so i wont even post it... unles anyoneis interested in how not to do it!...

regards to any respionders... and congratulatiuons to anyone that can visualise what i am trying to do. J

 

yes..post it...because otherwise..

it will be a constant guess as to what you're doing..even though we may already have a good idea, showing what you're already doing and explaining helps tremendously and helps YOU learn..if not others as well

 

EDUT

Of the rows that are selected i also want to a select the rows that have a postId that equals the threadId of the originally selected rows... 

 

and this bares repeating.....whut??

[kickstart]

Of the rows that are selected i also want to a select the rows that have a postId that equals the threadId of the originally selected rows...

 

I take it from this you want the posts that are the first posts in a thread. However do you only want those posts or do you want a way to highlight those posts?

 

All the best

 

Keith

[jonniejoejonson]

Hey guys... thanks for the responses...

 

I am trying to get the most recent and the orignial posts of threads that have a categroyId of '3'.

I am also trying to get the user info of the person that posted the most recent post and the info of the person that posted the original post.

 

If we try and break it down...

 

how would you select the most recent postId of a select statement with a GROUP BY clause...

 

eg...

 

$sql = "SELECT

postId

FROM posts

WHERE categoryId='3'

GROUP BY threadId";

 

I want the most recent post in the threadId?... how do you do that?

 

Your continued support is much appreciated.

 

 

 

 

 

 

[Zane]

well...for the most recent and the original post...all you'd do is order by the datetime after you have grouped them by the threadid....and walla...grab the top one for the original and last one for the most recent

 

I think that's where you're headed..

 

what's the category have to do with it....I'd assume that is the forum itself? like the "3rd" forum category and such...but why would you search on forum categories when you already have the thread id.

 

I would think you would just have a table full of ALL the threads no matter what category..

and put the category column in the thread table.

[Zane]

I am trying to get the most recent and the orignial posts of threads that have a categroyId of '3'.

 

You want EVERY thread's original and recent!??

 

Now for just ONE thread... say thread 70

SELECT postId FROM posts

ORDER BY dateTime DESC

WHERE threadId = 70

 

and then do what I said from there

[jonniejoejonson]

Thanks zanus..

 

Correct i want the original post and most recent post of every post with category Id 3

 

This is tough to explain... but the ORDER BY clause doesn't work becuase it is ordering the the results that have already been selected... it doesn't order them before the GROUP BY clause is put into action... therefore i am always getting the first result in the GROUP BY clasue not the most recent...

 

I am trying to create the same as this page:

http://www.phpfreaks.com/forums/index.php/board,3.0.html

It displays the 20 most recent posts in that forum board.

It tells you the most recent post title. The posters name etc.

It also displays the original subject.

Instead of having different boards i've called them categries

 

thanks J

[jonniejoejonson]

Sorry the post above was written before i saw your posting.. so i don't know how applicable it is...

also with what you have written... you don't appear to be able to do an ORDER BY before a WHERE clause.

 

Anyway insetad if we just continue to look at this smaller problem i might be able to work it out...

 

$sql = "SELECT

postId

FROM posts

WHERE categoryId='3'

GROUP BY threadId";

 

I want it to select the most recent post in the GROUP BY threadId?... how do you do that?

 

cheers J.

 

[kickstart]

Hi

 

I would try something like this:-

 

SELECT a.*, b.*

FROM  (SELECT threadId, MAX(postId) AS maxPostId FROM ForumTable GROUP BY threadId) Deriv1

INNER JOIN (SELECT threadId, MIN(postId) AS minPostId FROM ForumTable GROUP BY threadId) Deriv2 ON Deriv1.threadId = Deriv2.threadId

INNER JOIN ForumTable a ON Deriv1.threadId = a.threadId AND Deriv1.maxPostId = a.postId

INNER JOIN ForumTable b ON Deriv2.threadId = b.threadId AND Deriv2.minPostId = b.postId

ORDER BY a.postId DESC

LIMIT 20

 

(probabyl a few typos, but hope you get the idea).

 

Basically do a select to get the max post per thread and another for the min posts per thread (you could possibly merge these into one if you wanted), then join the results to the original table to get the rest of the data, once for the min post and once for the max post.

 

Then order it and put a limit on (I have used a.postId to get the 20 threads with the most recent posts).

 

All the best

 

Keith

[jonniejoejonson]

Hey kick start i've not fully looked at your response yet.. but are you saying becuase postId increments by 1 each time, that the larger the postId the more recent the post?....

 

Also is there not a simple way to do the following.. that actually oreders the GROUPBY by postDateTimeCreated.

 

$sql = "SELECT

postId

FROM posts

WHERE categoryId='3'

GROUP BY threadId

ORDER BY postDateTimeCreated ASC";

 

this doesn't actually order the results before the GROUP BY, which is what i need...

 

thnaks for you time... regards J

[kickstart]

Hi

 

I am assumign that the larger the postId the more recent the post.

 

Never tried using a GROUP BY on a column that isn't being returned, and logically not sure you should be able to. Not really that sure what you are trying to do with that statement. I guess you want a postID for each threadId, but the postId will be random.

 

This seems to be what you want:-

 

$sql = "SELECT threadId, MAX(postDateTimeCreated)

FROM posts

WHERE categoryId='3'

GROUP BY threadId";

 

and then link that back to get the relevant postId.

 

If postId is in postDateTimeCreated order (which would appear logical if postId is a numeric auto increment key) then it is easier (especially as there is the possibility that postDateTimeCreated isn't unique).

 

All the best

 

Keith

[jonniejoejonson]

Hey Keith thanks for all of that... im afraid that your code pulls exactly the same results as mine did... it isn't grouping the data correctly...

i have opened a new thread that maybe you may took a look at... hopefully it better explains what i am trying to do... kind regards J

[jonniejoejonson]

postIdthreadIdpostpostDateTime

11this is my post 2009-09-07 11:14:45

22 this is my post okay 2009-09-07 12:14:00

31 this is my post continued 2009-09-08  13:13:00

41another post 2009-09-08  15:00:00

52more of a post 2009-09-08  16:00:00

62post, post post, post post. 2009-09-09  17:00:00

 

 

$sql = "SELECT postId FROM posts GROUP BY threadId ORDER BY postDateTime DESC";

 

Okay the above code pulls

row 1-> postId = 1

row 2-> postId = 2

 

It should however get the most recent postId of the grouped threadId

row 1-> postId = 4

row 2-> postId = 6

 

 

[cbolson]

If I am not wrong it is grouping by the first threadid that it finds.

 

Try this:

$sql = "SELECT postId, MAX(postDateTime) FROM posts GROUP BY threadId ORDER BY postDateTime DESC";

 

Chris

[Zane]

please don't start new topics for the same question...

[jonniejoejonson]

Okay sorry Zanus... I just thought it was getting a little confused and this is kinda a seperate thing too how it started.. anyway...

 

cbolson... you have indeed found the problem, but sadly not the solution

[cbolson]

Bear in mind that I answered to the new thread that you had started.  I wasn't aware of the previous discussion.

 

Can you do a table structure and data dump so that I can recreat it and see why it isn't working as expected.

 

Chris

[kickstart]

$sql = "SELECT postId FROM posts GROUP BY threadId ORDER BY postDateTime DESC";

 

Okay the above code pulls

row 1-> postId = 1

row 2-> postId = 2

 

It should however get the most recent postId of the grouped threadId

row 1-> postId = 4

row 2-> postId = 6

 

I think you are misunderstanding things. The order by is ordering the results, not data half way to your results.

 

What it has brought back is post id for each thread id. The post id that is returned for each thread id is effectively a random one. Logically could be the lowest or the highest post id. You haven't specified and MySQL has no defined reasoning to decide which to give you.

 

The order by is merely acting on the resulting 2 random post ids and giving you them in order of postDateTime.

 

Using your data I knocked up a test table. The SQL I listed earlier appears to give exactly what you want. A Row for each thread with the first and latest posts (complete post rows) for each of them:-

 

SELECT a . * , b . * 
FROM (
SELECT threadId, MAX( postId ) AS maxPostId
FROM posts
GROUP BY threadId
)Deriv1
INNER JOIN (

SELECT threadId, MIN( postId ) AS minPostId
FROM posts
GROUP BY threadId
)Deriv2 ON Deriv1.threadId = Deriv2.threadId
INNER JOIN posts a ON Deriv1.threadId = a.threadId
AND Deriv1.maxPostId = a.postId
INNER JOIN posts b ON Deriv2.threadId = b.threadId
AND Deriv2.minPostId = b.postId
ORDER BY a.postId DESC 
LIMIT 20

 

All the best

 

Keith

[jonniejoejonson]

Thanks kickstart/ keith,

Please bare with me... becuase as you may have noticed im no expert...

 

When i break you sql statement into 2 seperate stretements.. one for the max and one for the min, i am able to get it to work... however if you could show me how you extract the data from the result.. as i think that is where im going wrong: i have:

 

$sql_result = mysql_query($sql,$connection);

 

$n=0;

while ($row = mysql_fetch_array ($sql_result)){

$postId[$n]=$row["postId"];

$threadId[$n]=$row["threadId"];

etc...

}

 

 

sorry for my confusion... regards Jonathan....

 

p.s. thanks Chris.. but i think that Keith has the answer i just need a little more help! regards J

 

[kickstart]

Hi

 

Leave them joined up. No reason really to split them.

 

For example, this code fragment would give you a list of the first and latest posts for the last 20 threads to have a post:-

 

$sql = "SELECT a.threadId AS curThreadId, Deriv1.maxPostId, Deriv1.minPostId, a.post AS MaxPostText, b.post AS MinPostText, a.postDateTime AS MaxPostDateTime, b.postDateTime AS MinPostDateTime
FROM (SELECT threadId, MAX( postId ) AS maxPostId FROM postsGROUP BY threadId)Deriv1
INNER JOIN (SELECT threadId, MIN( postId ) AS minPostId FROM posts GROUP BY threadId )Deriv2 ON Deriv1.threadId = Deriv2.threadId
INNER JOIN posts a ON Deriv1.threadId = a.threadId AND Deriv1.maxPostId = a.postId
INNER JOIN posts b ON Deriv2.threadId = b.threadId AND Deriv2.minPostId = b.postId
ORDER BY a.postId DESC 
LIMIT 20";

$sql_result = mysql_query($sql,$connection);

while ($row = mysql_fetch_array ($sql_result))
{
echo "Thread ".$row["curThreadId"].", first post at ".$row["MinPostDateTime"]." saying ".$row["MinPostText"]." , last post at ".$row["MaxPostDateTime"]." saying ".$row["MaxPostText"]."<br />";
}

 

All the best

 

Keith

[jonniejoejonson]

Keith 2 words... leg-end ... that is very much appreciated...

thanks also to everyone else too for their help.

 

p.s. how can you mark something as the answer?.. cos keith has the answer.

[Zane]

bottom left of screen