need help to generate summary report.

[mrlol]

Hi guys,

currently im creating a script to view summary report for each student and their assignments.. my database look like this:

Assignment Title   |   Group Member   |   Submit Date   
     Title 1         John, Sue, Amir       2010-10-10
     Title 2         John, Sue, Bob        2010-10-09
     Title 3         Bob, Sue, Amir        2010-10-06

I've created a form for lecturer choose which student and range date to view this report... something like this:

Date Submit: from _______ to _______
Student Name: ____________(Multiple Select Box)

I want to view summary in website something like this:

Student Name   |   Assigment Title   |   Date Submitted
John               Title 1               2010-10-10
Sue                Title 1               2010-10-10
Amir               Title 1               2010-10-10
John               Title 2               2010-10-09
Sue                Title 2               2010-10-09
Bob                Title 2               2010-10-09
Bob                Title 3               2010-10-06
Sue                Title 3               2010-10-06
Amir               Title 3               2010-10-06

I've created a php script to view like above summary report, but failed... i've detected the problem where my sql statement only call once for each assignments... and in my table db, all student group in one box (group member)...

my coding:

if(isset($_POST['B9'])){

$baru_satu = cleanQuery($_POST['D4']); //type of date, by submit or create
$baru_dua = cleanQuery($_POST['D5']); //From date
$baru_tiga = cleanQuery($_POST['D6']); //To date

foreach($_POST['available'] as $s) {
$q .= $s.', ';
$qtrim = strtolower($q);
$qtrim = stripslashes($q);
$qtrim = strip_tags($q);

}

$qtrim = explode(', ',$q);
foreach($qtrim as $value)
   {
      if($value!=NULL){
      $qtrim = $qtrim."OR cag_member like '%$value%' ";
      }
   }

$qtrim = substr($qtrim,8);

if($baru_satu == "Submit Date"){
$masok = "submit_date";
}
if($baru_satu == "Create Date"){
$masok = "create_date";
}
//AND ".$qtrim." AND ".$qtrim0." AND ".$qtrim1
$query  = "SELECT * FROM ag_info WHERE (".$qtrim.") AND ".$masok." BETWEEN '$baru_dua' AND '$baru_tiga' ";
$result = mysql_query($query);

I hope anyone can help me without changing or upgrading my database..

thank you so much..

[hielo]

i've detected the problem where my sql statement only call once for each assignments

You are seeing the correct results. Your query is returning all the relevant rows. The problem is in your DB design.

I hope anyone can help me without changing or upgrading my database..

Why would you want to impose this restriction when your DB design is the problem? If you really want to stick with your flawed design, what you need to do is when you retrieve the result, for every row, you will need to split 'Group Member' at the comma and then generate the rows you seek.

[mrlol]

thanks for your reply..

i've created a form for student to fill up when submitting their assignment, so thats why i've designed the DB like that... the form should look something like this:

Assignment title: ____
Assigment date: ___
Group member: _____select box____ (Multiple selection)
Upload assignment: ______

im using foreach() to store all data from group member input.. so thats why my db design look like that...

hmm.. so i guess i need to re-design my db? and can't use multiple selection of select box?

[hielo]

Ideally `Group Member` should have just ONE value. So instead of one RECORD such as : Title 1 John, Sue, Amir 2010-10-10 You would need to enter THREE (each separately):

Title 1         John       2010-10-10
Title 1         Sue        2010-10-10
Title 1         Amir       2010-10-10

Read
http://en.wikipedia.org/wiki/First_normal_form

(pay special attention to the "Repeating Groups" section). My suggestion would be:

Person
id	Name
1	John
2	Sue
3	Amir
4	Bob

Assignment
id	title
1	Title 1
2	Title 2
3	Title 3

GroupAssignment
Assignment_id	Person_id	SubmissionDate
1		1		2010-10-10
1		2		2010-10-10
1		3		2010-10-10
2		1		2010-10-09
2		2		2010-10-09
2		4		2010-10-09
3		4		2010-10-06
3		2		2010-10-06
3		3		2010-10-06

And as far as the multiple selection box goes, Yes, you can still use it. You just need to make sure that when you receive those multiple selected values, you "break" them apart and insert each value as its own record in GroupAssignment.

[mrlol]

thanks for you advice..

we forget about the summary report for awhile.. just want to focus in the database design..

ok sir, let say if i brake it row by row, what happen if user want to update the information? example, currently registered member is John, Sue & Amir. and suddenly user want to add more one member, Bob AND edit the assignment title. do you have any coding or suggestion to update the assignment title while adding new row for Bob?

please advice..
thank you

[hielo]

All you need is a list of all the assignments AND an empty field for your to enter/add Bob to Person. As soon as you add Bob to Person, get the new id for Bob and add it to GroupAssignment along with the id of his assignment:

<?php
if( isset($_POST) && !empty($_POST) )
{
   $assignment=intval($_POST['assignment']);

   //connect to db here
   //...


   if( $assignment > 0 )
   {
      //update your Assignment title here
      mysql_query("Update Assignment SET title='".mysql_real_escape_string($_POST['title'])."' WHERE id=".$assignment) or die( mysql_error() );

   echo "Assignment Title has been updated<br />";
   }

   if( $assignment > 0 && isset($_POST['person']) && !empty($_POST['person']) )
   {
      mysql_query("INSERT INTO Person('Name') VALUES('".mysql_real_escape_string($_POST['person'])."') ") or die(mysql_error());
      
      mysql_query("INSERT INTO GroupAssignment(Person_id,Assignment_id,SubmissionDate) VALUES(" . $assignment . "," . mysql_insert_id() . ",'".date('Y-m-d')."')" or die(mysql_error());
   }
   echo 'New member added<br />';
}

$result=mysql_query("SELECT id,title FROM Assignment") or die( mysql_error() );
$assignments='<select name="assignment"><option value="">Make a selection</option>';
while( $row=mysql_fetch_assoc($result) )
{
  $assignments.=sprintf('<option value="%s">%s</option>', $row['id'], htmlentities($row['title'], ENT_QUOTES) );
}
$assignments='</select>';
echo <<<FORM
<form method="post" action="{$_SERVER['PHP_SELF']}">
Assignment: {$assignments}<br />
Person: <input type="text" name="person" value="" /><br />
<input type="submit" name="Send" value="Add and Update"/>
</form>
FORM;
?>

[mrlol]

thanks for your update sir,

One more further question here, I've no problem to add new user when updating the assignment information BUT let say if user want to change the user, example:

+ Current member for Assignment A is John, Bob & Sue. Let say user want to add new member which is Frank while canceling current team member which is Bob?

Please guide me by constructing a code or detail explanation.

Thanks for your kind help.

[hielo]

You can incorporate checkboxes to the left of the name lists. Let's call it a DELETE column. If an item is checked, then delete that item. So your interface can be:

/*
Delete  Name
[]      John
[x]     Bob
[]      Sue
Assignment: [_Select an assignment_]
Person: _Frank__
{Submit}


Your checkboxes would basically be:
*/
<input type="checkbox" name="delete[]" value="1"/> John
<input type="checkbox" name="delete[]" value="12"/> Bob
<input type="checkbox" name="delete[]" value="34"/> Sue

Where the values 1, 12, AND 34 are the UNIQUE use ids for John, Bob, and Sue respectively.

On the above, when you submit, you should first look for the checked items and delete them from the SELECTED assignment: