Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Gah i hope my title says something about what i am trying to do, i'll try to describe it abit better: I have a database with two tables, one named "movies" and another one named "directors". Its a small movie database where we are supposed to be able to display all the movies, their title, year and producer.

In the table "directors" i have a field "id" and in my "movies" table i have a field named "producer" with the matching id. I want the while loop to loop thru all the movies in the "movies" table (working fine) and if i choose to print the "id" from "movies" its correct. But now i want the loop to display the "title" and "year" from the "movies" table, and go to the "directors" table and get the name for the matching "id".

Im new to both php and mysql querys and my code does this correctly for the first movie, but the rest have their "producer" field empty as for now. (right now im just trying to display the surname to see that it works) Im well aware that this code is going to make your pro-eyes bleed but spare with me :) FYI this is for a school project. code:

 <?php
 $sql = "SELECT * FROM movies ORDER BY title ASC";
 $result = mysql_query($sql);
 $row = mysql_fetch_assoc($result);
 $id = $row['id'];


 $num=mysql_numrows($result);

 $i=0;

 while ($i < $num) {

 $title=mysql_result($result,$i,"title");
 $year=mysql_result($result,$i,"year");
 $producer=mysql_result($result,$i,"producer");
 $id=mysql_result($result,$i,"id");


 $sqldir = "SELECT * FROM directors WHERE id='$producer'";
 $result1 = mysql_query($sqldir);
 $row1 = mysql_fetch_assoc($result1);
 $iddir = $row['id'];
 $producertext = mysql_result($result1,$i,"surname");
 ?>





 <b>Title:</b> <?php echo $title ?>
 <br/><b>Year:</b> <?php echo $year ?>
 <br/><b>Director:</b> <?php echo $producertext ?>
 <br/>    

    </form> <HR>
    <?php
    $i++;

}   
?>
share|improve this question
I'm not sure what your question is. Does the code above work? Is there an error? Is it producing the wrong output? The right output, but you need it to do something extra? – andrewsi May 10 at 16:59
It does display the titles, year correct but only the producer for the first movie in the loop. The rest have their producer fields blank, sorry for being fussy – user1864418 May 10 at 17:04
I've added an answer. If you need any more help, just ask. – andrewsi May 10 at 17:13

4 Answers

up vote 0 down vote accepted

Here should be a complete solution assuming the query works as expected 

 <?php
 $sql = "SELECT movies.title as title, movies.year as `year` producer.surname as surname FROM movies, producer where movies.producter = producer.id ORDER BY title ASC";
 $result = mysql_query($sql);
 while ($row = mysql_fetch_assoc($result)): ?>
    <b>Title:</b> <?php echo $row['title'] ?>
    <br/><b>Year:</b> <?php echo $row['year'] ?>
    <br/><b>Director:</b> <?php echo $row['surname]; ?>
 <?php
 endwhile;
 ?>
share|improve this answer
The second table is called directors rather than producer – andrewsi May 10 at 18:46
Hm if i remove the "movies.year as year" it works? why is that? I want the year attribute included :P – user1864418 May 10 at 18:47
I had the year in backticks because it's a mysql native function name... you could also do movies.year as year_released then reference it as $row['year_released'] in the output template – Orangepill May 10 at 18:49
THIS IS WORKING NOW :D Thanks you very much! Just had to change the order of the title and year for some reason.. Thank you everyone for you answers, you've all helped! – user1864418 May 10 at 18:52
up vote 0 down vote

You are using wrong function 

$num=mysql_numrows($result);

you should use

$num=mysql_num_rows($result);
share|improve this answer
That looks better, it doent solve my problem thou, but thanks alot! – user1864418 May 10 at 17:02
up vote 0 down vote

You're accessing the producer information as:

$producertext = mysql_result($result1,$i,"surname");

However, $i is the index from the main SQL loop; so on the second run through, you'll be looking for the surname from the second line of your producer result set, which won't necessarily be there. So only the first producer is showing up.

Some other things you might want to look at - you can use PDO or mysqli to access data - they're more secure that mysql, which is in the process of being deprecated. You're also using mysql_fetch_assoc at the moment, but you don't seem to be using the resulting array for anything. It's a lot more common to go through a result set with something like:

while ($row = myssql_fetch_assoc($result)) {
    ....
}

Which loads the next row into an associative array for you to use, and stops when you run out of rows.

Also, have you considered what your code should do if there's more than one producer for a film? You might want to add a loop for that query, too.

share|improve this answer
Hm this is also what ive been thinkin of without the knowledge of how to solve it. What would i need to look for, since it does have the correct ID stored somewhere because if i print the $director, it shows the right id on every movie. however if i do: $producertext = mysql_result($result1,$director,"surname"); it doesnt work – user1864418 May 10 at 18:25
I wouldn't use mysql_result; instead, I'd run the query for the moveie, and then have a while loop while ($movie_row = mssql_fetch_array($result)) { - then, inside there, you can see the movie's ID as $movie_row['id'], and you can use that to generate your next query for the director. You can generate the SQL for that as $sqldir = "SELECT * FROM directors WHERE id='" . $movie_row['director'];, and then loop through the results from that query in exactly the same way – andrewsi May 10 at 18:30
Does that make sense? I can edit a fuller version into the answer you think it'd help. – andrewsi May 10 at 18:30
up vote 0 down vote

Assuming that every movie has a single director then you can just create a joined query

SELECT movies.title as title, movies.year as `year` producer.surname as surname  
FROM movies, producer where movies.producter = producer.id ORDER BY title ASC

You can then just walk the result set and the surname will be in the result arrays.

share|improve this answer
This seems to be what im looking for, however im not sure how to implement it to my code. ive tried: $sql = "SELECT movies.title as title, movies.year as year producer.surname as surname FROM movies.producer where movies.producter = producer.id ORDER BY title ASC"; $result = mysql_query($sql); $row = mysql_fetch_assoc($result); – user1864418 May 10 at 18:22
that should be all it takes to get the first answer. Just to double check the query against the result you can try to manually issue it to the server (via phpmyadmin or the commandline) just to make sure you are getting back what you expect; – Orangepill May 10 at 18:26
Ok ill give it a try! Thanks! – user1864418 May 10 at 18:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.