PHP SQL Query Output Help

[ncurran217]

Right now I have a code that outputs results into a table:

 

<?php
include 'includes/db_connect.php';
$List = $_GET['List'];
if( $connection === false )
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));
}


$query = " SELECT LISTCODE, YEAR, COUNT(YEAR) AS Count
FROM Names
GROUP BY LISTCODE, RIGHT(LISTCODE, 2), YEAR
HAVING (RIGHT(LISTCODE, 2) = '$List')
ORDER BY LISTCODE, YEAR ";
$result = sqlsrv_query($connection,$query);


// each array key is the database column name, the corresponding value is the legend/heading to display in the HTML table
// the order of the items in this array are the order they will be output in the HTML table
$fields = array('LISTCODE'=>'ListCode','YEAR'=>'Year','Count'=>'Count');

// start table and produce table heading
echo "<table>\n<tr>";
foreach($fields as $legend){
echo "<th>$legend</th>";
}
echo "</tr>\n";


// output table data
while($row = sqlsrv_fetch_array( $result,SQLSRV_FETCH_ASSOC))
{
echo "<tr>";
foreach($fields as $key=>$not_used)
{
echo "<td>$row[$key]</td>";
}
echo "</tr>\n";
}
echo "</table>\n";
sqlsrv_free_stmt ($result);
sqlsrv_close( $connection);
?>

 

That all works great and the attached picture originalqueryoutput is what it looks like. Just a little hard to read. What I would like to is have the output look something like the attached picture wantedqueryoutput, which I just did in excel to show an example of what I am going for. Is this possible? And how would I even begin to manipulate the data rows from the query to the table?

 

Thanks in advance for the help!

[devilsvein]

If I followed correctly you would have something like:

 

1) table header.....so simple html

 

<table border=1 width=500px><tr><td>List/code</td><td> Year</td><td> Count</td></tr>

 

2)Create a while loop that executes each row of your table until it reaches a set amount. While loop would be something like

 

while (condition)
{
echo "<tr><td>003EL</td><td>$year</td><td>$count</td></tr>";

 

Then echo out the </table>

Edited January 18, 2013 by devilsvein

[ncurran217]

hmm I will try a few things with that and see what I can put together.

[ncurran217]

Also, the 003EL and 004EL are not the only ones, there are hundreds of those, so hard cording that in wouldnt be an option. But will try and play with it and see what I can get to work. If you have any options on how I would do this that will be very helpful. Thanks again for the help!

[ncurran217]

Ok, I am just confusing myself, on trying to put this together. I figured there could be some type of verifying of the next row that the List Code is the same, if the list code is the same it will echo out the counts of the years in the columns. Once it sees the List Code does not match it will move to the next row and start the process again. But i have not linked while statements with actual conditions or with foreach statement with conditions. All I know is that I am extremely confused on how to even do this. Can anyone right up an example of how this would be done. Or point me to where there is a tutorial with something similar to this? Thank you in advance.

[Barand]

Basically this, although at the moment it assumes all listcodes have data for same years. But it should get you started in the right direction

 

$data = array();
/******************
* Store in array
*/
while($row = sqlsrv_fetch_array( $result,SQLSRV_FETCH_ASSOC)) {
   $data[$row['listcode']][$row['year']] = $row['count'];
}

/******************
* print the array
*/
foreach ($data as $listcode => $yearcounts) {
   echo "<tr><th>$listcode</th>" ;
   foreach ($yearcounts as $count) {
    echo "<td>$count</td>";
   }
   echo "</tr>";
}

[ncurran217]

Basically this, although at the moment it assumes all listcodes have data for same years. But it should get you started in the right direction

 

$data = array();
/******************
* Store in array
*/
while($row = sqlsrv_fetch_array( $result,SQLSRV_FETCH_ASSOC)) {
$data[$row['listcode']][$row['year']] = $row['count'];
}

/******************
* print the array
*/
foreach ($data as $listcode => $yearcounts) {
echo "<tr><th>$listcode</th>" ;
foreach ($yearcounts as $count) {
 echo "<td>$count</td>";
}
echo "</tr>";
}

 

I get Unidentified Indexes for Listcode year and count at this line:

 

$data[$row['listcode']][$row['year']] = $row['count']

[Barand]

Case sensitive?

[ncurran217]

That worked. I always forget about the case sensitive part.

[ncurran217]

Now what if every LISTCODE do not have data for all the years?

[Psycho]

Now what if every LISTCODE do not have data for all the years?

 

I was trying to JOIN the table on a subquery of the same table using a DISTINCT query on the years to try and get the data such that there would always be a record for all the applicable years for each listcode - but didn't have any success. That would be the easiest to code for. The other option is to pre-process the data into an array beforehand. If someone can figure out the query I would go that route.

[Psycho]

This tweak to Barand's code should work:

$data = array();
$years = array();
/******************
* Store in array
*/
while($row = sqlsrv_fetch_array( $result,SQLSRV_FETCH_ASSOC))
{
   $data[$row['LISTCODE']][$row['YEAR']] = $row['COUNT'];
   if(!in_array($row['YEAR'], $years))
   {
    $years[] = $row['YEAR']
   }
}

/******************
* print the array
*/
foreach ($data as $listcode => $counts)
{
   echo "<tr><th>$listcode</th>";
   foreach ($years as $year)
   {
    $count = isset($counts[$year]) ? $counts[$year] : 0;
    echo "<td>$count</td>";
   }
   echo "</tr>";
}

[Barand]

This should ensure data for all years for all codes

 

SELECT C.listcode, C.year, COUNT(names.year) as count
FROM
   (
    SELECT *
    FROM
    (SELECT DISTINCT listcode FROM Names) as A
    CROSS JOIN
    (SELECT DISTINCT year FROM Names) as B
   ) as C
   LEFT JOIN names USING (listcode, year)
WHERE RIGHT(C.listcode, 2) = 'EL'
GROUP BY listcode, year;

[Barand]

putting it all together (mysqli rather than sqlsrv so I could test)

 

$query = " SELECT C.listcode, C.year, COUNT(names.year) as count
FROM
(
 SELECT *
 FROM
 (SELECT DISTINCT listcode FROM Names) as A
 CROSS JOIN
 (SELECT DISTINCT year FROM Names) as B
) as C
LEFT JOIN names USING (listcode, year)
WHERE RIGHT(C.listcode, 2) = 'EL'
GROUP BY listcode, year; ";

$result = $mysqli->query($query);

$data = array();
/******************
* Store in array
*/
while($row = $result->fetch_assoc()) {
$data[$row['listcode']][$row['year']] = $row['count'];
}

echo "<table border='1'>";
/*******************
* table headings
*/
list ($code, $totals) = each($data);
$heads = array_keys($totals);
echo "<tr><th>LISTCODE</th>";
foreach ($heads as $y) {
echo "<th>$y</th>";
}
echo "</tr>\n";
/******************
* print the array
*/
foreach ($data as $listcode => $yearcounts) {
echo "<tr><th>$listcode</th>" ;
foreach ($yearcounts as $count) {
 echo "<td>$count</td>";
}
echo "</tr>";
}
echo "</table>" ;

Edited January 18, 2013 by Barand

[ncurran217]

Ok, well right now it seems the query is what is throwing everything else off. When I put it into SSMS it doesn't like it. I have to move some of the code around.

Edited January 21, 2013 by ncurran217

[Barand]

I don't know if sqlsrv supports an explicit CROSS JOIN. You can replace it with INNER JOIN or just JOIN.

 

I used CROSS to show that it was intentional that there was no ON clause.

[ncurran217]

I don't know if sqlsrv supports an explicit CROSS JOIN. You can replace it with INNER JOIN or just JOIN.

 

I used CROSS to show that it was intentional that there was no ON clause.

 

From what I see Cross Join is in SQL Server. Just trying to understand what all is going on with the Query you wrote up. I think I have part of it, but so not getting what really is happening within it. Sorry for the lack of understanding on this.

[Barand]

The inner table subquery selects each code used and each year used. Using a cross join (cartesian) joins each code with every date so you have all combinations

 

a,b,c CROSS JOIN 1,2,3 gives a1, a2, a3, b1, b2, b3, c1, c2, c3.

 

It then left joins this query to your data so we get totals where there is matching data and zero where there is none. This ensures that we have a total (even if it zero) for every code/year.

Edited January 21, 2013 by Barand

[Barand]

LEFT JOIN names USING (listcode, year)

 

That is line objected to.

 

Change to

 

LEFT JOIN names ON C.listcode = names.listcode AND C.year = names.year

[ncurran217]

Well, I was getting there, haha.

 

Also, one thing I changed was:

 

GROUP BY listcode, year

 

To:

 

GROUP BY c.listcode, c.year

[ncurran217]

Thanks for the help so much!!!