Robert Castley Robert Castley - 1 month ago 9
SQL Question

SQL into PHP array for easy table creation

My DB schema is

CREATE TABLE "tests" (
"id" integer UNIQUE,
"type" integer NULL,
"monitor" integer NULL,
"name" varchar(255) NULL,
PRIMARY KEY (id, name)
);

CREATE TABLE "summary" (
"id" integer PRIMARY KEY AUTOINCREMENT,
"testid" integer NULL,
"timestamp" datetime NULL,
"total" integer NULL,
);


My SQL query is:

SELECT "s"."testid", AVG(total) AS "total" , DATE(timestamp) AS "d"
FROM "summary" AS "s"
INNER JOIN "tests" AS "t" ON s.testid = t.id
WHERE (timestamp >= date('now', '-7 days'))
GROUP BY "d", "testid" ORDER BY "d" DESC


Which returns the following data example data:

Test A | 2014-07-30 | 100
Test B | 2014-07-30 | 99
Test C | 2014-07-30 | 98
Test A | 2014-07-31 | 100
Test B | 2014-07-31 | 99
Test C | 2014-07-31 | 98
Test A | 2014-08-01 | 100
Test B | 2014-08-01 | 99
Test C | 2014-08-01 | 98


My desired end goal is to have a HTML table that displays the above data like this:

Test Name | 2014-07-30 | 2014-07-31 | 2014-08-01
------------------------------------------------
Test A 100 100 100
Test B 99 99 99
Test C 98 98 98


I am struggling to create the required PHP array from the SQL to then loop through and create the table.

Any help would be most gratefully received!

Answer

Since you didn't write any code it is harder for me to help you.

The basic idea is this:

$query = mysql_query("SELECT * FROM your_table");
echo "<table>";
while($row = mysql_fetch_assoc($query))
{

echo "<tr><td>$row['your_cell_name']</td></tr>";

}
echo "</table>";

When you will upload your code I will be able to help you more.

I assumed that you use php with mysql server.

edit:

What I would do is:

  1. find the distinct items at the test name column
  2. find the distinct dates
  3. Use basic SQL WHERE to find for example test A at 2014-07-30 and echo it into <td> tag

EXAMPLE:

$query = mysql_query("SELECT DISTINCT timestamp FROM summary");
echo "<table><tr><td>Test Name</td>"
while($row = mysql_fetch_array($query))
{
    foreach($row as $date)
    {
        echo "<td>{$date}</td>"
    }
}
echo "</tr><tr>";
$query = mysql_query("SELECT DISTINCT testid FROM summary");
while($row = mysql_fetch_array($query))
{
    foreach($row as $testid){
        echo "<td>{$testid}</td>";
        $sum_query = "YOUR QUERY FOR THE SCORE OF THE TEST";//you can use $testid to find all of the summery at the distinct date
        //Hope you understand what I did
    }
}
echo "</tr>";
Comments