current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

I am just begining php and mysql. I have two queries from my database. First one queries a table called lifestyles then does a foreach loop and outputs each item on its row. Now each of these rows have commenting boxes for members to post comments about them. the comments table has associative column called postid for the lifestyle rows. So, I did another foreach loop inside the previous one to display these comments below each row of lifestyle. The problem is that each comment is replicated the number of times of each lifestyle query. See below:

Title:  TEST

Date:   28th-Jun-2011 at 10:10 PM

TEST LIFESTYLE POST

Comments

TEST COMMENT

TEST COMMENT

TEST COMMENT

TEST COMMENT

TEST COMMENT

TEST COMMENT

TEST COMMENT

TEST COMMENT

TEST COMMENT

TEST COMMENT

TEST COMMENT

and the code:

<?php

        $lifestyles=dbAll("select * from user_accounts, lifestyle where lifestyle.category=1 and lifestyle.user_id=user_accounts.id order by cdate DESC");

        if(!$lifestyles){
            echo '<em><strong>No Posts yet. Why don\'t you be the first!</strong></em>';
        }
        else{

            foreach($lifestyles as $lifestyle){
                $lifestyleid=(int)$lifestyle['id'];
                echo '<form class="postform" method="post" action="shopping.php?redirect='.$_SERVER["PHP_SELF"].'">';
                echo '<input type="hidden" name="postshop" value="1" />';
                echo '<input type="hidden" name="postid" value="'.$lifestyleid.'" />';
                echo '<table class="postinfo"><tr><th>Title:</th><td style="font-weight:bold">'.$lifestyle['title'].'</td>
                </tr>';
                echo '<th>Date:</th><td>'.date('dS-M-Y', strtotime($lifestyle['cdate'])).' at'.' '.date('g:h A', strtotime($lifestyle['cdate'])).'</td>
                <th>By:</th><td>'.$lifestyle['firstname'].' '.$lifestyle['lastname'].'</td>';
                echo '</table>';

                echo '<div class="postbody"><table>';
                echo '<tr><td>'.$lifestyle['body'].'</td><td></td></tr>'.'</table></div>';

                $comments=dbAll("select * from comments,lifestyle where comments.postid=$lifestyleid and section_id=1 order by commentdate DESC");

                echo '<div class="comment">';
                echo '<table><tr><th>Comments</th></tr>';

                **foreach($comments as $comment)
                {
                    echo '<tr><td>'.$comment['commentbody'].'</td></tr>';
                }**

                echo '<tr><td><textarea rows="1" title="Write a comment" name="comment" placeholder="Write a comment..."></textarea></td></tr>';
                echo '</table></div>';
                echo '<input type="submit" name="action" value="Save Comment" />';
                echo '<hr />';
                echo '</form>';
            }

        }
    ?>

I have been trying all day, please help.

share|improve this question
1  
You should never nest correlated queries in a loop like this. Connecting to a database, sending a query, producing a resultset and sending that resultset back to your application is expensive. Do it once, not as many times as you loop. You can join all 3 tables in a single query to get the 'lifestyles' and all the related 'comments' all at once. Order the results by lifestyle then comment date and only print the lifestyle once per group of comments in the application side. – Dan Grossman Jun 28 '11 at 19:55
up vote 0 down vote accepted

The problem is the query on this line:

$comments=dbAll("select * from comments,lifestyle where comments.postid=$lifestyleid and section_id=1 order by commentdate DESC");

You aren't joining the comments table to the lifestyle table. Try adding the join, changing the query to:

select *
from comments,lifestyle
where comments.postid=$lifestyleid
and section_id=1
---->>
AND comments.postid = lifestyle.id
order by commentdate DESC
share|improve this answer
    
Thanks a lot. I added the JOIN and all worked like magic. But I will modify the query to join all the 3 tables at once. Thanks again guys. – Maurice Jun 28 '11 at 20:20
    
Don't forget to accept an this answer if it helped you. It helps both of our reputations. – Jacob Eggers Jun 28 '11 at 20:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.