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 8 of 8
  1. #1
    New Coder
    Join Date
    Jan 2011
    Posts
    92
    Thanks
    8
    Thanked 0 Times in 0 Posts

    updating multiple columns to database depending on checkbox selection

    Hello,

    I am trying to update attendance table.

    My database is like this

    updating multiple columns to database depending on checkbox selection-db.png

    and my form is like this

    Code:
    <form action="update_mul_attend1.php" method="post" name="form1">
    <tr bgcolor="#ffffff">
    
    <td>R No</td>
    <td>Name</td>
    <td>Class</td>
    <td>Sec</td>
    <td>1</td>
    <td>2</td>
    <td>3</td>
    <td>4</td>
    <td>5</td>
    <td>6</td>
    <td>7</td>
    <td>8</td>
    <td>9</td>
    <td>10</td>
    <td>11</td>
    <td>12</td>
    <td>13</td>
    <td>14</td>
    <td>15</td>
    <td>16</td>
    <td>17</td>
    <td>18</td>
    <td>19</td>
    <td>20</td>
    <td>21</td>
    <td>22</td>
    <td>23</td>
    <td>24</td>
    <td>25</td>
    <td>26</td>
    <td>27</td>
    <td>28</td>
    <td>29</td>
    <td>30</td>
    <td>31</td>
    <td>SELECT</td>
    
    <?php
    $count=mysql_num_rows($query);
    if($count==0)
    {
    echo "<tr><td>SORRY NONE OF THE RECORD FOUND IN TABLE</td></tr>";
    }
    else
    {
    
    while($row=mysql_fetch_array($query))
    {
    
    ?>
    
    <tr><td><input name="enroll_no" type="text" readonly value="<?php echo $row['enroll_no']; ?>"  style="width:120px;"></td><td><input type="text" name="name[]" readonly value="<?php echo $row['name']; ?>"></td><input type="hidden" value="<?php echo $row['mid']; ?>" name="mid[]"><td><input name="class[]" type="text"  readonly value="<?php echo $row['class']; ?>" style="width:30px;"></td><td><input type="text" name="section[]" readonly value="<?php echo $row['section']; ?>" style="width:20px;"></td><td><input type="text" value="<?php echo $row['day01']; ?>" name="day01[]" style="width:20px;"></td>
    <input type="checkbox" name="<?php echo $row['day01'],$row['enroll_no']?>" value="P" />
    
    <td><input type="text" value="<?php echo $row['day02']; ?>" name="day02[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day03']; ?>" name="day03[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day04']; ?>" name="day04[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day05']; ?>" name="day05[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day06']; ?>" name="day06[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day07']; ?>" name="day07[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day08']; ?>" name="day08[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day09']; ?>" name="day09[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day10']; ?>" name="day10[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day11']; ?>" name="day11[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day12']; ?>" name="day12[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day13']; ?>" name="day13[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day14']; ?>" name="day14[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day15']; ?>" name="day15[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day16']; ?>" name="day16[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day17']; ?>" name="day17[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day18']; ?>" name="day18[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day19']; ?>" name="day19[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day20']; ?>" name="day20[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day21']; ?>" name="day21[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day22']; ?>" name="day22[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day23']; ?>" name="day23[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day24']; ?>" name="day24[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day25']; ?>" name="day25[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day26']; ?>" name="day26[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day27']; ?>" name="day27[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day28']; ?>" name="day28[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day29']; ?>" name="day29[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day30']; ?>" name="day30[]" style="width:20px;"></td>
    <td><input type="text" value="<?php echo $row['day31']; ?>" name="day31[]" style="width:20px;"></td>
    <td><input name="selector[]" type="checkbox" value="<?php echo $row['enroll_no']; ?>" />
    										</td></tr>
    
    <?php
    
    }
    }
    ?>
    	<tr><td><input type="submit" class="delete_multiple" value="Update Attendance" name="update_multiple"  /></td></tr>
    </form>
    </table>

    update_mul_attend1.php

    Code:
    if (isset($_POST['update_multiple']))
    {
    $enroll_no = $_POST['enroll_no'];
    $id=$_POST['selector'];
    $name = $_POST['name'];
    
    
    //echo $name;
    $N = count($id);
    $class=$_POST['class'];
    $section = $_POST['section'];
    $mid = $_POST['mid'];
    //$day01 = $_POST['day01'];
    var_dump($_POST['day01']);
    //print_r($day01);
    $day02 = $_POST['day02'];
    $day03 = $_POST['day03'];
    $day04 = $_POST['day04'];
    $day05 = $_POST['day05'];
    $day06 = $_POST['day06'];
    $day07 = $_POST['day07'];
    $day08 = $_POST['day08'];
    $day09 = $_POST['day09'];
    $day10 = $_POST['day10'];
    $day11 = $_POST['day11'];
    $day12 = $_POST['day12'];
    $day13 = $_POST['day13'];
    $day14 = $_POST['day14'];
    $day15 = $_POST['day15'];
    $day16 = $_POST['day16'];
    $day17 = $_POST['day17'];
    $day18 = $_POST['day18'];
    $day19 = $_POST['day19'];
    $day20 = $_POST['day20'];
    $day21 = $_POST['day21'];
    $day22 = $_POST['day22'];
    $day23 = $_POST['day23'];
    $day24 = $_POST['day24'];
    $day25 = $_POST['day25'];
    $day26 = $_POST['day26'];
    $day27 = $_POST['day27'];
    $day28 = $_POST['day28'];
    $day29 = $_POST['day29'];
    $day30 = $_POST['day30'];
    $day31 = $_POST['day31'];
    for($i=0; $i < $N; $i++)
    {
    	
    $sql = "update attendance set day01='".$day01[$i]."', day02='".$day02[$i]."', day03='".$day03[$i]."', day04='".$day04[$i]."', day05='".$day05[$i]."', day06='".$day06[$i]."', day07='".$day07[$i]."', day08='".$day08[$i]."', day09='".$day09[$i]."', day10='".$day10[$i]."', day11='".$day11[$i]."', day12='".$day12[$i]."', day13='".$day13[$i]."', day14='".$day14[$i]."', day15='".$day15[$i]."', day16='".$day16[$i]."', day17='".$day17[$i]."', day18='".$day18[$i]."', day19='".$day19[$i]."', day20='".$day20[$i]."', day21='".$day21[$i]."', day22='".$day22[$i]."', day23='".$day23[$i]."', day24='".$day24[$i]."', day25='".$day25[$i]."', day26='".$day26[$i]."', day27='".$day27[$i]."', day28='".$day28[$i]."', day29='".$day29[$i]."', day30='".$day30[$i]."', day31='".$day31[$i]."' where enroll_no='".$id[$i]."'" ;
    }

    }
    It displays all the records from the database. If i want to edit or mark someone Absent (A), i will do it and select the checkbox infront of that and then submit it to database. In my code, update happens only if i select 1st record with others , or else it doesn't update. I mean its taking the value for those records also which is not selected.

    Can somebody tell me where i am going going? I have been struggling in this

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    In your previous thread, it was mentioned that your database table is laid out like a spreadsheet and this will make "it very hard for a program or query to insert, find, or update information." It was also stated that "Your database table should simply store the data, one item per row."

    Each piece of data that you are displaying should be its own row in a database table. Also, you should only store data that's needed. Since the normal/majority case is someone is present, you wouldn't store Present information. You would only store data when someone is Absent.

    Your table should have a primary id, a course_id, student_id, and date. To display the information for any range of dates (i.e. the current month), you would retrieve any data that matches the range of dates and display it as you loop over the range of dates (you would also never hard code all 31 possible sets of html and php code, you would let the computer produce that using a loop.)
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,472
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    My question is, why are you using checkboxes?

    Each day would be a choice of 'A', 'P', or blank (if the day hasn't arrived yet).

    Wouldn't that be a <select> box?

    <select name="day[]">
    <option value=" "></option>
    <option value="A"></option>
    <option value="P"></option>
    </select>


    EDIT:

    CFM beat me to this one ... he posted while I was still typing ... but I'll leave mine here anyhow ....

    ... and I'm not sure about 31 columns of days. What happens after 31 days? You don't keep track of every day for several months?

    I guess I would have one DB Table for the students info ... ID, name, etc. and then have another DB Table for attendance.

    Each row of the attendance table is a student ID, the date, and whether or not they attended. You would have a lot of rows in that table, but it would be easy to manage. You could group by student, class, range of dates, and do reports on attendance statistics.

    You could keep records of all days, for all students, for several years.

  • #4
    New Coder
    Join Date
    Jan 2011
    Posts
    92
    Thanks
    8
    Thanked 0 Times in 0 Posts
    @mlseim: Yeah, even i am not happy with this method, but i am bit poor in this. So i don't know how to do this student attendance, how to update everyday and how to display. Can you please explain me how to do it . Please help

  • #5
    New Coder
    Join Date
    Jan 2011
    Posts
    92
    Thanks
    8
    Thanked 0 Times in 0 Posts
    CFMaBiSmAd : Can you please explain much, sorry for taking your precious time, but i really need this solution. And i am not able to do it . Is there any example for this kind of student attendance

  • #6
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,472
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    I commend you for learning PHP/MySQL. It will be something of a huge advantage to you for future projects.

    I can only speak for myself, but what you should be coding will take a fair amount of time. For me I'm not able to spend time on it. You could hire someone (freelancer) to at least get you set-up and running in the right direction. You'll also learn as you go.

    There's also way more to this than you are discussing. Someone has to enter the data, be able to go back and edit the data, pull-out data for single or multiple reports. Possibly create some PDF outputs for printed "paper" records. Enter/edit/delete students, classes/courses, and most likely, other things will be added that haven't been though of yet.

    This is not something that can be done very well using a forum like this.

  • #7
    New Coder
    Join Date
    Jan 2011
    Posts
    92
    Thanks
    8
    Thanked 0 Times in 0 Posts
    @mlseim: Thanks a lot for the suggestions.

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    The following is an incomplete example that shows a small part of how you can do this for the case of having the 'attendance' table with just the (Absent) rows that I described -

    Code:
    <?php
    $mysqli = new mysqli(your database credentials would go here...);
    
    // use a $_GET variable to supply the desired date YYYYMM, so that a date selector could be used to pick any year-month
    $get_date = isset($_GET['date']) ? $_GET['date']: date('Ym'); // use submitted year-month, if any, or current year-month
    
    $q_date = $mysqli->real_escape_string($get_date);
    // get any attendance (absent) data for the selected year-month
    $query = "SELECT * FROM attendance WHERE EXTRACT(YEAR_MONTH FROM date) = '$q_date' ORDER BY course_id, student_id";
    $result = $mysqli->query($query) or die("Query failed: $query<br>Error: $mysqli->error");
    // pre-process data into a format that allows direct testing later
    $attendance_data = array();
    while($row = $result->fetch_assoc()){
    	$attendance_data[$row['date']][$row['course_id']][$row['student_id']] = $row;
    }
    
    
    // at this point you would get a list of courses/students in those courses (for the current instructor filling in the attendance data)
    // some dummy data (two courses with two students in each course) -
    $roster[1][] = 1; // first index is the course_id, data is the student_id
    $roster[1][] = 2; // first index is the course_id, data is the student_id
    $roster[3][] = 4; // first index is the course_id, data is the student_id
    $roster[3][] = 5; // first index is the course_id, data is the student_id
    
    
    $start_date = $get_date . '01'; // produce a YYYYMM01 date (first day of the selected month)
    $num_days = date('t',strtotime($start_date)); // number of days in the selected month
    $days = range(1,$num_days); // make the days in the month an array. you would remove things like weekend days at this point
    $base_date = date('Y-m-',strtotime($start_date)); // date for iteration/testing purposes
    $heading_col = $num_days + 2; // colspan value for the table heading. the actual 'extra' columns (2) is dependent on what is going to be displayed
    $heading_date = date('F Y',strtotime($start_date)); // for display purposes only
    
    // output the form and table heading
    echo "<form method='post' action='formaction.php'>";
    echo "<table><tr><th colspan='$heading_col'>$heading_date</th></tr>";
    echo "<tr><td>Course id</td><td>Student id</td>";
    foreach($days as $x){
    	echo "<th>".sprintf('%02d',$x)."</th>";
    }
    echo "</tr>\n";
    
    // output the form data
    $current_date = date('Y-m-d');
    foreach($roster as $course_id=>$arr){
    	foreach($arr as $student_id){
    		echo "<tr><td>$course_id</td><td>$student_id</td>";
    		foreach($days as $x){
    			$todays_date = $base_date . sprintf('%02d',$x);
    			echo "<td><select name='attendance[$todays_date][$course_id][$student_id]'>";
    			if($todays_date > $current_date){
    				echo "<option value = ''></option>"; // if date > current date
    			}
    			$absent = isset($attendance_data[$todays_date][$course_id][$student_id]) ? "selected" : '';
    			echo "<option value = 'A' $absent>A</option>"; // if data present
    			$present = ($todays_date <= $current_date && $absent == '') ? "selected" : '';
    			echo "<option value = 'P' $present>P</option>"; // if no data and date <= current date
    			echo "</select></td>";
    		}
    		echo "</tr>\n";
    	}
    }
    echo "</table>";
    echo "<input type='submit'></form>";
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

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