Page 2 of 2

Re: Two id's LEFT JOIN AS two different array keys?

Posted: Tue Dec 16, 2008 5:49 am
by VladSun
For those willing to help JAB:
[sql]SELECT     author_user_account.username        AS author_username,     author_user_account.username_base   AS author_username_base,     last_post_user_account.username     AS last_post_username,     last_post_user_account.username_base    AS last_post_username_base,     forum_thread.id,     forum_thread.count_replies,     forum_thread.count_views,     forum_thread.date_post,     forum_thread.date_last,     forum_thread.title,     forum_post.body FROM     forum_threadINNER JOIN     user_account AS author_user_account         ON forum_thread.author_id = user_account.id INNER JOIN     user_account AS last_post_user_account         ON forum_thread.author_id_last = last_post_user_account.idINNER JOIN     forum_post         ON forum_thread.author_id = forum_post.thread_idWHERE     forum_thread.forum_id = 1     AND forum_thread.sticky = 0 LIMIT     0, 30[/sql]

Now ....
[sql]INNER JOIN     forum_post         ON forum_thread.author_id = forum_post.thread_id[/sql]
What's the authors and threads relationship?

I'm pretty sure that the result rows multiplication is because of the last join. I'm not sure what you are trying to display, but I think you will need a subselect instead of JOIN (some will argue that a GROUP BY would do the same, but I am not keen on using GROUP BY for such purposes)

Re: Two id's LEFT JOIN AS two different array keys?

Posted: Tue Dec 16, 2008 7:50 am
by Eran
He probably meant:

Code: Select all

... ON forum_thread.id = forum_post.thread_id
Since he is attempting to retrieve some post data according to thread. Just messed up the join condition a little.

Re: Two id's LEFT JOIN AS two different array keys?

Posted: Tue Dec 16, 2008 12:08 pm
by John Cartwright
VladSun and pytrin are dead on. If you did infact want to join your posts table, and still only want one row per thread returned, you can add

Code: Select all

GROUP BY forum_thread.id

Re: Two id's LEFT JOIN AS two different array keys?

Posted: Tue Dec 16, 2008 12:42 pm
by VladSun
Jcart wrote:VladSun and pytrin are dead on. If you did infact want to join your posts table, and still only want one row per thread returned, you can add

Code: Select all

GROUP BY forum_thread.id
As I said, I'm not sure what JAB wants to display. If it's the first post per thread (according to his proposed query, most probably this is the case), GROUP BY should work fine ... in MySQL ;).
But if JAB wants to display the last post per thread (which seems to me more reasonable), then GROUP BY will not do the work and a subselect should be used.

Re: Two id's LEFT JOIN AS two different array keys?

Posted: Tue Dec 16, 2008 12:44 pm
by John Cartwright
VladSun wrote:
Jcart wrote:VladSun and pytrin are dead on. If you did infact want to join your posts table, and still only want one row per thread returned, you can add

Code: Select all

GROUP BY forum_thread.id
As I said, I'm not sure what JAB wants to display. If it's the first post per thread (according to his proposed query, most probably this is the case), GROUP BY should work fine ... in MySQL ;).
But if JAB wants to display the last post per thread (which seems to me more reasonable), then GROUP BY will not do the work and a subselect should be used.
Gotcha. I strenuously forget the the first row is returned, not the last. Didn't I have a topic on this relatively recently? :)

Re: Two id's LEFT JOIN AS two different array keys?

Posted: Fri Dec 19, 2008 10:28 am
by JAB Creations
I have not been ignoring this thread as I've needed to ensure a lot more consistency and removal of redundancy in my tables. I can't yet design a whole database before beginning work on it though I certainly would like to get to that point! :mrgreen:

So I needed to get to the point where I am able to have the forum post a reply to an existing thread. I knew that if I didn't get that far before replying that I'd really end up with a mess...which I did. :mrgreen: Ok so I was struggling with the relationship between the posts and threads table. The issue was that the thread index (when you click on a forum link that displays 30 threads in example) was also showing reply posts as threads too which is obviously not very desirable!

I just now finally figured a descent relationship to build my SELECT query upon...

Code: Select all

SELECT * FROM forum_posts AS fp JOIN forum_threads AS ftON (ft.forum_thread_id = fp.forum_posts_id)  WHERE fp.forum_posts_id = ft.forum_thread_id_post
Now looking at the thread thanks to Vlad for explaining my asinine naming scheme for everyone else. :lol: pytrin was correct, I did end up making some errors on the JOIN's which I realized...that is now clearly in my mind the reason why I see duplicate results now so thankfully I know why I get duplicated returned rows. I've really been getting better with JOIN's though I certainly haven't mastered them!

Jcart, are you saying GROUP BY forum_thread.id would only return the first post row per thread row instead of requiring a relational column in the thread table to designate the first post's ID to prevent not-first thread posts as being retrieved as threads?

Vlad, I'm working on this page...
http://www.jabcreations.com/forums/

It's currently using a day old build though there was a lot of mess I've cleaned up thus far as I mentioned locally already.

What I'm going to do from here is work on rebuilding the SELECT/JOIN query now that I have some way of preventing reply (non-original posts) being returned as threads.

Then once it's working I have to implement a loop (maybe a simple while loop, not sure at the moment) to display a single thread's posts which I think should be easy enough.

So I'll be replying in maybe an hour or two with the refined query for the forum's thread index.

Thanks for your replies!

Re: Two id's LEFT JOIN AS two different array keys?

Posted: Fri Dec 19, 2008 10:31 am
by JAB Creations
Just some clarification of the terminology I'm using...feel free to correct me to a more industry standard method...

index.php - Forum Index
index.php?f=1 Forum Thread Index
index.php?f=1&t=1 Thread

Re: Two id's LEFT JOIN AS two different array keys?

Posted: Fri Dec 19, 2008 12:55 pm
by JAB Creations

Code: Select all

SELECT fp.forum_posts_body, fp.forum_posts_date, fp.forum_posts_title, ft.forum_thread_id, ft.forum_thread_count_replies, ft.forum_thread_count_views, ft.forum_thread_date_last, ua.user_username AS author_username, ua.user_username_base AS author_username_base, ua_last.user_username AS last_username, ua_last.user_username_base AS last_username_base FROM forum_posts AS fpJOIN forum_threads AS ftON (ft.forum_thread_id = fp.forum_posts_id) INNER JOIN user_accounts AS ua ON (ft.forum_thread_author_id = ua.user_id) INNER JOIN user_accounts AS ua_last ON (ft.forum_thread_author_id_last = ua_last.user_id) WHERE fp.forum_posts_id = ft.forum_thread_id_post AND forum_thread_sticky = '0' ORDER BY forum_thread_date_last DESC LIMIT 0, 30
...at the beginning of the year I had no clue I'd be writing stuff like this. Hey, when do we get to the anti-gravity boots? :mrgreen:

Here is a screenshot of the query with the results in phpMyAdmin...cleaner then copying from the MySQL console (not DOS I know but close enough!)
The image's dimensions are 1920px × 563px
http://img525.imageshack.us/img525/4551/mysqloi1.gif

Thoughts please?

I am pretty sure this will work as desired now so I'm going to comment out the existing queries and replace the thread index with this now.

Then I'm going to work on the loop to display replies when viewing an individual thread.