2 Replies Latest reply on Sep 22, 2013 2:49 PM by Rob Hecker2

    how to update multiple rows in one query using php

    ARUNNARAYAN

      i am new to this can any one help me how to update multiple rows at a time i am doing an school attendance page

        • 1. Re: how to update multiple rows in one query using php
          David_Powers Adobe Community Professional (Moderator)

          To solve this you need a combination of PHP and MySQL techniques.

           

          Assuming your school attendance page contains a list of the students, create a checkbox for each student. Each checkbox should have as its value the student ID, and the name assigned to the checkbox should have an empty pair of square brackets after it like this:

           

          <input type="checkbox" name="student_id[]" value="1">Student 1

          <input type="checkbox" name="student_id[]" value="2">Student 2

          <input type="checkbox" name="student_id[]" value="3">Student 3

           

          When the form is submitted, the $_POST array will have all checked values in $_POST['student_id'] as a subarray.

           

          You can then use implode() to convert the subarray to a comma-separated list like this: $attended = implode($_POST['student_id']);

           

          You can then use that in a SQL query like this:

           

          $sql = "UPDATE students SET attended = TRUE WHERE student_id IN($attended)";

          1 person found this helpful
          • 2. Re: how to update multiple rows in one query using php
            Rob Hecker2 Adobe Community Professional & MVP

            Often the situation is such that you have multiple courses across a range of dates.So students may take more than one course, and you need to track attendance for each course date. The following graphic demonstrates this:

             

            attendence.gif

            In such a situation, you need four database tables as follows:

            students (student_id, student_name, etc.)

            courses (course_id, course_name, etc.)

            students_courses (student_id, course_id)

            attendance (student_id, course_id, dater)

             

            A fifth table may also be needed to define the dates of courses, but you may also be able to build this array programmatically by using PHP's robust date functions, which can give you, for instance, all the Tuesdays and Thursdays between a start date and end date.

             

            The students_courses table simply keeps track of which students are taking which courses, so it has just two columns for the primary keys of both of the main tables. The attendance table is similar, but it also includes a date field. The following view of the attendance table demonstrates this:

             

            attdata.gif

            So if David's solution does cover your needs, consider yourself lucky, because this could quickly grow from a beginner-appropriate project to a moderately advanced one.

            1 person found this helpful