Issue with a SQL query

Discussion forum for MOD Writers regarding MOD Development.
Locked
RalphChadkirk
Registered User
Posts: 37
Joined: Sat Feb 13, 2010 1:28 pm

Issue with a SQL query

Post by RalphChadkirk »

Hi all,

I don't quite know where to post this, so feel free to move it :)

I'm working on a website where the news page grabs a feed from the forum news section. That works fine.
The problem I am having is that I want to show who posted each post, so I built this SQL query:

Code: Select all

SELECT phpbb_users.username FROM phpbb_users, phpbb_posts WHERE phpbb_users.user_id='$posterid'
I've set the variable $posterid above with the posterid grabbed from the posts database.

It seems to work partly - i.e. there is only one post in there at the moment and it gets that username correctly. However, when you add another post from a different username, all the posts displayed on the news page all take that username.

Any ideas?
User avatar
RMcGirr83
Former Team Member
Posts: 22016
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Issue with a SQL query

Post by RMcGirr83 »

Can you post your query for the posts table?
Former Modifications/Extensions Team Member | My extensions | github | All requests for support via PM will be ignored
Appreciate the extensions/mods/support then buy me a beer Image
User avatar
lucky89
Registered User
Posts: 145
Joined: Wed Apr 04, 2007 1:54 pm
Location: Buenos Aires, Argentina
Name: Lucas

Re: Issue with a SQL query

Post by lucky89 »

Ok, i dunno if I understand well, but I think this query can solve your problem.

If you are showing the post, you must get the post id too. So the query will be:

Code: Select all

sql = "SELECT `username` FROM `phpbb_users`, `phpbb_posts` WHERE `phpbb_posts`.`poster_id` = `phpbb_users`.`user_id` AND `phpbb_posts`.`post_id` = " . $post_id . ";" 
This variable

Code: Select all

$post_id 
is the variable, you should get from your other query.
[/b]
User avatar
RMcGirr83
Former Team Member
Posts: 22016
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Issue with a SQL query

Post by RMcGirr83 »

The reason I asked to see the original query is because you can probably left join on the users table to get the information and save yourself a query as the posts table stores the users id in it and you can join off of that.
Former Modifications/Extensions Team Member | My extensions | github | All requests for support via PM will be ignored
Appreciate the extensions/mods/support then buy me a beer Image
User avatar
lucky89
Registered User
Posts: 145
Joined: Wed Apr 04, 2007 1:54 pm
Location: Buenos Aires, Argentina
Name: Lucas

Re: Issue with a SQL query

Post by lucky89 »

I don't post a JOIN query because i dunno the other query. But what RMcGirr83 say is true. You can get the post and the username with one single query.
RalphChadkirk
Registered User
Posts: 37
Joined: Sat Feb 13, 2010 1:28 pm

Re: Issue with a SQL query

Post by RalphChadkirk »

Hi,

Thanks for the quick responses!

The query for the posts table gets all the information from the posts table (filtered by the forum ID):

Code: Select all

SELECT * FROM phpbb_posts WHERE forum_id = 24 ORDER BY post_id DESC

lucky89 - Thanks for the query. I tried it, and it gets the username for one post fine. However, all the posts displayed on the page then take the most recent posters username as well - my problem as outlined in my first post.
User avatar
RMcGirr83
Former Team Member
Posts: 22016
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Issue with a SQL query

Post by RMcGirr83 »

Code: Select all

SELECT p.*, u.username 
	FROM phpbb_posts p
	LEFT JOIN phpbb_users u ON p.poster_id = u.user_id
	WHERE p.forum_id = 24 
	ORDER BY p.post_id DESC
should allow you to pull the appropriate username for whomever made the post.
Former Modifications/Extensions Team Member | My extensions | github | All requests for support via PM will be ignored
Appreciate the extensions/mods/support then buy me a beer Image
RalphChadkirk
Registered User
Posts: 37
Joined: Sat Feb 13, 2010 1:28 pm

Re: Issue with a SQL query

Post by RalphChadkirk »

Thanks for that, I shall test it now!
RalphChadkirk
Registered User
Posts: 37
Joined: Sat Feb 13, 2010 1:28 pm

Re: Issue with a SQL query

Post by RalphChadkirk »

Sir, I take my hat off to you! Works fine!
User avatar
RMcGirr83
Former Team Member
Posts: 22016
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Issue with a SQL query

Post by RMcGirr83 »

Please post up exactly how you are using it in your code. In fact, just post up your code all together.
Former Modifications/Extensions Team Member | My extensions | github | All requests for support via PM will be ignored
Appreciate the extensions/mods/support then buy me a beer Image
RalphChadkirk
Registered User
Posts: 37
Joined: Sat Feb 13, 2010 1:28 pm

Re: Issue with a SQL query

Post by RalphChadkirk »

If you're referring to the SQL error I thought I had - don't worry - fixed it. Accidentally put a space where their shouldn't be a space.
User avatar
RMcGirr83
Former Team Member
Posts: 22016
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: Issue with a SQL query

Post by RMcGirr83 »

Yeah, I guess we were cross posting. Glad it works for you. :)
Former Modifications/Extensions Team Member | My extensions | github | All requests for support via PM will be ignored
Appreciate the extensions/mods/support then buy me a beer Image
Locked

Return to “[3.0.x] MOD Writers Discussion”