compare results from 2 tables

yandoo · June 28, 2013

Hiya I was hoping for some help please , 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 outputted from a media table. With each record is a form and submit button that will allow the user to add +1 rating to the rating table record for the media record they are on. I want to stop people rating more than once per media_id by storing their ip address in the rating table and then comparing it against the media table.

I have 2 tables:

media - media_id, other details

rating - rating_id, media_id, ip

I want it so that if any of the media_ids from the rating table rows is the same as any of the media_ids from the media table rows that this form and submit button is not echoed out.

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 which I previously stored in a variable. This works fine so far, but I then have to compare the media_id from this query with the media_id from the other query checking to make sure they are NOT the same. Because if they are the user will have rated that media already.

This is where I'm stuck. I tried to nest a while loop within another while loop but this doesn't work and gives incorrect results.

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.

Here's my code which should explain a lot clearer:

<?php 

// 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"];
			
	
///// see if the media id from rating table is not the same as the media id	from media table. If its not the user has not rated this media before so should be outputted fully.		 
			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>';
	  
// See if the media id from the rating table is the same as the media id from the media table. If it is then the user has already rated this media and the form should not be outputted.	  
	  
} 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(); 
?>

Here's the full code:

<?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 .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
} else if ($pn == $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a>  ';
} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
}
// 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. '   ';
    // If we are not on page 1 we can place the Back button
    if ($pn != 1) {
        $previous = $pn - 1;
        $paginationDisplay .=  '   <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 .=  '   <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"];
			
	
///// see if the media id from rating table is not the same as the media id	from media table. If its not the user has not rated this media before so should be outputted fully.		 
			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>';
	  
// See if the media id from the rating table is the same as the media id from the media table. If it is then the user has already rated this media and the form should not be outputted.	  
	  
} 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();

Please help

Thank you

DavidAM · June 28, 2013

While using the IP address is not a sure-fire way to handle this (in fact, there is no sure-fire way unless you only allow registered users to vote); You should be able to do this in one query:

SELECT media.*, IF (ISNULL(ratings.ip), 0, 1) AS HasRated
FROM media LEFT JOIN ratings ON media.media_id = ratings.media_id AND ratings.ip = '$ipaddress'
WHERE media.type = ...

For each media row found, the computed column "HasRated" will be 1 if the IP has rated it, and it will be 0 (zero) if they have not. So you handle everything in a single loop with a single call to the database.

yandoo · June 28, 2013

Thank you so much for the reply, its working! Ok so I did as you suggested:

$sql4 = mysql_query("SELECT media.*, IF (ISNULL(rating.ip), 0, 1) AS HasRated
FROM media LEFT JOIN rating ON media.media_id = rating.media_id AND rating.ip = '".$ipaddress."'
WHERE media.media_type = 'Book'");

I changed the existing while loop to $sql4:

	while($row = mysql_fetch_array($sql4)){ 
             $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"];
	 		 
			            $media_id = $row["media_id"];

and used an if statement to determine if HasRated is 1 or 0:

if ($row['HasRated']== '0'){
Do this
}
if ($row['HasRated']== '1'){
Do this instead
}

It seems to be working brilliantly. Can I just ask though, will this effect the pagination? The while loop was originally set as $sql2 which is what is being used in the pagination query? Could I just add the $limit variable to the new $sql query?

Thank you very much.

yandoo · June 28, 2013

It seems to work just fine adding the $limit variable to it. As seen here:

$sql4 = mysql_query("SELECT media.*, IF (ISNULL(rating.ip), 0, 1) AS HasRated
FROM media LEFT JOIN rating ON media.media_id = rating.media_id AND rating.ip = '".$ipaddress."'
WHERE media.media_type = 'Book' ORDER BY media_title ASC $limit");

Which means this query is completely redundant?

$sql2 = mysql_query("SELECT * FROM media WHERE media_type = 'Book' ORDER BY media_title ASC $limit");

Psycho · June 28, 2013

SELECT media.*, IF (ISNULL(ratings.ip), 0, 1) AS HasRated
FROM media LEFT JOIN ratings ON media.media_id = ratings.media_id AND ratings.ip = '$ipaddress'
WHERE media.type = ...

FYI: If you need a Boolean (i.e. 0 or 1) based upon a condition, there is no need to create an IF() condition that checks the condtition then sets a 0 or 1 value. Just use the condition as the value (it will return a 1 or 0). So instead of

SELECT media.*, IF (ISNULL(ratings.ip), 0, 1) AS HasRated

You could just use

SELECT media.*, NOT ISNULL(ratings.ip) AS HasRated

Edited June 28, 2013 by Psycho

Sign In

compare results from 2 tables

Recommended Posts

yandoo

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

yandoo

Link to comment

Share on other sites

yandoo

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information