[Resolved] Confusing Problem

[nick2005]

This is a tough one for me to get my head around, wonder if anyone can help.

I'm calling results from two tables in the same database.

One table is called 'castcrew' which has the prog_id of a particlar tv programme in my database (from another table called 'progs')and then the fields director1, director2, producer1, producer2 etc. in these fields there is an id number of a person. that relates to another table i have 'people' which includes their name, dob, pob etc.

I want a page to display all the programmes, they have produced and/or directed. But if they have directed two programmes it will only display one of the programmes they have directed.

Here's my code - may be a bit slapdash cos i am a relative begginner..

function showdirectoredprogrammes($people_id){
$query = "SELECT * FROM castcrew WHERE director = '$people_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames');
$num = mysql_num_rows($result);

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

$prog_id = $rows['prog_id'];

$query = "SELECT * FROM progs WHERE prog_id = '$prog_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

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

$title = $rows['title'];
$first_shown = $rows['first_shown'];
$prog_id = $rows['prog_id'];
$channel = $channel['channel'];

echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

} // ends while loop
}

$query = "SELECT * FROM castcrew WHERE director1 = '$people_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames');
$num = mysql_num_rows($result);

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

$prog_id = $rows['prog_id'];

$query = "SELECT * FROM progs WHERE prog_id = '$prog_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

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

$title = $rows['title'];
$first_shown = $rows['first_shown'];
$prog_id = $rows['prog_id'];
$channel = $channel['channel'];

echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

} // ends while loop
}

$query = "SELECT * FROM castcrew WHERE director2 = '$people_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames');
$num = mysql_num_rows($result);

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

$prog_id = $rows['prog_id'];

$query = "SELECT * FROM progs WHERE prog_id = '$prog_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

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

$title = $rows['title'];
$first_shown = $rows['first_shown'];
$prog_id = $rows['prog_id'];
$channel = $channel['channel'];

echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

} // ends while loop
}

$query = "SELECT * FROM castcrew WHERE director3 = '$people_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames');
$num = mysql_num_rows($result);

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

$prog_id = $rows['prog_id'];

$query = "SELECT * FROM progs WHERE prog_id = '$prog_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

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

$title = $rows['title'];
$first_shown = $rows['first_shown'];
$prog_id = $rows['prog_id'];
$channel = $channel['channel'];

echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

} // ends while loop
}

$query = "SELECT * FROM castcrew WHERE director4 = '$people_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames');
$num = mysql_num_rows($result);

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

$prog_id = $rows['prog_id'];

$query = "SELECT * FROM progs WHERE prog_id = '$prog_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

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

$title = $rows['title'];
$first_shown = $rows['first_shown'];
$prog_id = $rows['prog_id'];
$channel = $channel['channel'];

echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

} // ends while loop
}

$query = "SELECT * FROM castcrew WHERE director5 = '$people_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames');
$num = mysql_num_rows($result);

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

$prog_id = $rows['prog_id'];

$query = "SELECT * FROM progs WHERE prog_id = '$prog_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

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

$title = $rows['title'];
$first_shown = $rows['first_shown'];
$prog_id = $rows['prog_id'];
$channel = $channel['channel'];

echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

} // ends while loop
}

$query = "SELECT * FROM castcrew WHERE director6 = '$people_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames');
$num = mysql_num_rows($result);

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

$prog_id = $rows['prog_id'];

$query = "SELECT * FROM progs WHERE prog_id = '$prog_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

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

$title = $rows['title'];
$first_shown = $rows['first_shown'];
$prog_id = $rows['prog_id'];
$channel = $channel['channel'];

echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

} // ends while loop
}

$query = "SELECT * FROM castcrew WHERE director7 = '$people_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames');
$num = mysql_num_rows($result);

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

$prog_id = $rows['prog_id'];

$query = "SELECT * FROM progs WHERE prog_id = '$prog_id'";
$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

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

$title = $rows['title'];
$first_shown = $rows['first_shown'];
$prog_id = $rows['prog_id'];
$channel = $channel['channel'];

echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

} // ends while loop
}

} //ends function

[nick2005]

The problem essentially is that, for the first half of the code - it finds where the people_id matches the director1 field, it displays the data.

But it does not repeat the process for every instance of the people_id - director1 matches. Is there any code I could place that simply says 'repeat for all row results' ?

if you see wot i mean! any help appreciated!!

[liquorvicar]

a couple of points:
- try and use PHP board tags to display PHP as it looks nicer and is easier to read:

$query = "SELECT * FROM `castcrew` WHERE `director` = '$people_id'";
	$result = mysql_query($query) or die ('Unable to query database for peoplenames');
	$num = mysql_num_rows($result);

while ( $rows = mysql_fetch_array($result) ) {
	$prog_id = $rows['prog_id'];

	$query = "SELECT * FROM `progs` WHERE `prog_id` = '$prog_id'";
	$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

	while ( $rows = mysql_fetch_array($result) ) {
		$title = $rows['title'];
		$first_shown = $rows['first_shown'];
		$prog_id = $rows['prog_id'];
		$channel = $channel['channel'];


		echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

	} // ends while loop
}

• it helps if you try and indent your code as again it's easier to read

both the above will help us to help you with your scripts!

ok, so your script. you could rewrite it much easier like this:

for ($loop=1;$loop<8;$loop++) {
  $director = ($loop>1 ? $loop : '');
  $query = "SELECT * FROM `castcrew` WHERE `director$director` = '$people_id'";
	$result = mysql_query($query) or die ('Unable to query database for peoplenames');
	$num = mysql_num_rows($result);

while ( $rows = mysql_fetch_array($result) ) {
	$prog_id = $rows['prog_id'];

	$query = "SELECT * FROM `progs` WHERE `prog_id` = '$prog_id'";
	$result = mysql_query($query) or die ('Unable to query database for peoplenames2');

	while ( $rows = mysql_fetch_array($result) ) {
		$title = $rows['title'];
		$first_shown = $rows['first_shown'];
		$prog_id = $rows['prog_id'];
		$channel = $channel['channel'];


		echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

	} // ends while loop
}
}

hint, if you ever find yourself repeating large chunks of code, stick it in a function or a loop.

[nick2005]

ah ok, didnt notice that option there, will format it different next time.

I'll give your code a try tommorrow.

thanks for your help.

[nick2005]

It doesnt work.

Have a look here:
http://www.uktvindex.net/progs/people/index.php?people_id=9

I've put your code in the director bit, in red

The 'frank skinner show' is in director2, and 'baddiel and skinner' is in the director1 field. but it only shows up the director1.

Could you look at your code again, its probably a little stupid mistake somewhere.

Thanks.

[nick2005]

Ah I think i no the problem.

The first field is not director1 its director. then its director1,director2,director3 etc.

Nope doesnt help.

[nick2005]

^

[liquorvicar]

ah, could be my fault. i've used the same names for the inner loop.

try this:

for ($loop=1;$loop<8;$loop++) {
  $director = ($loop>1 ? $loop : '');
  $query = "SELECT * FROM `castcrew` WHERE `director$director` = '$people_id'";
    $results = mysql_query($query) or die ('Unable to query database for peoplenames');
    while ( $row = mysql_fetch_array($results) ) {
        $prog_id = $row['prog_id'];

    $query = "SELECT * FROM `progs` WHERE `prog_id` = '$prog_id'";
    $result = mysql_query($query) or die ('Unable to query database for peoplenames2');

    while ( $rows = mysql_fetch_array($result) ) {
        $title = $rows['title'];
        $first_shown = $rows['first_shown'];
        $prog_id = $rows['prog_id'];
        $channel = $channel['channel'];

        echo("<a href=\"../show.php?prog_id=$prog_id\">$title</a> ($first_shown)<br>");

    } // ends while loop
}
}

[nick2005]

Thanks very much for all your help. But its still not working.

Have a look:
http://www.uktvindex.net/progs/people/index.php?people_id=9

Peter Orton's id number (9) is listed in the field 'director' for Baddiel & Skinner Unplanned - that is showing up in ur code.

But his id number is also definatly, (i've double double checked) under the field 'director1' for the Frank Skinner Show (have a look: http://www.uktvindex.net/progs/show2.php?prog_id=47) - but this isn't showing up.

Please stick with it! and have another go!

[liquorvicar]

my fault again. i didn't realise you had a 'director' field and a 'director1' field.
try changing

for ($loop=1;$loop<8;$loop++) {
  $director = ($loop>1 ? $loop : ''); 

to

for ($loop=0;$loop<8;$loop++) {
  $director = ($loop>0 ? $loop : ''); 

[liquorvicar]

out of interest, what your DB schema? it seems like you're not using the optimum layout.

i'd have it something like this:
table PROGRAMS
prog_id
etc

table PEOPLE
people_id
etc

table ROLES
role_id
role_name

table PROG_ROLES
prog_role_id
prog_id
people_id
role_id

where the ROLES table would be:
1,Director
2,Producer
etc
your query would then be:
SELECT * FROM programs,prog_roles WHERE people_id= '$people_id' AND role_id='1' and programs.prog_id=prog_roles.prog_id

[nick2005]

I have something quite similar to what you suggest.

I have progs - like you say

then people - with their name, dob, pob etc,

then i have castcrew - which has;
prog_id
director
director1,
director2,
etc

with the people id number in each field. if you see wot i mean.

It's a confusing, set up but I thats how it formed in my head!! so have the programme and then all the roles for that programme. like I say im not completly new to mysql but not far from it.

Woohoo! that new code works!

Thanks alot for all your help.

[liquorvicar]

no problem. it's nice to give something back to the community after years of getting help myself.
as for the db structure, with my method you can have unlimited people in each role for each program i.e. from zero to how ever many is the maximum. with your structure, you will be limited by how many director etc columns you have in your table. you are also storing data for 8 directors for each program (even if most of them are blank). with my method, you are not taking up unnecessary space and you will be able to add roles much more easily.