0

I need to dynamically pivot an html table using prepared statement in MySQL. The sql code is good (tested). What I want is how to integrate this query in PHP so I can show it in my web page. here is what I want to show

<table>
    <tr>
        <th>VOL_ID</th>
        <th>VOL_GIV_NAME</th>
        <th>VOL_FAM_NAME</th>
        <th>2015-02-03</th>
        <th>2015-02-04</th>
        <th>2015-02-05</th>
    </tr>
    <tr>
        <td>1</td>
        <td>Jack</td>
        <td>Napper</td>
        <td>(null)</td>
        <td>A</td>
        <td>P</td>
    </tr>
    <tr>
        <td>2</td>
        <td>Sylvie</td>
        <td>Tingle</td>
        <td>P</td>
        <td>(null)</td>
        <td>A</td>
    </tr>
    <tr>
        <td>3</td>
        <td>Floy</td>
        <td>Stambaugh</td>
        <td>(null)</td>
        <td>(null)</td>
        <td>P</td>
    </tr>
</table>

SQL:

SQL:
create table volunteer
(
  vol_id int,
  vol_giv_name varchar(15),
  vol_fam_name varchar(15),
  PRIMARY KEY (`vol_id`)

);
insert into volunteer values
(1, 'Jack', 'Napper'),
(2, 'Sylvie', 'Tingle'),
(3, 'Floy', 'Stambaugh');


create table volunteer_attendance
(
  att_id int,
  vol_id int,
  att_date varchar(15),
  vol_att char(1),
  PRIMARY KEY (`att_id`)
);
insert into volunteer_attendance values
(1,  '2', '"2015-02-03"', 'P'),
(2,  '1', '"2015-02-04"', 'A'),
(3,  '1', '"2015-02-05"', 'P'),
(4,  '2', '"2015-02-05"', 'A'),
(5,  '3', '"2015-02-05"', 'P');

Query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(va.att_date = ''',
      att_date,
      ''', va.vol_att, NULL)) AS ',
      att_date
    )
  ) INTO @sql
FROM volunteer_attendance;

SET @sql = CONCAT('SELECT v.vol_id
                    , v.vol_giv_name
                    , v.vol_fam_name, ', @sql, ' 
                   FROM volunteer v
                   LEFT JOIN volunteer_attendance AS va 
                    ON v.vol_id = va.vol_id
                   GROUP BY v.vol_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Salim
  • 1
  • 1
  • use `for loop` to traverse through the result you are getting from `mysql`. – RNK Feb 23 '15 at 16:49
  • I don't see why in your case you need prepared a statement to select from the database. Do you have a php script already? You first need to connect to database with your php script, then select your results, and then loop them in a cycle. http://www.w3schools.com/pHp/php_mysql_select.asp; http://stackoverflow.com/questions/8970117/mysql-query-result-into-php-array – kasparg Feb 23 '15 at 17:29
  • Thank you kasparg, a prepared statement because I want to pivot columns of the table (cross tabulations). Also the dates that I have will be incremented with the time – Salim Feb 23 '15 at 18:39

0 Answers0