Fetching values from MySql Database

havish · 10-16-2012, 03:29 PM

I have a table attendance which contains id(int), attendance(char), and date(date datatype) columns. Values get stored in this table through a php file. In the below code I have given two textbox for the user to select date range. eg. from 2012-10-15 to 2012-10-17.. when the user selects date range I am populating the whole class students attendance. So in my html table im populating name, roll no, attendance and date. But I do not know how to populate the date in different columns. suppose the user selects 15th october to 17th october as mentioned above, the attendance for these three dates should appear. I have tried something but I guess it's wrong.

PHP Code:


			
<link rel="stylesheet" href="http://code.jquery.com/ui/1.9.0/themes/base/jquery-ui.css" />

    <script src="http://code.jquery.com/jquery-1.8.2.js"></script>

    <script src="http://code.jquery.com/ui/1.9.0/jquery-ui.js"></script>

<script type="text/javascript">

$(document).ready(function() {

 $( "#from" ).datepicker({

            defaultDate: "+1w",

            dateFormat: "yy-mm-dd",

            changeMonth: true,

            numberOfMonths: 3,

            onSelect: function( selectedDate ) {

                $( "#to" ).datepicker( "option", "minDate", selectedDate );

            }

        });

        $( "#to" ).datepicker({

            defaultDate: "+1w",

            dateFormat: "yy-mm-dd",

            changeMonth: true,

            numberOfMonths: 3,

            onSelect: function( selectedDate ) {

                $( "#from" ).datepicker( "option", "maxDate", selectedDate );

            }

        });  

});

</script>



$report = mysql_query("SELECT id, studname, studroll, attendance, date FROM student a, samp b WHERE a.id = b.stud_id AND b.date BETWEEN '".$_POST['from']."' AND '".$_POST['to']."'") or die(mysql_error());

echo '<form action="" method="post">

      Please select date :<input name="from" id="from" type="text"  />

      Please select date :<input name="to" id="to" type="text" />

      <table width="600" border="2">

      <tr>

          <th width="83" scope="col">ID</th>

         <th width="83" scope="col">Student Name</th>

    <th width="55" scope="col">Student Roll.No</th>

    <th width="51" scope="col">Attendance</th>

   </tr>';

   while(list($id, $studname, $studroll, $attendance, $date) = mysql_fetch_row($report))

   {

     echo '<tr>

                <td>'.$id.'</td>

                <td>'.$studname.'</td>

                <td>'.$studroll.'</td>

                <td>'.$date.'</td>

           </tr>';

   }

   echo '</table><input type="submit" name ="submit"  id="submit2" value ="submit"></input>

</form>';

Student is my master table from which Iam fetching student name, student rollno.. and from table samp, iam fetching attendance and the date on which attendance was taken.. Is there any mistake in my table design. Kindly guide..

Fou-Lu · 10-16-2012, 04:54 PM

I don't understand your question. The date is specified as having its own column in this table already.

havish · 10-16-2012, 05:28 PM

Quote:

Originally Posted by Fou-Lu

I don't understand your question. The date is specified as having its own column in this table already.

Yeah but the problem is all dates get listed in different rows.. i need output like this

PHP Code:


			
<table width="443" border="1">

  <tr>

    <th rowspan="2" scope="col">Id</th>

    <th rowspan="2" scope="col">StudentName</th>

    <th rowspan="2" scope="col">StudRoll</th>

    <th colspan="3" scope="col">Attendance</th>

  </tr>

  <tr>

    <th scope="col">15-10-2012</th>

    <th scope="col">16-10-2012</th>

    <th scope="col">17-10-2012</th>

  </tr>

</table>

I hope you got what I meant. I need these selected dates separately and list who were present and absent on those days

Fou-Lu · 10-17-2012, 12:03 AM

Easily doable, its all about control.
I'm going out in a few minutes so I don't have the time to right this currently; I'll look at it when I get back. What version of PHP are you currently using (major and minor)?

havish · 10-17-2012, 03:04 AM

Quote:

Originally Posted by Fou-Lu

Easily doable, its all about control.
I'm going out in a few minutes so I don't have the time to right this currently; I'll look at it when I get back. What version of PHP are you currently using (major and minor)?

A dumb question.. as my I am new to PHP. What is major and minor version? I am currently using 5.3.8

Fou-Lu · 10-17-2012, 04:04 PM

That's what I needed to know: MAJOR.MINOR.REV, so 5.3 is what I needed to know. That means you can use the DateInterval and DatePeriod which will be handy in looping. Although you can do this from just the while loop for the fetch, I personally think that would get rather complex rather quickly. So I'd suggest dumping everything to a controlled array first, then iterating it. This way you don't even need to order the records if you don't want (although I'd still order by id then date).

PHP Code:


			
$aRecords = array();

while(list($id, $studname, $studroll, $attendance, $date) = mysql_fetch_row($report)) 

{

    if (!isset($aRecords[$id]))

    {

        $aRecords[$id] = array(

            'id' => $id,

            'studname' => $studname,

            'studroll' => $studroll,

            //'attendance' => $attendance, // I don't know what this is, so I'm leaving it out

            'ondate' => array($date)

        );

    }

    else

    {

        $aRecords[$id]['ondate'][] = $date;

    }

}



$dtStart = new DateTime($_POST['from']);

$dtEnd = new DateTime($_POST['to']);

$diDiff = $dtEnd->diff($dtStart, true);

$di = new DateInterval('P1D');

$dp = new DatePeriod($dtStart, $di, $dtEnd);



printf('

<table width="443" border="1">

  <tr>

    <th rowspan="2" scope="col">Id</th>

    <th rowspan="2" scope="col">StudentName</th>

    <th rowspan="2" scope="col">StudRoll</th>

    <th colspan="%d" scope="col">Attendance</th>

  </tr>

  <tr> 

', $diDiff->d); 

foreach ($dp AS $attDate)

{

    printf('<td>%s</td>' . PHP_EOL, $attDate->format('d-m-Y');

}

print('</tr>');

foreach ($aRecords AS $record)

{

    $att = array_pop($record);

    print('<tr>');

    vprintf('<td>%s</td><td>%s</td><td>%s</td>', $record);

    foreach($dp AS $attDate)

    {

        if (in_array($attDate, $att))

        {

            print('<td>x</td>');

        }

        else

        {

            print('<td>&nbsp;</td>');

        }

    }

    print('</tr>');

}

Try that out. Works okay in my head.

havish · 10-17-2012, 06:13 PM

Quote:

Originally Posted by Fou-Lu

That's what I needed to know: MAJOR.MINOR.REV, so 5.3 is what I needed to know. That means you can use the DateInterval and DatePeriod which will be handy in looping. Although you can do this from just the while loop for the fetch, I personally think that would get rather complex rather quickly. So I'd suggest dumping everything to a controlled array first, then iterating it. This way you don't even need to order the records if you don't want (although I'd still order by id then date).

PHP Code:


			
$aRecords = array();
while(list($id, $studname, $studroll, $attendance, $date) = mysql_fetch_row($report)) 
{
    if (!isset($aRecords[$id]))
    {
        $aRecords[$id] = array(
            'id' => $id,
            'studname' => $studname,
            'studroll' => $studroll,
            //'attendance' => $attendance, // I don't know what this is, so I'm leaving it out
            'ondate' => array($date)
        );
    }
    else
    {
        $aRecords[$id]['ondate'][] = $date;
    }
}

$dtStart = new DateTime($_POST['from']);
$dtEnd = new DateTime($_POST['to']);
$diDiff = $dtEnd->diff($dtStart, true);
$di = new DateInterval('P1D');
$dp = new DatePeriod($dtStart, $di, $dtEnd);

printf('
<table width="443" border="1">
  <tr>
    <th rowspan="2" scope="col">Id</th>
    <th rowspan="2" scope="col">StudentName</th>
    <th rowspan="2" scope="col">StudRoll</th>
    <th colspan="%d" scope="col">Attendance</th>
  </tr>
  <tr> 
', $diDiff->d); 
foreach ($dp AS $attDate)
{
    printf('<td>%s</td>' . PHP_EOL, $attDate->format('d-m-Y');
}
print('</tr>');
foreach ($aRecords AS $record)
{
    $att = array_pop($record);
    print('<tr>');
    vprintf('<td>%s</td><td>%s</td><td>%s</td>', $record);
    foreach($dp AS $attDate)
    {
        if (in_array($attDate, $att))
        {
            print('<td>x</td>');
        }
        else
        {
            print('<td>&nbsp;</td>');
        }
    }
    print('</tr>');
}

Try that out. Works okay in my head.

Thanks for this snippet.. I am trying to understand your code.. It worked out exactly as I wanted.. But I am not understanding how to populate the attendance field.. The attendance field is of varchar(1) datatype and it contains 'P' AND 'A'. P stands for present and A stands for absent.. And also I see that you have given difference of the two dates if I am not mistaken in understanding the code. Actually I wanted it to be less than or equal to the 'to date'. eg: if i select from date as 2012-10-15 and to date as 2012-10-17, it populates 15th october and 16th october, but I need all the three dates. ie 15th, 16th and 17th..

Fou-Lu · 10-17-2012, 06:24 PM

I don't understand why you need this field at all. The impression I have of the table is if a record exists, that it is assumed that attendance would exist.
Does a record exist for every date and applied either an A or P in the attendance field for every student? It's a bit of a waste in dataspace IMO to do that, but you can modify it easily as well by changing the array creation in the while:

PHP Code:


			
    if (!isset($aRecords[$id]))
    { 
        $aRecords[$id] = array(
            'id' => $id,
            'studname' => $studname,
            'studroll' => $studroll,
            'attendance' => array($date => $attendance), // I don't know what this is, so I'm leaving it out
        ); 
    else
    {
        $aRecords[$id]['attendance'][$date] = $attendance;
    }

To that, and modifying the foreach at the end to this:

PHP Code:


			
foreach ($aRecords AS $record)
{
    $att = array_pop($record);
    print('<tr>');
    vprintf('<td>%s</td><td>%s</td><td>%s</td>', $record);
    printf('<td>%s</td>', implode('</td><td>', $att);
    print('</tr>');
}

Looks like it would do it.

havish · 10-17-2012, 06:35 PM

Quote:

Originally Posted by Fou-Lu

I don't understand why you need this field at all. The impression I have of the table is if a record exists, that it is assumed that attendance would exist.
Does a record exist for every date and applied either an A or P in the attendance field for every student? It's a bit of a waste in dataspace IMO to do that, but you can modify it easily as well by changing the array creation in the while:

PHP Code:


			
    if (!isset($aRecords[$id]))

    { 

        $aRecords[$id] = array(

            'id' => $id,

            'studname' => $studname,

            'studroll' => $studroll,

            'attendance' => array($date => $attendance), // I don't know what this is, so I'm leaving it out

        ); 

    else

    {

        $aRecords[$id]['attendance'][$date] = $attendance;

    }

To that, and modifying the foreach at the end to this:

PHP Code:


			
foreach ($aRecords AS $record)

{

    $att = array_pop($record);

    print('<tr>');

    vprintf('<td>%s</td><td>%s</td><td>%s</td>', $record);

    printf('<td>%s</td>', implode('</td><td>', $att);

    print('</tr>');

}

Looks like it would do it.

What I am doing is, like I told earlier, I am marking attendance for every student as A or P through a checkbox. So if a checkbox is ticked the student is present and if not ticket the student is absent. And to mark the attendance, I have given a text field where the staff selects the date and this date gets stored in date column of database. Am i doing anything wrong?

havish · 10-17-2012, 06:37 PM

This is how the staff takes attendance..

Code:

<link rel="stylesheet" href="http://code.jquery.com/ui/1.9.0/themes/base/jquery-ui.css" />
    <script src="http://code.jquery.com/jquery-1.8.2.js"></script>
    <script src="http://code.jquery.com/ui/1.9.0/jquery-ui.js"></script>
<script type="text/javascript">
$(document).ready(function() {
 $('#datepicker').datepicker({
	dateFormat: 'yy-mm-dd',
	changeMonth: true,
			changeYear: true});   
});

</script>

PHP Code:


			
$report = mysql_query("SELECT id, studname, studroll FROM student ") or die(mysql_error());

echo '<form action="" method="post">

      Please select date :<input name="date" id="datepicker" type="text" />

      <table width="600" border="2">

      <tr>

          <th width="83" scope="col">ID</th>

         <th width="83" scope="col">Student Name</th>

    <th width="55" scope="col">Student Roll.No</th>

    <th width="51" scope="col">Attendance</th>

   </tr>';

   while(list($id, $studname, $studroll) = mysql_fetch_row($report))

   {

     echo '<tr>

                <td>'.$id.'</td>

                <td>'.$studname.'</td>

                <td>'.$studroll.'</td>

                <td><input type="hidden" name="att['.$id.']" value="0"/><input type="checkbox" name="att['.$id.']" value="1"/></td>

           </tr>';

   }

   echo '</table><input type="submit" name ="submit2"  id="submit2" value ="submit"></input>

</form>';

?>

<?php

$date = date($_POST['date']);









$att = $_POST['att'];

foreach($att AS $key => $value)

{

 $attendance  =$value ? 'P' : 'A';

$query = "INSERT INTO `samp`(`stud_id`,`attendance`,`date`) VALUES ('".$key."','".$attendance."','".$date."')";

 mysql_query($query);

     

}

havish · 10-18-2012, 04:17 AM

PHP Code:


			
echo '<form action="" method="post">

      from: <input type="text" id="from" name="from"/>

      to: <input type="text" id="to" name="to"/>

      <input type="submit" name="submit" value="submit"/>

      </form>';

$aRecords = array(); 

$report  = mysql_query("SELECT id, studname, studroll, attendance, date FROM student a, samp b WHERE a.id=b.stud_id AND date BETWEEN '".$_POST['from']."' AND '".$_POST['to']."'");

while(list($id, $studname, $studroll, $attendance, $date) = mysql_fetch_row($report))  

{ 

    if (!isset($aRecords[$id])) 

    { 

        $aRecords[$id] = array( 

            'id' => $id, 

            'studname' => $studname, 

            'studroll' => $studroll, 

            'attendance' => array($date => $attendance), // I don't know what this is, so I'm leaving it out 

             

        ); 

    } 

    else 

    { 

        $aRecords[$id]['attendance'][$date] = $attendance; 

    } 

} 



$dtStart = new DateTime($_POST['from']); 

$dtEnd = new DateTime($_POST['to']); 

$diDiff = $dtEnd->diff($dtStart, true); 

$di = new DateInterval('P1D'); 

$dp = new DatePeriod($dtStart, $di, $dtEnd); 



printf(' 

<table width="443" border="1"> 

  <tr> 

    <th rowspan="2" scope="col">Id</th> 

    <th rowspan="2" scope="col">StudentName</th> 

    <th rowspan="2" scope="col">StudRoll</th> 

    <th colspan="%d" scope="col">Attendance</th> 

  </tr> 

  <tr>  

', $diDiff->d);  

foreach ($dp AS $attDate) 

{ 

    printf('<td>%s</td>' . PHP_EOL, $attDate->format('d-m-Y')); 

} 

print('</tr>'); 

foreach ($aRecords AS $record) 

{ 

    $att = array_pop($record); 

    print('<tr>'); 

    vprintf('<td>%s</td><td>%s</td><td>%s</td>', $record); 

    printf('<td>%s</td>', implode('</td><td>', $att));

    

    print('</tr>');

}

    

 

?>

Your code works as I wanted.

But the last date ie the to date does not appear on the row. I have attached the screenshot. I have selected date ranges from 15th to 18th.

Fou-Lu · 10-18-2012, 06:01 AM

$dtEnd must be exclusive on dateperiod.
Add this directly before the datePeriod creation: $dtEnd->add($di);.

As for your data, I myself would probably track the absentee. Assuming that there is specific guidelines to follow that dictate when a day should be in attendance, then recording only those absent would reduce the amount of dataspace required. This of course assumes that on average the majority of the student population is present and not absent.

havish · 10-19-2012, 05:10 AM

Quote:

Originally Posted by Fou-Lu

$dtEnd must be exclusive on dateperiod.
Add this directly before the datePeriod creation: $dtEnd->add($di);.

As for your data, I myself would probably track the absentee. Assuming that there is specific guidelines to follow that dictate when a day should be in attendance, then recording only those absent would reduce the amount of dataspace required. This of course assumes that on average the majority of the student population is present and not absent.

Ok Fou-Lu I tried it out but facing some problems still. Will try my best to rectify it else will come back