Fetching values from MySql Database

[havish999]

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.

<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>
<?php
$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..

[diafol]

OK without delving through your code, checking between 2 dates should be as easy as you've attempted - i.e. using BETWEEN ... AND ... syntax.

I haven't used jQUery's DP for a while, so a little rusty on the date format. Shouldn't this be: dateFormat: "yyyy-mm-dd" instead of "yy-mm-dd"? Well depends on your needs I suppose.

I don't like this: ".$_POST['from']."' AND '".$_POST['to']."'" as you're using unescaped/unsanitised data directly in the SQL - use mysql_real_escape_string() if using mysql or used parameter binding if using mysqli/PDO.

You don't mention what output you're getting.

As for your table structures, they look OK. I'm assuming a LEFT JOIN here so that you can list every student and then have attendance columns. The difficulty with this is that you need new 'fields' from records, which pretty much means multiple joins (a little complicated) or the use of subqueries (to be avoided if possible).

Of course you could just dump all the data into a php array and do some fiddling from there on. In fact that might be a lot easier.

I'll come back if nobody else has a go. I'm stuck with some heavy-duty marking at the mo. :(

[havish999]

The output i get is below..

<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><tr>
                <td>1</td>
                <td>havish</td>
                <td>123</td>
                <td>2012-10-15</td>
           </tr><tr>
                <td>2</td>
                <td>murthy</td>
                <td>1234</td>
                <td>2012-10-15</td>
           </tr><tr>
                <td>3</td>
                <td>lalitha</td>
                <td>12345</td>
                <td>2012-10-15</td>
           </tr><tr>
                <td>4</td>
                <td>usha</td>
                <td>123456</td>
                <td>2012-10-15</td>
           </tr><tr>
                <td>5</td>
                <td>deepak</td>
                <td>1234567</td>
                <td>2012-10-15</td>
           </tr><tr>
                <td>6</td>
                <td>vasu</td>
                <td>12345678</td>
                <td>2012-10-15</td>
           </tr><tr>
                <td>7</td>
                <td>pradeep</td>
                <td>123456789</td>
                <td>2012-10-15</td>
           </tr><tr>
                <td>8</td>
                <td>sharanya</td>
                <td>1234567890</td>
                <td>2012-10-15</td>
           </tr><tr>
                <td>1</td>
                <td>havish</td>
                <td>123</td>
                <td>2012-10-16</td>
           </tr><tr>
                <td>2</td>
                <td>murthy</td>
                <td>1234</td>
                <td>2012-10-16</td>
           </tr><tr>
                <td>3</td>
                <td>lalitha</td>
                <td>12345</td>
                <td>2012-10-16</td>
           </tr><tr>
                <td>4</td>
                <td>usha</td>
                <td>123456</td>
                <td>2012-10-16</td>
           </tr><tr>
                <td>5</td>
                <td>deepak</td>
                <td>1234567</td>
                <td>2012-10-16</td>
           </tr><tr>
                <td>6</td>
                <td>vasu</td>
                <td>12345678</td>
                <td>2012-10-16</td>
           </tr><tr>
                <td>7</td>
                <td>pradeep</td>
                <td>123456789</td>
                <td>2012-10-16</td>
           </tr><tr>
                <td>8</td>
                <td>sharanya</td>
                <td>1234567890</td>
                <td>2012-10-16</td>
           </tr><tr>
                <td>1</td>
                <td>havish</td>
                <td>123</td>
                <td>2012-10-17</td>
           </tr><tr>
                <td>2</td>
                <td>murthy</td>
                <td>1234</td>
                <td>2012-10-17</td>
           </tr><tr>
                <td>3</td>
                <td>lalitha</td>
                <td>12345</td>
                <td>2012-10-17</td>
           </tr><tr>
                <td>4</td>
                <td>usha</td>
                <td>123456</td>
                <td>2012-10-17</td>
           </tr><tr>
                <td>5</td>
                <td>deepak</td>
                <td>1234567</td>
                <td>2012-10-17</td>
           </tr><tr>
                <td>6</td>
                <td>vasu</td>
                <td>12345678</td>
                <td>2012-10-17</td>
           </tr><tr>
                <td>7</td>
                <td>pradeep</td>
                <td>123456789</td>
                <td>2012-10-17</td>
           </tr><tr>
                <td>8</td>
                <td>sharanya</td>
                <td>1234567890</td>
                <td>2012-10-17</td>
           </tr></table>

[havish999]

The output I need is in this fashion

<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

[diafol]

OK, this is very quickly written - off the top of my head - not tested at all. Note that I've changed some of the field names too. It's very rough and I seem to have gone 'loopy' - anyway - soemthing like this maybe:

$date1 = mysql_real_escape_string($_POST['date1']);
$date2 = mysql_real_escape_string($_POST['date2']);


//FUNCTION for date increment - just adds a day every time
function incrementDate($date){
    return strtotime(date("Y-m-d", strtotime($date)) . " +1 day");  
}

//FUNCTION for getting relevant dates - no Sats and Suns
function getDatesArray($date1,$date2){
    $d = $date1;
    $dates[] = $date1;
    while($d <= $date2){
        $d = strtotime(date("Y-m-d", strtotime($d)) . " +1 day");
        $day = date('w', strtotime($d));
        if($day != 0 && $day != 6)$dates[] = $d;
    }
    return $dates;
}


//Create the student data array
$sql = mysql_query("SELECT student_id, student_name, roll_no FROM students ORDER BY student_name");
while($data = mysql_fetch_assoc($sql)){
    $student[$data['student_id']] = array('student_name'=>$data['student_name'],'roll_no'=>$data['roll_no']);   
}

//Create the register data array
$sql = mysql_query("SELECT student_id, attendance, dated FROM register WHERE dated BETWEEN '$date1' AND '$date2'");
while($data = mysql_fetch_assoc($sql)){
    $reg[$data['student_id']][$data['dated']] = $data['attendance'];    
}

//create column headings
$output = "<table><thead><th>#</th><th>Name</th><th>Roll No.</th>";
$dates = getDatesArray($date1,$date2);
foreach($dates as $date){
    $output .= "<th>$date</th>";  
}
$output .= "</tr></thead><tbody>";

//outer loop for student data
foreach($student as $k => $v){
    //create the student data
    $output .= "<tr><td>$k</td><td>{$v['student_name']}</td><td>{$v['roll_no']}</td>";
    //initialize the counter
    $datecount = $date1;
    //inner loop for attendance data    
    while($datecount <= $date2){
        //get the day number 0 =Sun, 1 = Mon etc
        $day = date('w', strtotime($datecount));
        //don't do Saturday or Sunday
        if($day != 0 && $day != 6){
            //add the attendanc emark for this date
            $output .= (isset($reg[$k][$datecount])) ? "<td>{$reg[$k][$datecount]}</td>" : '<td>?</td>'; 
        }
        //increment $datecount by 1 day
        $datecount = incrementDate($datecount);
    }
    //close off the row data
    $output .= "</tr>";  
}
$output .= "</tbody></table>";

[havish999]

thanks.. will test this out.