Who can help with making a simple MySQL query?

[danest]

I have a website baker site with maybe 150 News posts, that are all enabled for comments. I would like to see what is going on in the comments. It needs a little SQL, but I will take me forever to try to figure  that out myself.

What I need is to produce a table with, say the last 30 comments, with coloumn headings like:

Time, Username, Comment heading, Post heading.

I have the module News installed, and the table mod_news_comments contain the following fields:

comment_id, section_id, page_id, post_id, title, comment, commented_when, commented_by

The field user_id from the "users" table should joined with the "commented_by" from above, so that the query can produce the "username" and "display_name" from users.

Well, there are different issues, and i am not able to make this work myself. Are there anybody willing to help me with this?

The site is:

www.hidethedecline.eu

 

[danest]

I have a website baker site with maybe 150 News posts, that are all enabled for comments. I would like to see what is going on in the comments. It needs a little SQL, but I will take me forever to try to figure  that out myself.

What I need is to produce a table with, say the last 30 comments, with coloumn headings like:

Time, Username, Comment heading, Post heading.

 

What I have managed so far by copy and paste and fiddling a little is this code:

// Specify the Group(id) you want to read the news from or 0 for all groups
$group = 0;       

// Specify number of newsitems                       
$limit=30;

// Specify read more text
$readmore = "Read more";       

// Specify Older news text
$oldernews = "Older items";       

// Query for obtaining stuff
$query = "SELECT * FROM ".TABLE_PREFIX."mod_news_comments ORDER BY comment_ID DESC LIMIT 0, 30;";

// Query for all groups 
if ($group<1 ) {
  $query = "SELECT * FROM ".TABLE_PREFIX."mod_news_comments ORDER BY comment_ID DESC LIMIT 0, 30;";
}

// No changes needed here, maybe for output!

global $database;

$result = $database->query($query);
$total = $result->numRows();       // how many items are there?

if ($total>200) {                                // at least 2 needed for 2 news items
  $data = $result->fetchRow() ;    // get left item
  $data2 = $result->fetchRow() ;  // get right item
  echo '<table width=100% border=1>';
  echo '<tr><td width=50%><b>'.$data['title'].'</b></td><td></td></tr>';
  echo '<tr><td>'.$data['content_short'].'</td><td></td></tr>';
  echo '<tr><td><a href="'.WB_URL.PAGES_DIRECTORY.$data['link'].PAGE_EXTENSION.'">'.$readmore.'</a></td><td></td></tr>';
  echo '<tr><td></td><td></td></tr>';
  echo '<tr><td></td><td></td></tr>';
  echo '<tr><td></td><td></td></tr>';
  echo '<tr><td></td><td></td></tr>';
  echo '<tr><td></td><td></td></tr>';
  echo '<tr><td></td><td width=50%><b>'.$data2['title'].'</b></td></tr>';
  echo '<tr><td></td><td>'.$data2['content_short'].'</td></tr>';
  echo '<tr><td></td><td><a href="'.WB_URL.PAGES_DIRECTORY.$data2['link'].PAGE_EXTENSION.'">'.$readmore.'</a></td></tr>';
  echo '</table>';
}

// Next show the rest
if ($total>0) {                                // are there any left?
echo '<br><br><table><tr><td colspan=2><b>'.$oldernews.'</b></td></tr>';
echo '<tr><td>'.$data['commented_when'].'</td><td>Comment title</td></tr>';
while ($data = $result->fetchRow()) { 
  echo '<tr><td>'.strftime( "%d-%m-%Y",$data['posted_when']).'</td>';
  echo '<td><a href="'.WB_URL.PAGES_DIRECTORY.$data['link'].PAGE_EXTENSION.'">'.$data['title'].'</a></td></tr>';
}
echo '</table>';
}

 

That produces this result: http://www.hidethedecline.eu/pages/links/test-sql.php

 

Besides the problems with making the JOIN queries, the above query gives wrong dates, and the links doesn't work.

[danest]

I have major technical problems at the moment. The nes module is gone from my website baker site. Can I re-install it without having to re-install the entire site?