Creating a List of Bloggers on an Install That's Dependant on Newest Node Created

By nikkiana on 9 Jun 2006 at 16:30 UTC

I'm banging my head on MySQL queries and I sorta wondered if someone smarter than me could tell me where I'm messing up....

On the main page of my site is a list of the bloggers that post on my site (to see an example, go here: http://www.conmon.com/drupal. The code that generates this is this:

global $user;

result = db_query(db_rewrite_sql("SELECT * FROM {users} u"));
while ($user_info = db_fetch_object($result)) {
  if ($user_info->uid) {
    profile_load_profile($user_info); //load the profile for that user
    if ($user_info->profile_blog_title) {
      print '<div class="odder-user">';
      if($user_info->picture) {
        print '<img src="/drupal/'.$user_info->picture.'" height=50 align=left>';
      }
      if ($user_info->profile_blog_title) {
        print '&nbsp;' . l($user_info->profile_blog_title, "blog/$user_info->uid");
      }
      else {
        print '<div class="fields">&nbsp;' . l('Suggest a name for my blog', "blog/$user_info->uid").'</div>';
      }
      print '<div class="fields"><font size="-1">&nbsp;by '.l($user_info->name, "user/$user_info->uid").'</font></div>';
      if ($user_info->profile_blog_description) {
        print '<div class="fields"><font size="-1">&nbsp;'.$user_info->profile_blog_description.'</font></div>';
      }
      else {
        print '<div class="fields"><font size="-1">&nbsp;A description of my blog will appear here when I have one</font></div>';
      }
      print '</div>';
    }
  }
}

As it defaultly is, the blogs are defaultly listed in order of their user ID.

We'd like the blogs to display in order that they were most recently updated, but the problem I'm having is when I change the MySQL query to be based off of the created field in the node table, blogs in the list end up getting listed multiple times because the query is looking at more than just the newest node created by each user.

What's happening is say Joe created the newest node on the site, he created one before that and then John created one before him, and Mary created two before him, and then Joe created one before her. The list ends up being like this:

Joe
Joe
John
Mary
Mary
Joe

The way I want the list to come out is like this..

Joe
John
Mary

I want the query to ONLY look at the newest node created by each user so each user is only displayed in the list once.

My MySQL is weak. Someone help!

Comments

I cannot speak to the overall approach

laura s commented 9 June 2006 at 17:05

But using DISTINCT in your SELECT will prevent duplicate results. Hope this helps.

You might look at the php Snippets for ideas on syntax and ways to leverage the functionality Drupal core code already provides. It can make a big difference in terms of security and performance.

Also be sure to follow the views.module's development. This is a wonderful tool for custom lists and the like.

Laura
_____ ____ ___ __ _ _
design, snap, blog

_____ ____ ___ __ _ _
Laura Scott :: design » blog » tweet

That's what I thought about

nikkiana commented 12 June 2006 at 16:41

That's what I thought about SELECT DISTINCT... but in some of the queries I tried, it didn't seem to make a bit of difference whether I used SELECT DISTINCT or SELECT...

I really wish I'd saved all the different queries I tried...

I'd rather use the views.module to do it, personally... but even after reading through the documentation, I'm still feeling totally lost on how views is supposed to work and where I ought to start... I've been told that it would solve several other problems I'm having...

nikkiana
everytomorrow.org - blogsNH

Here the Blogger module

drupalnesia commented 22 September 2006 at 14:42

Hi, I just release Blogger module. Blogger module used for:
1. Display list of Bloggers. You can limit how many Bloggers displayed.
2. Display number of written blogs or not.
3. A "read more link" to show all Bloggers.

download here: http://drupal.org/project/blogger

The closest I've come to

nikkiana commented 13 June 2006 at 16:12

The closest I've come to getting what I want is this:

SELECT DISTINCT u.uid, u.name, u.picture, n.created FROM {users} u, {node} n WHERE n.uid = u.uid AND n.type = 'blog' ORDER BY n.created DESC

You'd think that select distinct would only select the first time each user occurred, but it doesn't... regardless of whether I use select or select distinct I get the same exact query results.

nikkiana
everytomorrow.org - blogsNH