Jump to content

Select statement


tomato

Recommended Posts

I have a table that is part of a "post a comment system" with the following fields.

commentid, pluginid, objectid, date, username, comment

The code below will return multiple entries with the same same objectid. Leaving my "latest comments" section with several entries for the same object (a forum post). This screen shot below should clarify my issue. I only need to return the last comment from the last few entries, not 3 entries from the same object if they where posted one after another. I hope I was clear explaining this.


[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ORDER BY DATE DESC LIMIT 0, $limit");

  // display comments

  while($comment = $DB->fetch_array($getcomments))
  {

      $blog = $DB->query_first("SELECT * FROM " . TABLE_PREFIX . "p819_personal_blog WHERE blog_id = '".$comment['objectid']."'"); [/code]


[img src=\"http://www.jaspersrealm.com/tmp/sql_issue.png\" border=\"0\" alt=\"IPB Image\" /]
Link to comment
Share on other sites

You'll need a subquery for this, because you can't group by first and then order by -- that is, you want to order by date desc. on unique objectid's:

[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid = ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ) ORDER BY DATE DESC LIMIT 0, $limit");[/code]
Link to comment
Share on other sites

Thanks fenway,

I didn't know you could use subqueries, How ever I'm left with an error atating that the subquery returned more than 1 row. Could you point me in the proper direction again ;)

I've found a bit more info.

[i] If the subquery returns more than one row, error 1242 will occur. In that case, the query should be rewritten as:

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);[/i]

How ever this leaves me with original issue for some reason, as if the SELECT DISTINCT isn't functioning.
Link to comment
Share on other sites

My bad -- I've not been thinking properly today, from the looks of my recent posts... I meant "IN", not "=":

[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ) ORDER BY DATE DESC LIMIT 0, $limit");[/code]
Link to comment
Share on other sites

[!--quoteo(post=356789:date=Mar 20 2006, 06:11 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 20 2006, 06:11 PM) [snapback]356789[/snapback][/div][div class=\'quotemain\'][!--quotec--]
My bad -- I've not been thinking properly today, from the looks of my recent posts... I meant "IN", not "=":

[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' ) ORDER BY DATE DESC LIMIT 0, $limit");[/code]
[/quote]

It parses fine although the select DISTINCT is not doing what it is supposed to do... Some how I'm still gettingt he same results. How is that possible?
Link to comment
Share on other sites

A generic query would surfice. I would like to see the structure as the code below has failed to produce any results from the query at all. HAVING max(date) = date returned all the entries again.



[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' GROUP BY objectid HAVING max(date) ) ORDER BY DATE DESC LIMIT 0, $limit");[/code]

BINGO!

Thank you both for helping me find the way. My host is using 4.0.25.
It's run on the test server but not the site.

[code]$getcomments = $DB->query("SELECT commentid,pluginid, objectid, date, username,comment  FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' AND objectid IN ( SELECT DISTINCT objectid FROM " . TABLE_PREFIX . "comments WHERE pluginid = '819' GROUP BY objectid HAVING max(date)= date ) ORDER BY DATE DESC LIMIT 0, $limit"); [/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.