PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Mon Oct 15, 2018 9:02 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 6 posts ] 
Author Message
PostPosted: Thu May 07, 2015 8:09 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4425
Location: United Kingdom
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.

Syntax: [ Download ] [ Hide ]
 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.


Top
 Profile  
 
PostPosted: Thu May 07, 2015 9:05 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4425
Location: United Kingdom
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.


Top
 Profile  
 
PostPosted: Thu May 07, 2015 9:11 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4425
Location: United Kingdom
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.

Syntax: [ Download ] [ Hide ]
 $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.


Top
 Profile  
 
PostPosted: Thu May 07, 2015 9:13 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6424
Location: Montreal, Canada
What do the table schemas look like?

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Thu May 07, 2015 9:17 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6424
Location: Montreal, Canada
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.

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Thu May 07, 2015 9:19 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4425
Location: United Kingdom
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group