Jump to content


Photo

Select statement


  • Please log in to reply
7 replies to this topic

#1 tomato

tomato
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 20 March 2006 - 09:14 PM

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.


$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']."'");


[img src=\"http://www.jaspersrealm.com/tmp/sql_issue.png\" border=\"0\" alt=\"IPB Image\" /]

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 March 2006 - 09:52 PM

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:

$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");

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 tomato

tomato
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 20 March 2006 - 10:11 PM

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.

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);


How ever this leaves me with original issue for some reason, as if the SELECT DISTINCT isn't functioning.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 March 2006 - 11:11 PM

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

$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");

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 tomato

tomato
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 20 March 2006 - 11:17 PM

[!--quoteo(post=356789:date=Mar 20 2006, 06:11 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 20 2006, 06:11 PM) View Post[/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 "=":

$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");
[/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?


#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 20 March 2006 - 11:54 PM

You need to use a subquery that finds the objectid with MAX(date) with a GROUP BY. I don't have the energy to write it up but I will try to later.

#7 tomato

tomato
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 March 2006 - 01:04 AM

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.



$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");

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.

$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");


#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 21 March 2006 - 04:54 AM

Yeah that's pretty close. It looks like there would be naming ambiguities with that, but if it's working, I guess it's ok.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users