help with inner join

[9thReg]

Hello all,

I'm trying to learn something new, and I'm having a little bit of difficulty.  I'm trying to learn about inner joins.  I've looked at countless books, tutorials, and everything else I can find, but I can't seem to figure this out.  Nothing I try works.

I've got tables that are related, and I'm trying to get the data to display.  I've got the database set up as follows:

movies (name of table) has the fields movie_id, movie_name, and movie_star_rating

family_rating (name of table) has the fields star_id, star_name, and star_file_name

in PHPMyAdmin, I've got movies.movie_star_rating internally related to family_rating.star_id

When I display the data, I am trying to pull from the movies table, but instead of having a number displayed, I would like to have family_rating.star_name displayed.  I've got the following code to do this:

$query="SELECT * FROM movies
  INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id 
  WHERE movies.movie_name_series='$search'";
$result= mysql_query($query)or die (mysql_error());
while($row=mysql_fetch_array($result)){
// Display the data 
 echo $row['movies.movie_name'];
 echo $row['family_rating.star_file_name'];
}

Can anyone let me know what I'm doing wrong?  Thanks for your help.

[bregent]

>I'm trying to learn something new,

>and I'm having a little bit of difficulty.

>I'm trying to learn about inner joins.

Really? Inner joins are just regular joins - the most commonly used join in SQL. Are you thinking about something else?

>Can anyone let me know what I'm doing wrong?

What results are you getting? Show us some sample data from both tables.

[9thReg]

Thank you for your reply.  It might be an issue about me thinking about something else, every tutorial and book I've read tells me that what I'm trying to do is an inner join (as opposed to a left join or right join).  But yes, I am trying to get information from another table to display when I query another table.  So, for some sample data:

This is the sample data for the table movies:

movie_id	movie_rating	parent_alert	movie_genre_one	movie_genre_two	movie_genre_three	movie_name
387	2	2	27	28	22	Test   Movie Entry
movie_name_series	movie_star_rating	link	alias	art	description
3	4	testmovie.avi	2	coverart.jpg	This   is a test

This is the sample data for the table family_rating:

star_id	star_name	star_file_name
1
2	One   Star	one_star.png
3	Two   Stars	two_star.png
4	Three   Stars	three_star.png
5	Four   Stars	four_star.png
11	Five   Stars	five_star.png

The results that I'm getting with the code on the web page of:

$query="SELECT * FROM movies, family_rating
  INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id
  WHERE movies.movie_name_series='$search'";

$result= mysql_query($query)or die (mysql_error());

while($row=mysql_fetch_array($result)){

// Display the data

echo $row['movies.movie_name'];
echo $row['family_rating.star_file_name'];
}

is:

Not unique table/alias: 'family_rating'

What I'm trying to have as an output is (after a search is performed so the veriable being sent between pages is $search:

Test Movie Entry

One Star

[image of stars]

Obviously I'll ultimately be pulling information from other tables as well, but I'm starting with just two tables so I can learn how this works.  Again, I thank you for your help.

[bregent · Accepted solution]

$query="SELECT * FROM movies, family_rating
   INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id
   WHERE movies.movie_name_series='$search'";

OK, that SQL is not the same as your original post.

>>Not unique table/alias: 'family_rating'

This tells us where the error is. You are specifying both tables after the from clause. Try it again with your original query:

$query="SELECT * FROM movies
  INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id
  WHERE movies.movie_name_series='$search'";

[9thReg]

Thank you so much for your help.  It worked.  I think I understand it now.  Sorry about pasting a different query from my original post, I was just still playing around with it hoping to get it right.

Again, thank you very much.