Shortening a repetitive 1400-line MySQL UNION query using PHP

Question

I have a MYSQL table that is 1275 fields wide. Each row in the table represents a single class of students, with 17 fields per student, X up to 75 students per class, so 17 X 75 = 1275 fields.

I have devised an SQL UNION query that successfully pulls the students into another table, with each student on a single row.

Now, I want to use this UNION query as part of a PHP program. I have "imported" the query into PHP as it is. But, isn't there a way to use PHP to shorten the SQL query? Shamelessly, here is my code:

    $sql = "
    INSERT INTO $t_mem2
    SELECT localcourse
         , statecourse
         , coursetitle
         , semester
         , section
         , teachercode
         , teachername
         , meetingcode
         , classpop
         , student_id_01 AS student_id
         ,        sex_01 AS sex
         ,        dob_01 AS dob
         ,      grade_01 AS grade
         ,     ethnic_01 AS ethnic
         ,  last_name_01 AS last_name
         , first_name_01 AS first_name
         , $c_sch        AS sch_code
    FROM $t_mem1
    UNION
    SELECT localcourse
         , statecourse
         , coursetitle
         , semester
         , section
         , teachercode
         , teachername
         , meetingcode
         , classpop
         , student_id_02 AS student_id
         ,        sex_02 AS sex
         ,        dob_02 AS dob
         ,      grade_02 AS grade
         ,     ethnic_02 AS ethnic
         ,  last_name_02 AS last_name
         , first_name_02 AS first_name
         , $c_sch        AS sch_code
    FROM $t_mem1
    UNION 
    SELECT localcourse
         , statecourse
         , coursetitle
<...snip..............................>
     , teachername
     , meetingcode
     , classpop
     , student_id_75 AS student_id
     ,        sex_75 AS sex
     ,        dob_75 AS dob
     ,      grade_75 AS grade
     ,     ethnic_75 AS ethnic
     ,  last_name_75 AS last_name
     , first_name_75 AS first_name
     , $c_sch        AS sch_code
  FROM $t_mem1
 ORDER
    BY localcourse
     , statecourse
     , semester
     , section
     , teachername
     , meetingcode
     , last_name
     , first_name" ;

Answer 1

Let me be the first to say OMG!

Second, start reading about normalization: http://en.wikipedia.org/wiki/Database_normalization

Third: use a loop and replace 01..75 with printf('%02d', $i)

Answer 2

First off, this query indicates that your database schema is very, very poor.

That aside, yes, you can shorten the query with PHP:

$query = "INSERT INTO $t_mem2 ";
for ($i = 1; $i <= 75; $i++) {
    if ($i > 1) {
        $query .= ' UNION ';
    }
    $s = "$i";
    if ($i < 10) {
       $s = '0'.$s;
    }
    $query .= "SELECT localcourse
         , statecourse
         , coursetitle
         , semester
         , section
         , teachercode
         , teachername
         , meetingcode
         , classpop
         , student_id_{$s} AS student_id
         ,        sex_{$s} AS sex
         ,        dob_{$s} AS dob
         ,      grade_{$s} AS grade
         ,     ethnic_{$s} AS ethnic
         ,  last_name_{$s} AS last_name
         , first_name_{$s} AS first_name
         , $c_sch        AS sch_code FROM $t_mem1";
}
$query .= " ORDER
BY localcourse
 , statecourse
 , semester
 , section
 , teachername
 , meetingcode
 , last_name
 , first_name" ;

This will "shorten the query" by generating it in PHP code. If, on the other hand, you're looking to make the query as it hits the database shorter, that is also possible; just modify the loop above to start $query anew in each iteration, and glom all the results together in one array. You'll end up with 75 queries made, and PHP will perform the UNION.

Answer 3

I call WoLpH's OMG, and I raise a WTF.

To explain a bit more what the others mean by "normalization", you want to have (at least) two tables here: one for courses, one for students (probably also one for teachers). All the fields with a number go into the students table, teachername and teachercode (possibly with a id) go into teachers table, and everything else goes into the courses table, along with some new fields: teacher_id (or teachercode) and student_id, and preferably its own AUTOINCREMENTed id as well. Then, when you want to get the results similar to your 70Kb query, you do this:

SELECT C.id AS course_id
     , C.localcourse
     , C.statecourse
     , C.coursetitle
     , C.semester
     , C.section
     , T.teachercode
     , T.teachername
     , C.meetingcode
     , C.classpop
     , C.student_id
     , S.sex
     , S.dob
     , S.grade
     , S.ethnic
     , S.last_name
     , S.first_name
FROM courses C
JOIN students S ON C.student_id = S.id
JOIN teachers T ON C.teacher_id = T.id

Also, you probably don't need classpop, you can get it with SELECT COUNT(C.student_id) AS classpop ... GROUP BY C.id.