Page 1 of 1

need help with simple PHP+SQL problem

Posted: Tue Dec 08, 2009 8:04 am
by Fuse
I'm currently using "beginning php apache mysql web developement" to learn php, and there is an exercise that is leaving me kinda stumped

I am given the following tables:

movie:

Code: Select all

+-----------+-----------------+----------------+-------------------+
| people_id | people_fullname | people_isactor | people_isdirector |
+-----------+-----------------+----------------+-------------------+
|         1 | Jim Carrey      |              1 |                 0 |
|         2 | Tom Shaydac     |              0 |                 1 |
|         3 | Lawrence Kasdan |              0 |                 1 |
|         4 | Kevin Kline     |              1 |                 0 |
|         5 | Ron Livingston  |              1 |                 0 |
|         6 | Mike Judge      |              0 |                 1 |
+-----------+-----------------+----------------+-------------------+

Code: Select all

+----------+----------------+------------+------------+-----------------+----------------+
| movie_id | movie_name     | movie_type | movie_year | movie_leadactor | movie_director |
+----------+----------------+------------+------------+-----------------+----------------+
|        1 | Bruce Almighty |          5 |       2003 |               1 |              2 |
|        2 | Office Space   |          5 |       1999 |               5 |              6 |
|        3 | Grand Canyon   |          2 |       1991 |               4 |              3 |
+----------+----------------+------------+------------+-----------------+----------------+
I need to use join to come up with the following table:

Code: Select all

+----------------+-----------------+----------------+
| movie_name     | movie_leadactor | movie_director |
+----------------+-----------------+----------------+
| Bruce Almighty | Jim Carrey      | Tom Shaydac    |
| Office Space   | Ron Livingston  | Mike Judge     |
| Grand Canyon   | Kevin Kline     | Lawrence Kasda |
+----------------+-----------------+----------------+
The closest i can get is to have moviename and movie lead actor display correctly, the movie director field would just have the actor names repeated, using:
Select movie_name, people_fullname movie_leadactor, people_fullname movie_director from movie left join people on movie_leadactor = people_id; (I've tried adding and movie_director = people_id by then for obvious reasons the query returns a empty set).
Can anyone tell me whut I is doing wrong?

TIA

Re: need help with simple PHP+SQL problem

Posted: Tue Dec 08, 2009 12:54 pm
by AbraCadaver
What do you want to retrieve?