GroupBY, Join advice needed, to show one prod, multiple coms

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

GroupBY, Join advice needed, to show one prod, multiple coms

Post by simonmlewis »

Hi

I've been asked to compile a page that shows all comments for each product, between a given set of dates.
So you might select June to August, and it will show each product that has comments, and their comments. The product details needs showing just once, with the comments next to that info.

At the moment I have it so that it shows each comment, and the photo - for every comment though!

I did think about a Join, but that would also show the photo each time it finds a comment.
There are over 2000 products so obviously I don't want it to be going thru all the products each time the page loads.

It's run by Admin, so there is no "spike" concerns.

Here is the code at the moment. Maybe it's a simple one. So you have the photo on the right, and then all the comments assigned to that product on the left.

Code: Select all

 echo "<div class='blockheader'>Comments for this period</div>";
  $result = mysql_query ("SELECT * FROM usercomments WHERE email LIKE '%@%' AND (dateentered BETWEEN '$datefrom' AND '$dateto') ORDER by dateentered DESC ");
			while ($row = mysql_fetch_object($result))
      {
      echo "<div class='awards_comments'>";
        
        $resultp = mysql_query ("SELECT photoprimary, title, catname, subname FROM products WHERE id = '$row->prodid'");
        while ($rowp = mysql_fetch_object($resultp))
        {
        echo "<div class='awards_product'><img src='/images/productphotos/small/$rowp->photoprimary' /><br/>
        $rowp->title<Br/>
        $rowp->catname</div>";        
        }
      echo "<div class='awards_comment'>$row->dateentered ($row->prodid, $row->prodname)<br/>$row->nickname<Br/>$row->email<Br/>($row->id) $row->usercomments</div>
      <div style='clear: both' /></div>
      </div>";
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GroupBY, Join advice needed, to show one prod, multiple

Post by simonmlewis »

Have tried this too, but the same result.
For each entry, it shows a photo. Don't know how to group them by the product, without having it scan thru ALL products first.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GroupBY, Join advice needed, to show one prod, multiple

Post by simonmlewis »

Unless there is a better way, I think this does it.
It first finds a product that has comments based on the date criteria, and then uses that to find the comments.

Code: Select all

 $result = mysql_query ("SELECT photoprimary, title, catname, subname, p.id AS pid FROM usercomments AS uc JOIN products AS p ON prodid=p.id  WHERE email LIKE '%@%' AND (dateentered BETWEEN '$datefrom' AND '$dateto') GROUP BY pid ORDER by dateentered DESC");
			while ($row = mysql_fetch_object($result))
      {
      echo "<div class='awards_comments'>
      
      <div class='awards_product'><img src='/images/productphotos/small/$row->photoprimary' /><br/>
          $row->title<Br/>
          $row->catname</div>";

  $resultc = mysql_query ("SELECT * FROM usercomments WHERE prodid = '$row->pid' AND email LIKE '%@%' AND (dateentered BETWEEN '$datefrom' AND '$dateto')  ORDER by dateentered DESC");
			while ($rowc = mysql_fetch_object($resultc))
      {          
          
          echo "<div class='awards_comment'>
      <form method='post' action='/a_commentawards'>
      <input type='hidden' name='update' value='setstar'>
      <input type='hidden' value='$rowc->id' name='id'>
      <input type='submit' value='Assign Star'>
      </form><br/>
      $rowc->dateentered ($rowc->prodid, $rowc->prodname)<br/>$rowc->nickname<Br/>$rowc->email<Br/>($row->pid) $rowc->usercomments</div>
      <div style='clear: both' /></div>
      ";
      
      }
      echo "</div>";}
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: GroupBY, Join advice needed, to show one prod, multiple

Post by Celauran »

What do the table schemas look like?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: GroupBY, Join advice needed, to show one prod, multiple

Post by Celauran »

You definitely want to use a JOIN there. Executing a query inside a loop is a good indication you're doing it wrong. This creates the so-called n+1 problem; if you have 50 products, you'll be running 51 queries where 1 would actually suffice.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GroupBY, Join advice needed, to show one prod, multiple

Post by simonmlewis »

Exactly!
I had a moment of weakness there... and then it came to me. If I can find all products that have the comments within the given dates, then I Can use that data instead to then query the usercomments table. Thus, only one product image, and all the comments for that product. Works nice.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply