?
Solved

Format table with mysql data using php

Posted on 2014-08-27
2
Medium Priority
?
129 Views
Last Modified: 2014-09-29
I have an sql database and need to get output as a table. I have done this before but always gone horizontally with data. This time I need to go vertically:

Output would look like:

Category     Jan-14    Feb-14   Mar-14   Apr-14 (through months entered for current year)   Average
Cat 1             98.7           95.5       87.7        98.8                                                                                    95.18              
Cat 2             89.5           97.5       90.25      95.2                                                                                    93.11
Cat 3             98.7           95.5       87.7        98.8                                                                                    95.18  
Cat 4             89.5           97.5       90.25      95.2                                                                                    93.11  

Looking for some reading to learn how to do this loop (vertically) and do the average based on the months entered.

Database fields are:
ID
Month
Year
Cat 1
Cat 2
Cat 3
Cat 4
0
Comment
Question by:BHUC
2 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 40289021
This is called "matrix inversion" and it's a common problem in computer science curricula.  Here is my teaching example.  The first part is just setup - moving parts start on line 92.
http://www.iconoun.com/demo/matrix_inversion.php

<?php // /demo/matrix_inversion.php
error_reporting(E_ALL);


// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28506750.html


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id       INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, name     VARCHAR(24) NOT NULL DEFAULT ''
, question VARCHAR(24) NOT NULL DEFAULT ''
, answer   VARCHAR(2)  NOT NULL DEFAULT ''
)
"
;
// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING IT UP WITH TEST DATA
$sql
=
"
INSERT INTO my_table
( name,     question,    answer )
VALUES
( 'David',  'Question1', 'A' ),
( 'John',   'Question1', 'B' ),
( 'David',  'Question2', 'B' ),
( 'Mary',   'Question1', 'C' ),
( 'John',   'Question2', 'A' ),
( 'David',  'Question3', 'B' ),
( 'Mary',   'Question2', 'A' )
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// GET THE COLUMN TITLES (NAMES)
$cols  = array();
$sql   = "SELECT name FROM my_table GROUP BY name ORDER BY name";
$c_res = $mysqli->query($sql);
while ($row = $c_res->fetch_assoc())
{
    $cols[] = $row['name'];
}

// GET THE ROW TITLES (QUESTIONS)
$rows  = array();
$sql   = "SELECT question FROM my_table GROUP BY question ORDER BY question";
$r_res = $mysqli->query($sql);
while ($row = $r_res->fetch_assoc())
{
    $rows[] = $row['question'];
}


// A QUERY THAT CAN BE USED TO LOAD THE MATRIX WITH THE ANSWERS
$sql = "SELECT name, question, answer FROM my_table";


// CREATE THE EMPTY MATRIX OF COLUMNS (NAMES) AND ROWS (QUESTIONS)
$mat = array();
foreach ($cols as $c)
{
    foreach ($rows as $r)
    {
        $mat[$c][$r] = '-';
    }
}


// CREATE THE MATRIX WITH COLUMNS == NAMES AND ROWS == QUESTIONS
$res = $mysqli->query($sql);
while ($row = $res->fetch_assoc())
{
    $mat[$row['name']][$row['question']] = $row['answer'];
}

// CREATE THE HTML OUTPUT TABLE
$out = '<table>' . PHP_EOL;

// ADD THE COLUMN TITLES
$out .= '<tr>';

// FIRST COLUMN TITLE IS EMPTY
$out .= '<th></th>';
foreach ($cols as $c)
{
    $out .= '<th>' . $c . '</th>';
}
$out .= '</tr>';
$out .= PHP_EOL;

// ADD EACH ROW
foreach ($rows as $r)
{
    $out .= "<tr><td>$r</td>";
    foreach ($cols as $c)
    {
        $out .= '<td>' . $mat[$c][$r] . '</td>';
    }
    $out .= '</tr>';
    $out .= PHP_EOL;
}

// FINISH THE TABLE
$out .= '</table>';
echo $out;


// CREATE THE EMPTY MATRIX OF COLUMNS (QUESTIONS) AND ROWS (NAMES)
$mat = array();
foreach ($rows as $r)
{
    foreach ($cols as $c)
    {
        $mat[$r][$c] = '-';
    }
}


// CREATE THE MATRIX WITH COLUMNS == QUESTIONS AND ROWS == NAMES
$res = $mysqli->query($sql);
while ($row = $res->fetch_assoc())
{
    $mat[$row['question']][$row['name']] = $row['answer'];
}
// ACTIVATE THIS TO SEE THE MATRIX
// print_r($mat);


// CREATE THE HTML OUTPUT TABLE
$out = '<table>' . PHP_EOL;

// ADD THE COLUMN TITLES
$out .= '<tr>';

// FIRST COLUMN TITLE IS EMPTY
$out .= '<th></th>';
foreach ($rows as $r)
{
    $out .= '<th>' . $r . '</th>';
}
$out .= '</tr>';
$out .= PHP_EOL;

// ADD EACH ROW
foreach ($cols as $c)
{
    $out .= "<tr><td>$c</td>";
    foreach ($rows as $r)
    {
        $out .= '<td>' . $mat[$r][$c] . '</td>';
    }
    $out .= '</tr>';
    $out .= PHP_EOL;
}

// FINISH THE TABLE AND WRITE THE DATA TO THE BROWSER
$out .= '</table>';
echo $out;

Open in new window

0
 

Author Comment

by:BHUC
ID: 40289025
Thanks Ray, I will dive into this tonight when I can concentrate!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question