[RESOLVED] Nested while loop - compare results

yandoo · Jun 28, 2013

Hiya I was hoping for some help pleas , I'm getting a little out my depth and can't work out how to fix this issue. I have a number of records that are displayed in a while loop. With each record is a form and submit button that will allow the user to +1 rating to the record they are on. I want to stop people rating more than once per user_id.

I have 2 tables that are used in this query

media - media_id, other details

rating - rating_id, media_id, ip

I want it so that as each record is displayed, if the ip address has the same user_id in the rating table that the rating submit button doesnt appear. I've got so far but I just can't get my head around it.

I ended up creating a Right Join SELECT to join the two tables and search for result WHERE the ip address is = to the current users ip address. This works fine so far but I then have the user_id which needs to be checked with an IF statement to see whether or not to echo out the rating form. This is where I'm stuck as I tried to nest a while statement within another while loop but given that they both have different numbers of rows not all the records are echoed out.
For example because the rating table only has 1 record that corresponds to an ip address and because it is nested after the other while loop (which outputs all the records in the media table) only 1 record is ever outputted. And the 2nd if statement (if the user_id of the media table == user_id of rating table with same ip) never applies.

I am having to use 3 queries to get all my records displayed (2 for outputting media and pagiination) and the 3rd for the Right join.

If you could please help me I'd be so grateful, I've been on this for ages and just can't work it out. Thank you.

Heres my code which should explain a lot clearer:

<?php 
// Script Error Reporting
error_reporting(E_ALL);
ini_set('display_errors', '1');
?>
<?php 
// Run a select query to get my letest 6 items
// Connect to the MySQL database  

include "storescripts/connect_to_mysql.php"; 
$dynamicList = "";
$sql = mysql_query("SELECT * FROM media WHERE media_type = 'Book' ORDER BY media_title ASC");
$productCount = mysql_num_rows($sql); // count the output amount

//////////////////////////////////// Adam's Pagination Logic ////////////////////////////////////////////////////////////////////////
$nr = $productCount; // Get total of Num rows from the database query
if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
    //$pn = ereg_replace("[^0-9]", "", $_GET['pn']); // filter everything but numbers for security(deprecated)
} else { // If the pn URL variable is not present force it to be value of page number 1
    $pn = 1;
} 
//This is where we set how many database items to show on each page 
$itemsPerPage = 5; 
// Get the value of the last page in the pagination result set
$lastPage = ceil($nr / $itemsPerPage);
// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
if ($pn < 1) { // If it is less than 1
    $pn = 1; // force if to be 1
} else if ($pn > $lastPage) { // if it is greater than $lastpage
    $pn = $lastPage; // force it to be $lastpage's value
} 
// This creates the numbers to click in between the next and back buttons
// This section is explained well in the video that accompanies this script
$centerPages = "";
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
} else if ($pn == $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a> &nbsp;';
} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
}
// This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 


// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
// $sql2 is what we will use to fuel our while loop statement below
$sql2 = mysql_query("SELECT * FROM media WHERE media_type = 'Book' ORDER BY media_title ASC $limit"); 
//////////////////////////////// END Pagination Logic 

////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////  Pagination Display Setup /////////////////////////////////////////////////////////////////////
$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is ot equal to 1, if it is only 1 page we require no paginated links to display
if ($lastPage != "1"){
    // This shows the user what page they are on, and the total number of pages
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '&nbsp;  ';
    // If we are not on page 1 we can place the Back button
    if ($pn != 1) {
        $previous = $pn - 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
    } 
    // Lay in the clickable numbers display here between the Back and Next links
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
    // If we are not on the very last page we can place the Next button
    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
    } 
}
///////////////////////////////////// END Pagination Display Setup /

// assign ip to varible and echo

$ipaddress = $_SERVER["REMOTE_ADDR"];
echo $ipaddress;


/////// Right JOIN media and rating tables to get user_id with same ip

$sql4 = mysql_query("SELECT media.media_id
FROM rating
RIGHT JOIN media ON rating.media_id = media.media_id
WHERE media_type ='Book' AND ip='".$ipaddress."'");



if ($productCount > 0) {
	while($row = mysql_fetch_array($sql2)){ 
             $id = $row["media_id"];
			 $media_title = $row["media_title"];
			 $genre = $row["genre"];
			 $media_image = $row["media_image"];
			 $media_date = $row["media_date"];
			 $media_producer = $row["media_producer"];
			 $media_description = $row["media_description"];
			 $media_link = $row["media_link"];
			 $get_copy = $row["get_copy"];



/// while loop to check if media_id is same from the RIGHT join table as the media table			 
	while($row = mysql_fetch_array($sql4)){ 		 
			$media_id = $row["media_id"];


///// this works for the first record which hasnt been rated with same ip						 
			if ($media_id != $id){

		 $dynamicList .= '<table width="100%" border="0" cellspacing="0" cellpadding="6">
    <tr>

	  <td width="83%" valign="top" colspan="2"><h2 class="PostTitle">' . $media_title . '</h2>

	  <hr><br/>
	  </td></tr>
      <tr><td colspan="2" valign="top"><img style="border:#666 1px solid;" src="media/' . $media_image . '.jpg" alt="' . $media_title . '" border="1" class="alignLeft"/></a>

	  <span class="MediaText"> <strong>Released:</strong> '. $media_date .' <br/>
	  	  <strong>Genre:</strong> '. $genre .' <br/>
	  	  <strong>Author:</strong> '. $media_producer .' <br/>
		  <strong>Description</strong><br/>'.$media_description.'</span>
	  </td></tr><tr><td colspan="2">


    </td></tr><tr><td width="13%">



        <form action="books.php" enctype="multipart/form-data" name="myForm" id="myForm" method="post">		   
    <input type="hidden" name="pid" id="pid" value='.$id.' />
	 <input type="hidden" name="ip" id="ip" value='.$ipaddress.' />
	 <input type="hidden" name="rating" id="rating" value="1"/>


	<span class="MediaText"><strong>Rate this Book</strong></span></td><td width="87%" align="left" valign="top">

	<input type="submit" value="" name="button" id="button" class="button" /></form>

	</td></tr><tr><td>
	<span class="MediaText"><strong>Get Your Copy:</strong></span></td><td> '.$get_copy.' </td>


    </tr><tr><td colspan="2"><br/><br/></td></tr>
  </table>';

// this never gets called, perhaps because the while loop above doesnt allow it too as it only find one record.	  

} else if ($media_id == $id){ 

 $dynamicList .= '<table width="100%" border="0" cellspacing="0" cellpadding="6">
    <tr>

	  <td width="83%" valign="top" colspan="2">

	  Is THIS WORKING?????

	  </td></tr>
  </table>';



}}}} else {
	$dynamicList = "We have no products listed in our store yet";
}
mysql_close();

yandoo · Jun 28, 2013

I mean media_id NOT user_id

[RESOLVED] Nested while loop - compare results

Yyandoo

Yyandoo