CROSSTAB REPORT - MYSQL

Question

I want generate a HTML report, using PHP and the following Mysql data:

VIDEOS

id | title
01 | video1
02 | video2
03 | video3
04 | video4

JURORS

id | Name
01 | juror1
02 | juror2
03 | juror3

REVIEWS

 id_video    | id_juror  | grade
    01       | 01        | 5,2
    02       | 01        | 4,5
    03       | 01        | 2,7
    04       | 01        | 7,0
    01       | 02        | 3,2
    02       | 02        | 9,6
    03       | 02        | 4,7
    04       | 02        | 8,3

The OUTPUT should be something like that:

       | juror1  | juror2  | juror3
video1 |   5,2   |   3,2   | NULL
video2 |   4,5   |   9,6   | NULL
video3 |   2,7   |   4,7   | NULL
video4 |   7,0   |   8,3   | NULL

I have tried many different querys and crosstab tutorials, but they would use CASE for a limited quantity of videos and jurors, but I need it to be dynamic.

score 1 · Answer 1 · answered Jan 23 '12 at 21:41

In MySQL, you can't do this with just a query. One approach, described at http://www.databasejournal.com/features/mysql/article.php/3871556/article.htm, is to create a stored-procedure that finds all the possible videos and all the possible jurors and then dynamically builds a SQL query. But since you're writing in PHP, I think you'd find it easier to do that in PHP than in a stored procedure. Alternatively, you can run a straightforward SELECT videos.title, jurors.name, reviews.grade FROM reviews JOIN videos ON videos.id = reviews.id_video JOIN jurors ON jurors.id = reviews.id_juror and handle the translation in PHP.

score 0 · Answer 2 · answered Jan 22 '15 at 03:50

Using MySQL Pivot table generator you can do this report instantly . You will be asked to enter the data source of your columns , Rows and Values of your report . In the columns : You select the JURORS table and the name column as in the following screenshot:

enter image description here

Similarly you should select the Videos table and Title column as the data source for the rows then finally the reviews table and grade column for the values and you get your report , which will be fed automatically from your database

For more information you can check the following tutorial : http://mysqlpivottable.net/Tutorial.html

CROSSTAB REPORT - MYSQL

2 Answers2