Return records from table and display each weeks data in a separate row

[djs1971]

Please excuse what may be a simple question from a relative newbie!

 

I have a table in my database which has the following fields/data types

 

id - integer

admin - integer

date - date

am - varchar

pm - archer

 

I am trying to work out what sort of MySQL query I could use to display the attendance marks for a complete week on each row of the table.

 

I can currently achieve something close but by using a separate table for each day of the week and having the 5 tables displayed side by side - but I am sure there must be a way of having it displayed in a single table, just can't figure it out!

 

Attachedis a screenshot of what I can achieve currently:

 

 

Hopefully someone would be kind enough to point me in the right direction.

 

The code I am currently to return data for single day is as follows:

$student_attendance_monday_query = "SELECT am, pm
	FROM attendance_15_16
	WHERE DATE_FORMAT(date,'%W')='Monday' AND admin = '" . $admin . "'
	";

$student_attendance_monday_results = mysqli_query($link, $student_attendance_monday_query);
while ($row = mysqli_fetch_array($student_attendance_monday_results)) {
	$mon_am_new = $row['am'];
	$mon_pm_new = $row['pm'];
	
echo "<tr>
      <td>" . $mon_am_new . "</td>
      <td>" . $mon_pm_new . "</td>";
};

Edited December 7, 2015 by djs1971

[Barand]

A table name like "attendance_15_16" implies you have your attendance data spread over several separate tables. Bad design.

What is the "admin" column. I would expect to see a student_id column in there to identify whose attendance it is recording.

 

You can accomplish what you want with a single query, but it help if, first, we know the structure of your other related tables also.