widget Posted January 26, 2013 Share Posted January 26, 2013 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. Link to comment https://forums.phpfreaks.com/topic/273669-compare-2-tables/ Share on other sites More sharing options...
PFMaBiSmAd Posted January 26, 2013 Share Posted January 26, 2013 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. Link to comment https://forums.phpfreaks.com/topic/273669-compare-2-tables/#findComment-1408377 Share on other sites More sharing options...
widget Posted January 26, 2013 Author Share Posted January 26, 2013 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"; } ?> Link to comment https://forums.phpfreaks.com/topic/273669-compare-2-tables/#findComment-1408382 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.