Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Jul 2012
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Smile Fetching values from MySql Database

    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..

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    I don't understand your question. The date is specified as having its own column in this table already.

  • #3
    New Coder
    Join Date
    Jul 2012
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Smile

    Quote Originally Posted by Fou-Lu View Post
    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

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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)?

  • #5
    New Coder
    Join Date
    Jul 2012
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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($dtStarttrue);
    $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.

  • #7
    New Coder
    Join Date
    Jul 2012
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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($dtStarttrue);
    $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..
    Last edited by havish; 10-17-2012 at 06:17 PM.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.

  • #9
    New Coder
    Join Date
    Jul 2012
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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?

  • #10
    New Coder
    Join Date
    Jul 2012
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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);
         
    }

  • #11
    New Coder
    Join Date
    Jul 2012
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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($dtStarttrue); 
    $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.

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    $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.

  • #13
    New Coder
    Join Date
    Jul 2012
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    $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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •