Compare 2 tables

[widget]

Using mysql and php 5

 

Table 1 is a list of books.

 

 

Table 2 is a list of userids and the books read.

 

USER_ID | ITEM_ID

________________

 

1 | 345

1 | 456

2 | 345

2 | 457

2 | 347

 

My page displays images (opaque) of all books available from Table 1

 

In the while statement that displays the books, I would like to check Table 2 for a match and if the user has read it, the image is not opaque and also contains a link.

 

I have no clue on the best way to do this.

 

Below is my code so far.

 

$sql_query = "SELECT id,item_name FROM items2 WHERE item_type = 'intel_inc'";
$result = mysql_query($sql_query);
if(mysql_num_rows($result))
{
 while($row = mysql_fetch_row($result))
 {
  $get_books_read = mysql_query("SELECT item_id FROM read2 WHERE user_id = '$id'");

  if( IF THERE IS A MATCH )
   {
  echo ("<div class='book_yes'><img src='$base_url/images/items/item_$row[0].gif' width='64px' height='64px'><br>$row[1]</div> ");
   }
  else 
   {
  echo ("<div class='book_no'><a href='#'><img src='$base_url/images/items/item_$row[0].gif' width='64px' height='64px'><br>$row[1]</a></div> ");
   }
 }
}
//if no fields exist
else
{
 echo "no values in the database";
} 

 

Thanks in advance for any help offered.

[PFMaBiSmAd]

You would use a LEFT JOIN between table 1 and table 2 ON the book id. This would give you all the matching rows from table 1 and if there is matching information from table 2, you would get a non-null value from an table 2 column that you select. A null value from a selected table 2 column would indicate that the user hasn't read the current book being displayed.

[widget]

Thank you for your advice although I managed to get it working with the below code.

 

Theres probably a much neater way to do it though.

 

$sql_query = "SELECT id,item_name FROM items2 WHERE item_type = 'intel_inc'";
$result = mysql_query($sql_query);
if(mysql_num_rows($result))
{
 while($row = mysql_fetch_row($result))
 {


$books_id = $row[0];
$find_pet = fetch("SELECT item_id FROM read2 WHERE pet_id = '$id' AND item_id = '$books_id'");
$pets_books_id = $find_pet[item_id];
if ($pets_books_id == $books_id) 
   {
 ?>
 <style>
 .book2 img{
  opacity:1.0;
  filter:alpha(opacity=100); /* For IE8 and earlier */
 </style>
 <?
  echo ("<div class='book book2'><a href=/books/$books_id.php><img src='$base_url/images/items/item_$books_id.gif' width='64px' height='64px'><br>$row[1]</a> </div> ");
   }
  else 
   {
   ?>
   <style>
   .book3 img{
 opacity:0.2;
 filter:alpha(opacity=20); /* For IE8 and earlier */
   </style>
   <?
  echo ("<div class='book book3'><img src='$base_url/images/items/item_$books_id.gif' width='64px' height='64px'><br>$row[1] </div> ");
   }



 }
}
//if no fields exist
else
{
 echo "no values in the database";
} 
?>