Jump to content

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


Recommended Posts

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:

 

post-93595-0-48385400-1449522856_thumb.png

 

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 by djs1971

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.