[SOLVED] Keeping track of movies

[J4B]

Alright, so I'm looking for an easier way to do this. Right now I tried this and it didn't work:

$DBrating = mysql_query("SELECT rating FROM members WHERE username = '$myusername'");
$DBmovies = mysql_query("SELECT movies FROM members WHERE username = '$myusername'");
echo ($DBrating.'<BR>');
echo ($DBmovies).'<BR>';
$insert_movie = mysql_query("INSERT INTO movie (num, title, rating) VALUES ('$imdb', '$title', '$rate,')");
$insert_movie1 = mysql_query("UPDATE members SET rating = '$DBrating'.'$rate,', movies = '$imdb,' WHERE username = '$myusername'") OR die(mysql_error());

 

Ya, that doesn't work. So I'm trying to figure out a way to keep track of what movies the person has seen, and what rating they gave it. any suggestions?

[lonewolf217]

for one, use a single query to get back both items

 

<?php
mysql_query("SELECT rating,movies FROM members WHERE username = {$myusername}");
?>

then use mysql_fetch_array to retrieve the values you want (google it if you dont know how to use it)

[J4B]

Alright, I had it setup how I thought it should be, and I even read about it but I can't figure out where I'm going wrong:

 

$sql = mysql_query("SELECT rating,movies FROM members WHERE username = '$myusername'");	
$result = mysql_query($sql,$c);
print_r(mysql_fetch_array($result));

[Psycho]

I am *assuming* the members table is a table of members. If that is the case, then ratings and "watched" movies should be in separate tables (along with another table for the movie records).

[J4B]

Right now I have 2 tables:

Members:

ID--username--password--movies--ratings

 

movies:

num--title--rating

 

and your saying I should have 3?

 

Members:

ID--username--password

 

watched:

ID--movies--ratings

 

movies:

num--title--rating

 

is that right?

[Psycho]

No, I'm saying you want four tables:

 

Members:
ID--username--password

movies:
ID--title--rating

watched:
movieID--memberID

member_rating
movieID--memberID--member_rating

 

In your current schema, how were you tracking multiple movies that a member had watched or multuple movies a member had rated?

 

Anyway, with the above schema you could get a list of every movie a member had watched and/or rated with a query such as this

SELECT *
FROM movies
LEFT JOIN watched ON watched.movieID = movies.ID
LEFT JOIN rating ON rating.movieID = movies.ID
JOIN members ON members.id = watched.memberID OR members.id = rating.memberID
WHERE members.name = '$memberName'

[J4B]

watched:
movieID--memberID

member_rating
movieID--memberID--member_rating

I'm assuming here that if they are rating the movie, they have seen it, so these 2 could be combined I think.

In your current schema, how were you tracking multiple movies that a member had watched or multuple movies a member had rated?

I didn't really have a way, that is why I started this.

 

Also, if I can combine them how would I grab the movies they had seen and what rating they gave them?

[Psycho]

Also, if I can combine them how would I grab the movies they had seen and what rating they gave them?

 

if you mean combined them into the members table, I can't really think of a way to logically do that. I asked in case I may be misunderstanding something about what you are trying to achieve.

 

Now one thing I am not sure of is do you track movies that members have watched IF they have not rated them? If so, then you don't need to track if they have watched the movie as you can simply deduce that by the movies they have rated. If you are tracking those separately you *could* possibly combine the watched and rating value for each member in a single table (movieID | watched | rating).

[J4B]

I meant combining the watched and member rating tables.

The way it works is the person adds a movie to their profile or whatever when they watch it, and rate it. So any movie they have seen, is all that will be on their profile. So if I did 3 tables as follows:

 

Members:

ID--username--password

 

Members_watched:

ID--movies--ratings

 

movies:

num--title--rating

 

Then this would effectively track what movies people had seen only. Also if I do it this way, I'm confused though as to how I would grab all the movies in the members_watched table that a certain member had seen.

example:

Members_watched:

ID--movies--ratings

A--matrix--5

B--matrix--4

D--jumper--4

C--matrix--5

A--jumper--5

A--300--4

B--jumper--3

D--matrix--3

 

how would I grab all the movies that person A had seen along with the rating?

[J4B]

bump

[J4B]

bump

[redarrow]

 

 

All you do is add the user's id to every database field, And then,

select any table via join, and pull the info out.

 

even easier way here we go.

 

database comments.

 

database table.

users.

 

user_id

username

user_url

user_email

 

database table comments

 

comments_id

user_id

comment

date

 

Now look a simple join WATCH ..... without naming ((join)) in the select WATCH.....

 

 

 

SELECT* form users,comments WHERE users.users_id=comments.comments_id

 

so now we got all the users posts shown via this grate wonderful example.

[Psycho]

Use the followign tables:

 

Members:

ID--username--password

 

watched:

movieID--memberID--rating

 

movies:

ID--title--rating

 

Then you can get a list of all the movies a member has watched (along with their rating as follows)

SELECT movie.title, watched.rating
FROM movies
JOIN watched ON movies.id = watched.movieID
JOIN members ON watched.memberID = members.ID
WHERE member.username = '$username'

[J4B]

Thank you!

[redarrow]

would this also work interesting.

SELECT * FROM members,movies,watched WHERE  
movies.id = watched.movieID AND watched.memberID 
= members.ID WHERE members.username = '$username'