Getting data from two tables using two clauses

PHP
1

Hello,

Wondering if anyone would be so kind as to help me out in my current confusion?

I am trying to get data from two tables using 2 clauses, the first clause is;

  1. WHERE userID='“.$_SESSION[‘userId’].”

  2. WHERE media_type = ‘book’

userID is in a table called web_users_reviews
media_type is in a table called web_data

Essentially what I am trying to do is get back the 5 most recent entries from a specific user that are only book reviews.

Table 1


CREATE TABLE web_data (
id NOT NULL AUTO_INCREMENT PRIMARY KEY INT(100),
userID INT(100),
media_type VARCHAR(255),
title VARCHAR(255)
);

Table 2


CREATE TABLE web_users_reviews (
id AUTO_INCREMENT PRIMARY INT(100),
userID INT(100),
mediaID INT(100),
title VARCHAR(255)
);

Thank you very much for taking the time to read this and hopefully finding a solution for me.

Dan.

2

Sorry, I forgot to post what code I have already :frowning:


$result = mysql_query("SELECT title, score, media_type
			FROM web_users_reviews, web_data
			WHERE web_users_reviews.userID='".$_SESSION['userId']."'
			AND web_data.media_type='book'");
3

please describe how the tables are related

4

web_users_reviews takes data from web_data in a relational way, thats as simple as it gets im afraid.

5

i’m sorry, there is no “takes data” operator in SQL, unless you mean an INSERT SELECT statement

i was asking about which columns would be used to match rows in a join query

6

Ah got you,

Well, web_users_reviews.mediaID is related to web_data.id and that is the only columns that are related in this query between the teo tables.

7 
SELECT web_data.title
     , web_data.score
  FROM web_users_reviews
INNER
  JOIN web_data
    ON web_data.id = web_users_reviews.mediaID 
   AND web_data.media_type = 'book'
 WHERE web_users_reviews.userID = $_SESSION['userId']
ORDER
    BY web_users_reviews.review_date DESC LIMIT 5
8

Thank you very much for your help r937, had to tweak it slightly to get it working but you got me on the right path. Really appriciate the help.

The final code for anyone else ever trying to do this same type of query.


mysql_query("SELECT web_data.title, web_users_reviews.score
	       FROM web_users_reviews
		   INNER JOIN web_data
		   ON web_data.id = web_users_reviews.mediaID 
		   AND web_data.media_type='book'
		   WHERE web_users_reviews.userID = ".$_SESSION['userId']."
		   ORDER BY web_users_reviews.id DESC LIMIT 5") or die(mysql_error());