View Full Version : problem with php and tables

08-31-2011, 05:42 PM
Hi guys,

I have a problem with three tables i have. There are 3 tables, which contains a person name and its id, other tables contains a movie title, id for the movie and a person_id which is for director of the movie and relates to the first table, and the third table is for movie_id and the person_id that specifies which person of the first table plays in the film of the second table.

My big problem is that i can show the list of movies, but when it comes to search for the director it just show the director of the first movies and other field are blank. I got problem in resetting the fetch_assoc, for the first movie it searches for the director in the first table by using the person_id of second table, but after that it won't reset and so other fields will be blank.

this is my code:


require 'connect.php';

$mares = mysql_query ("
SELECT * FROM movie_actor

$mdres = mysql_query ("
$pres = mysql_query ("
SELECT * FROM person

<!doctype html>

<title>Show Details</title>


<td width="40%">Film Title</td>
<td width="25%">Director Title</td>
<td width="25%">Actor Title</td>
<td width="25%">Role in the Movie</td>

<?php while ($mdrow = mysql_fetch_assoc($mdres)) { ?>


<td width="40%"><?php echo $mdrow['movie_title']; ?></td>
<?php while ($prow = mysql_fetch_assoc($pres)) { ?>
<td width="25%"><?php if ($mdrow['movie_person_id'] == $prow['person_id']) { ?>
<?php echo $prow['person_title']; ?>
<?php } ?></td>

<?php } ?>
<?php } ?>



I'm at a total loss.
Could anyone please help me?

08-31-2011, 09:31 PM
I'd personally say you would be better doing this in SQL, I don't know your exact field links, so I couldn't give you some SQL but I think doing it in SQL is the way to go.

08-31-2011, 10:32 PM
This is a task mostly dependent on SQL with a minor variation in PHP for the looping to display depending on what you want the output to look like. These descriptions are too vague, run a DESC movie_actor; DESC movie; DESC person and show us the structure of the database tables.