How can I echo only the newest database record. (mysql left join)

[madjack87]

Here is what I am trying to accomplish:

 

I have a students table with a studentID

I also have a notes table and a sched table with studentID

 

The sched table is working as planned where if the student is scheduled for more than one time he is displayed twice.

However if there are multiple notes per student the student is display for each individual note where I would like only the most current note to be displayed

 

Here is what the output is:

 

1- 10:30:00 - 10:50:00 student3 Three FST Teacher One Special1 One Writing Ratios 09/10

2- 10:30:00 - 10:50:00 student3 Three FST Teacher One Special1 One Needs to work on fractions and decimals 09/10

3- 13:00:00 - 14:00:00 student3 Three FST Teacher One Special1 One Writing Ratios 09/10

4- 13:00:00 - 14:00:00 student3 Three FST Teacher One Special1 One Needs to work on fractions and decimals 09/10

 

As you can see I have two notes and two schedule times for this student. what I want it to display is only line 1 & 3. which is the newest note in the system.

 

Below is my code that I am using. Any help would be greatly appreciated.

 

 

<?php 
$result = mysql_query("SELECT * FROM students 
				  LEFT JOIN teachers ON students.teacherId = teachers.teacherId
				  LEFT JOIN course ON students.courseId = course.courseId
				  LEFT JOIN specialEd ON students.specialId = specialEd.specialId
				  LEFT JOIN sched ON students.studentId = sched.studentId 
				  ORDER BY start
				  ");
echo "<table>";
while ($row = mysql_fetch_array($result)){
$id = "?id=" . $row['studentId'];
echo "<tr>";
	echo "<td>" . $row['start'] . " - " . $row['stop'] . "</td>";
	echo "<td>" . "<a href='student.php$id'>" . $row['fName'] . " " . $row['lName'] . "</td>";
	echo "<td>" . $row['courseName'] . "</td>";
	echo "<td>" . $row['teachers_fName'] . " " . $row['teachers_lName'] . "</td>";
	echo "<td>" . $row['special_fName'] . " " . $row['special_lName'] . "</td>";
	echo "<td>" . $row['note'] . "</td>";
	echo "<td>" . date("m/d", strtotime($row['started'])) . "</td>";
echo "</tr>";
}
echo "</table>";
?>

[redarrow]

Can we see the database schema please.

 

just show us the way you have written the database format.

 

also what the name of the notes from the database.

 

 

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

 

look this link up try and use interval or other good luck.

 

 

 

[madjack87]

Can we see the database schema please.

 

just show us the way you have written the database format.

 

also what the name of the notes fro the database.

Students Table

studentId

fName

lName

gradeLevel

courseId

teacherId

specialId

started

Notes Table

noteId

date

note

studentId

sched Table

schedId

start

stop

studentId

[redarrow]

In notes, you got date, what format is it in?

[madjack87]

In notes, you got date, what format is it in?

 

2011-09-10

[redarrow]

 

some think like this i am NOT sure.

 

 

  SELECT *
   2.
      FROM what ever
   3.
      WHERE date >= 'year(10)' AND RegDate <= 'now()'

 

your have to play with it (lol) sorry.

[madjack87]

 

some think like this i am NOT sure.

 

 

  SELECT *
   2.
      FROM what ever
   3.
      WHERE date >= 'year(10)' AND RegDate <= 'now()'

 

your have to play with it (lol) sorry.

 

Ill play with it.. Thanks for the help..

 

I think I might have to use DISTINCT or some type of GROUP by.. The problem with trying to narrow it down by date is that there will be different note dates for each student and that is where I am running into a snag!

[Muddy_Funster]

then your going to need to have either a counter in the database, or better, a timestamp filed that you can use to call the most recent comment on.