help with retrieving data from a many-to-many relationship

[ptoly]

Hi Everyone,

Newbie here. I've been searching for over a week for some tutorial information on how to append related data (mySQL tables) to a list.

 

I have a table of workshops, a many-to-many join table, and a table of teachers who will be team teaching in each workshop. I've made a list that displays the workshops and a drill down link to the detail but can't figure out the correct syntax for displaying the related teachers who are teaching each workshop.

 

I've gotten as far as making the many-to-many subquery work and return the correct number of rows but now I can't get the first row to echo.

 

Here is my code:

<?PHP               
                    mysql_select_db($database_backend, $backend);
                    $query_rsTeachers = "SELECT DISTINCT teachers.name FROM ((teachers 
				INNER JOIN teachers_crossref ON teachers_crossref.teacher_id=teachers.id) 
				INNER JOIN workshops ON '".$row_rsWorkshops['id_rec']."'=teachers_crossref.workshop_id) 
				ORDER BY teachers.name ASC";
                    $rsTeachers = mysql_query($query_rsTeachers, $backend) or die(mysql_error());
                    $row_rsTeachers = mysql_fetch_object($rsTeachers);
                    $totalRows_rsTeachers = mysql_num_rows($rsTeachers);
                    ?>
                	<div class="byline">teachers:
				<? echo $totalRows_rsTeachers; 
				echo "<br />";
				// Print out the contents of each row into a table 
				while($row_rsTeachers = mysql_fetch_object($rsTeachers)){
					echo $row_rsTeachers->name; echo ", "; echo $row_rsWorkshops['id_rec'];
					echo "<br />";
				}
				?>

As I said the query seems to work fine and return the correct number of rows, as shown by the <? echo $totalRows_rsTeachers; but then what is actually echoed from the <? echo $totalRows_rsTeachers; is missing the first row (teacher).

 

I suspect that there is something fairly loopy with my loop syntax, as I'm completely new to this. Any suggestions?

 

Any pointers to some good tutorials on php syntax for pulling relational data out of tables would be really appreciated as well.

 

Thanks!

[nethnet]

I'm suspecting it has something to do with internal pointers.  Remove this line right before you use mysql_num_rows():

 

$row_rsTeachers = mysql_fetch_object($rsTeachers);

 

And see if that makes the loop start at the first row instead of the second.

 

Theo

[ptoly]

That did it. Thanks!

 

Any suggestions for a good tutorial on this kind of syntax? I'm really flying blind here....

 

Thanks again.

 

I'm suspecting it has something to do with internal pointers.  Remove this line right before you use mysql_num_rows():

 

$row_rsTeachers = mysql_fetch_object($rsTeachers);

 

And see if that makes the loop start at the first row instead of the second.

 

Theo

[aschk]

Interestingly (in your SQL) it looks like you're searching for teachers that are in a particular Workshop. Is this a correct assumption?

So I thought you might like to consider rewriting your SQL to be simpler and remove the evil (well it's evil in my eyes) DISTINCT clause.

 

I'm working with the following assumptions.

a) You CANNOT have 1 teacher in the same workshop twice. (unless you're planning on cloning teachers)

b) thus: the teachers_crossref has a unique index on (teacher_id,workshop_id)

 

SELECT t.name
FROM teachers_crossref tcf
JOIN teachers t ON t.id = tcf.teacher_id
WHERE tcf.workshop_id = {$row_rsWorkshops['id_rec']}

[ptoly]

Love it! That is elegant because it is so much more simple and makes perfect sense.

 

Thanks! Much appreciated.