I need help with MYSQL JOINS

[madjack87]

Check out my code below and you will see what I am trying to do.

 

I cannot figure out what is going wrong.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mskordus/public_html/index.php on line 25

 

<?php
$result = mysql_query("SELECT * FROM students,teachers,specialEd,course,sched,notes WHERE
				   students.studentId=notes.studentId AND
				   students.studentId=sched.studentId AND
				   students.teacherId=teachers.teacherId AND
				   students.specialId=specialEd.specialId AND
				   students.courseId=course.courseId AND				
				   ");
echo "<table>";
while ($row = mysql_fetch_array($result)){
echo "<tr>";
	echo "<td>" . $row['students.fName'] . "</td>";
	echo "<td>" . $row['teachers.fName'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>

[WebStyles]

you title says

I need help with MYSQL JOINS

, so your logic is correct, you know what you want to do, but you don't have a single JOIN in your query.

maybe you should read this: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

Also: you query ends with AND

[madjack87]

you title says

I need help with MYSQL JOINS

, so your logic is correct, you know what you want to do, but you don't have a single JOIN in your query.

maybe you should read this: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

Also: you query ends with AND

 

Ok I will check that out. Thank you. Yea I figured I must have some syntax incorrect, but I was having a hard time finding a good source that related to what I wanted to do.

 

I will report back.

[madjack87]

One of the problems I am having is when trying to echo the results because my tables have some identical column names.

 

<?php
$result = mysql_query("SELECT * FROM students LEFT JOIN teachers ON students.teacherId = teachers.teacherId");
echo "<table>";
while ($row = mysql_fetch_array($result)){
echo "<tr>";
	echo "<td>" . $row['fName'] . "</td>";
	//echo "<td>" . $row['teachers.fName'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>

 

The above code will work but the commented out section will not. How do I differentiate between teachers.fName and students.fName when trying to echo the results?

[WebStyles]

you can set different names during your SELECT but you need the field names for that, so you won't be using SELECT *

 

instead you use something like :

 

"SELECT students.fName as SfName,teachers.fName as Tfname FROM students LEFT JOIN teachers ON students.teacherId = teachers.teacherId"

 

 

then u use the new names: $row['SfName'] and $row['TfName']

 

(SELECT * is bad anyway)