1 Topic by nickfzx (edited by nickfzx 2007-08-22 01:37)

  • From: Oxford, UK
  • Registered: 2006-05-11
  • Posts: 133

Topic: A challenge to someone who knows SQL

you know on forums.php and viewforum.php it says:

"last bost by <username>"

well I want to have it as "last post by <avatar><username>"

so it shows the user's avatar (if they have one) next to their username in forums.php and viewforum.php

I think it would really liven up the page a lot.  (i am going to use phpThumb to generate small (around 40x40 pixels) versions of avatars when people upload them)

So the username of the last poster in any given forum is stored in the column last_poster in forums table.

So to get the avatar all I need is the last_poster's ID.  To do this I think I need to LEFT JOIN with the users table on username like this:

LEFT JOIN forums ON users.username = forums.last_poster

But beyond that I am having difficulty.

I came up with this that doesn't really work:
SELECT users.id FROM users LEFT JOIN forums ON users.username = forums.last_poster

basically I am a little stuck at how to approach the query...any thoughts?


p.s.
As for the generating mini versions of avatars on the fly...I should be able to figure this one out on my own...I will post instructions once I have it working.

Punbb w/coppermine and wordpress integrated

see my hack to integrate punbb with wordpress comment system.
Illustration Community

nickfzx's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: A challenge to someone who knows SQL

Moved to Modifications
You'll want to work based off of the actual queries, not with fake queries. And you'll need to add a LEFT JOIN with the users table on the last_poster (which will be a slow join, it might be worthwhile to add a new column for the user ID of the last poster and populate that instead).

  • From: Oxford, UK
  • Registered: 2006-05-11
  • Posts: 133

Re: A challenge to someone who knows SQL

Smartys wrote:

Moved to Modifications
You'll want to work based off of the actual queries, not with fake queries. And you'll need to add a LEFT JOIN with the users table on the last_poster (which will be a slow join, it might be worthwhile to add a new column for the user ID of the last poster and populate that instead).

I thought about adding a new table...and this may be a better idea...but I will still need to populate this table for all the old (inactive topics)...so I will have to do this join at some point no matter what.

So I guess I would like help with an sql query to populate a newly created last_posters_id column  in the topics and forums table...by joining last_poster with username on the users table.

Punbb w/coppermine and wordpress integrated

see my hack to integrate punbb with wordpress comment system.
Illustration Community

nickfzx's Website

  • From: Oxford, UK
  • Registered: 2006-05-11
  • Posts: 133

Re: A challenge to someone who knows SQL

I have made the mod to reate mini avatars when someone uploads an avatar...and also created the new last_poster_id columns in the forums and topics tables.

I need some help populating those columns...it will need to be a one time left join with the users table on the usaneme...any ideas what the query might look like...might need some php in there too I think.

thanks, Nick

Punbb w/coppermine and wordpress integrated

see my hack to integrate punbb with wordpress comment system.
Illustration Community

nickfzx's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: A challenge to someone who knows SQL

update topics set last_poster_id=(select users.id from users where username=last_poster)

  • From: Oxford, UK
  • Registered: 2006-05-11
  • Posts: 133

Re: A challenge to someone who knows SQL

Smartys wrote:

update topics set last_poster_id=(select users.id from users where username=last_poster)

wow that seem really simple...I was thinking up complex loop ideas and crap...thanks, i'll give it a try and let you know how I get on

Punbb w/coppermine and wordpress integrated

see my hack to integrate punbb with wordpress comment system.
Illustration Community

nickfzx's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: A challenge to someone who knows SQL

That has a couple limitations:
it only works on MySQL 4.1 or higher (or any other system that supports subselects, postgresql might)
it assumes no database prefixes