Nested While loop problem

Hi,

I am driving myself mad with getting this to work. Basically i have a page see below:

http://www.freemanholland.com/babies/your-recommendations/

You will need to login via the link in the “SIGN UP FREE TODAY!” box at the top, with joe@hotmail.com, and password.

Thought i would create a test account so you can see exactly what the problem is.

So you will see a series of recommendations, and on the right of each a box which either allows the user to like the recommendation view or see that they already like the recommendation…

What i need to do is check to see if the CURRENT person who is logged in likes the recommendation. But it does not work…

I have the following methods:


    public function selectAllApprovedRecommendations(){

        $sql = "SELECT
        r.ID as theID, r.title, r.review, r.date_added as theDate, 
        m.fname, m.sname, 
        COUNT(h.RID) AS HITS, h.UID as theUser
        FROM tbl_recommendations r 
        LEFT JOIN tbl_members m ON r.UID = m.ID
        LEFT JOIN tbl_recommendationshits h ON r.ID = h.RID
        WHERE r.deleted = 0 AND r.approved = 1 AND m.deleted = 0
        GROUP BY r.ID, r.title, r.review, r.date_added, m.fname, m.sname
        ORDER BY r.date_added DESC";
        $result = mysql_query($sql);
        return $result;
    }

    public function selectAllApprovedRecommendationsHits($RID){

        $sql = "SELECT * FROM tbl_recommendationshits WHERE RID = $RID";
        $result = mysql_query($sql);
        return $result;
    }

Now i try to check to see if the SESSION[‘ID’] is the same as $hits[‘UID’], if so show a message, if not show the box which allows users to click to love the recommendation…


 	<? 
						 	$bubbles = Recommendations::selectAllApprovedRecommendations();

						 	$i = 0;
						 	while($row = mysql_fetch_array($bubbles)){ 
						 	$date = $row['theDate'];
						    $date = strtotime($date);
						    $thedate = date("M d, Y \\at h:i:s a",$date);
						 	?>
						 	
						 	<div class="recommendation-bubble-wrap">
							 	<div class="right">
							 		<? 			
							 		$bubbles_hits = Recommendations::selectAllApprovedRecommendationsHits($row['theID']);
						    		$hits_rows = mysql_num_rows($bubbles_hits);
						    		
						    		if($hits_rows == 0) {
						    		?>
						    			<form method="post" action="">
									 		<input type="submit" name="updatehits" id="updatehits" class="hits" value=""/>
									 		<input type="hidden" name="ID" value="<?=$row['theID']?>" />
									 	</form>
									<?
						    		} else {
						    			while($hits = mysql_fetch_array($bubbles_hits)){ 
						    								    					 		
									 		if($hits['UID'] == $_SESSION['ID']) { 
									 		?> 		
									 			<div class="youlikethis"><strong>You like this review</strong></div>									 		
											<? } else { ?>
								    			<form method="post" action="">
											 		<input type="submit" name="updatehits" id="updatehits" class="hits" value=""/>
											 		<input type="hidden" name="ID" value="<?=$row['theID']?>" />
											 	</form>
											<? }
										  }							 	
								 	 }?>
							 	</div>
							 	
							 	<div class="recommendation-bubbles<?=$i?>">
							 		<div class="right heart">
							 			<img src="http://<?=$_SERVER['SERVER_NAME'].$sitename?>images/heart.png" alt="">
							 			<?=$row['HITS']?> people love this too
							 		</div>
								 	<p><?=$row['fname'].' '.$row['sname']?> wants to recommend a <strong> <?=$row['title']?></strong></p>
								 	<strong><?=$thedate?></strong>
								 	
								 	<div class="recommendation-text"><?=$row['review']?></div>
							 	</div>
						 	</div>
						 	<? 
						 	$i++;
			                $i = $i &#37; 2; 
						 	} ?>

The code above is what i am trying to check, but it doesn’t seem to work, i record the number of hits in a separate table so i had to nest the while loop. But when 2 users like the same review i get this:

http://freemanholland.com/babies/images/test.jpg

So this is not right, this user likes the review, so shouldn’t see the box below it…

Any ideas what i’m doing wrong?

Thanks

Makes the query much easier to read. This is especially true for lengthy queries.

Hey,

Thank you so much, that worked perfectly. I check the like_flag and it works perfectly.

I had one question, is there a specific reason why you have presented the SELECT statement like you have?

I have seen man people do this when offering examples of code, is it common practice or something?

Just curious thats all :wink:

Thanks again

Run this outside of PHP and see if it flags the correct recommendations:

  • replace {users_id} with the user to test for

(untested)


SELECT
     r.ID theID
     ,r.title
     ,r.review
     ,r.date_added theDate
     ,m.fname
     ,m.sname
     ,COUNT(h2.RID) HITS
     ,h2.UID theUser
     ,IF(h1.UID IS NULL,0,1) like_flag
  FROM
     tbl_recommendations r
 INNER
  JOIN
     tbl_members m
    ON
     r.UID = m.ID
   AND
     m.deleted = 0
  LEFT OUTER
  JOIN
     tbl_recommendationshits h1
    ON
     r.ID = h1.RID
   AND
     h1.UID = {users_id}
  LEFT OUTER
  JOIN
     tbl_recommendationshits h2
    ON
     r.ID = h2.RID
 WHERE
     r.deleted =0 AND r.approved = 1
 GROUP
    BY
      r.ID
 ORDER
    BY
      r.date_added DESC

The primary reason your current solution is failing is because theUser is arbitrary given the group by clause. In most cases theUser is going to be the “first” person to have liked/hit the recommendation. However, that is even uncertain given the nature of group by. So instead what I have done is added an additional join based on what I “assume” is a 1:1 relationship so that the recommendation can be flagged appropriately given a single user who likes/hit it as you described. That way the group by problem is resolved. Once this works you can simply test against the like_flag column to see if button should be displayed or not based on whether its 1(true) or 0(false).

First question I have for you is whether you would like to include recommendations submitted by users that have been deleted? The reason I ask is because your performing a left join on the members table (optional) but referencing the deleted column for that table. That turns your left join into a inner join. Is every recommendation associated with a member? Only show those recommendations for members that have not been deleted?

The other thing I’m trying to comprehend is why you are grouping by so many columns when it seems like r.ID is the primary key? Is there a particular reason your doing that perhaps something unconventional given your table schema?

Hey,

Thanks for helping me, these are both the tables in question:

tbl_recommendations
ID (Auto Inc)
UID (User ID)
title
review
date_added

tbl_recommendationshits
ID (Auto Inc)
RID (Review ID)
UID (User ID)
date_added

Can you please help?

Thanks

I think i may have not explained properly…

In tbl_recommendations, the UID is just the user ID of the user who has added that specific recommendation.

The UID in tbl_recommendationshits is the user ID of the user who likes the specific recommendation.

So what i need to do is firstly SELECT ALL the recommendations, and then also check to see if the user who is currently logged in ($_SESSION[‘ID’]) likes the recommendation.

These are both in separate tables. So whilst the recommendations are looping i need to display the Hit me button depending on whether the user likes it or not?

I hope i make sense? Would this change the SQL statement?

Thanks

Actually no, scratch that. This should be done much simpler…


    public function selectAllApprovedRecommendations(){

        $sql = "SELECT
        r.ID as theID, r.title, r.review, r.date_added as theDate, 
        m.fname, m.sname, 
        COUNT(h.RID) AS HITS, COUNT(h2.RID) AS hashit, h.UID as theUser
        FROM tbl_recommendations r 
        LEFT JOIN tbl_members m ON r.UID = m.ID
        LEFT JOIN tbl_recommendationshits h ON r.ID = h.RID
        LEFT JOIN tbl_recommendationshits h2 ON r.ID = h2.RID
        WHERE r.deleted = 0 AND r.approved = 1 AND m.deleted = 0 AND h2.UID = ".$_SESSION['ID']."
        GROUP BY r.ID, r.title, r.review, r.date_added, m.fname, m.sname
        ORDER BY r.date_added DESC";
        $result = mysql_query($sql);
        return $result;
    }

Then run 1 while, using an if($row[‘hashit’] == 0) { //Show Hit me button } else { //Show the has already hit thing }

I have tried changing the method to this:


    public function selectAllApprovedRecommendations(){

        $sql = "SELECT
        r.ID as theID, r.title, r.review, r.date_added as theDate, 
        m.fname, m.sname, 
        COUNT(h.RID) AS HITS, h.UID as theUser
        FROM tbl_recommendations r 
        LEFT JOIN tbl_members m ON r.UID = m.ID
        LEFT JOIN tbl_recommendationshits h ON r.ID = h.RID
        WHERE r.deleted = 0 AND r.approved = 1 AND m.deleted = 0
        GROUP BY r.ID, r.title, r.review, r.date_added, m.fname, m.sname
        ORDER BY r.date_added DESC";
        $result = mysql_query($sql);
        return $result;
    }

And then checking like this:


							 		<? 			
						    		if($row['HITS'] == 0){
						    		?>
						    			<form method="post" action="">
									 		<input type="submit" name="updatehits" id="updatehits" class="hits" value=""/>
									 		<input type="hidden" name="ID" value="<?=$row['theID']?>" />
									 	</form>
									<?
						    		} else { 
						    		if($row['theUser'] != $_SESSION['ID']){
						    		?>
						    			<form method="post" action="">
									 		<input type="submit" name="updatehits" id="updatehits" class="hits" value=""/>
									 		<input type="hidden" name="ID" value="<?=$row['theID']?>" />
									 	</form>
									<?
						    		} else { ?>
						 			<div class="youlikethis"><strong>You like this review</strong></div>									 		
								 <?	 }
								  }?>
							 	</div>

But it works only when one users likes a recommendation, when a different user is logged in then it shows the button regardless of whether the user has already clicked it…

I’m confused as to why it won’t work :confused:

Hey,

I have tried doing that but now it does not show the Hit me button at all?

This is what i have:


						 	<? 
						 	$bubbles = Recommendations::selectAllApprovedRecommendations();

						 	$i = 0;
						 	while($row = mysql_fetch_array($bubbles)){ 
						 	$date = $row['theDate'];
						    $date = strtotime($date);
						    $thedate = date("M d, Y \\a&#116; h:i:s a",$date);
						 	?>
						 	
						 	<div class="recommendation-bubble-wrap">
							 	<div class="right">
							 		<? 			
						    		if($row['hashit'] == 0){
						    		?>
						    			<form method="post" action="">
									 		<input type="submit" name="updatehits" id="updatehits" class="hits" value=""/>
									 		<input type="hidden" name="ID" value="<?=$row['theID']?>" />
									 	</form>
									<?
						    		} else { ?>
						 			<div class="youlikethis"><strong>You like this review</strong></div>									 		
								 <?	 }?>
							 	</div>
							 	
							 	<div class="recommendation-bubbles<?=$i?>">
							 		<div class="right heart">
							 			<img src="http://<?=$_SERVER['SERVER_NAME'].$sitename?>images/heart.png" alt="">
							 			<?=$row['HITS']?> people love this too
							 		</div>
								 	<p><?=$row['fname'].' '.$row['sname']?> wants to recommend a <strong> <?=$row['title']?></strong></p>
								 	<strong><?=$thedate?></strong>
								 	
								 	<div class="recommendation-text"><?=$row['review']?></div>
							 	</div>
						 	</div>
						 	<? 
						 	$i++;
			                $i = $i % 2; 
						 	} ?>

Am i missing something out?

I need the structure of tbl_recommendationshits to answer this question.