Create new html table based on sql query results Order by

[system]

I have a simple script using a sql query to get data from a data base and order information by a certain column.

I want to be able to crate a new html table every time the “Tier” changes as you can see in the query below. How can I break this down so that the tier shows up in a new additional HTML table?

The below gets and displays the info, but there is no way to differentiate the tiers. Any ideas on how to format this?

<?php echo ""; echo ""; class TableRows extends RecursiveIteratorIterator { function __construct($it) { parent::__construct($it, self::LEAVES_ONLY); } function current() { return ""; } function beginChildren() { echo ""; } function endChildren() { echo "" . "\n"; } } $servername = "localhost"; $username = "channelz"; $password = "passhere"; $dbname = "Channel_Lineup"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $conn->prepare("SELECT channel, description FROM Channel_LineUps WHERE Market_ID = 1 ORDER BY Tier ASC"); $stmt->execute(); // set the resulting array to associative $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { echo $v; } } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; echo "

Channel	Description
" . parent::current(). "

"; ?>

[ErnieAlex]

Not 100% sure I understand the question. Do you mean you have many tiers such as 1,2,3 and you want
all of the tier 1’s in a table and tier 2’s in another? That is easy… Just use the GROUP option in the query.
You can sort them in order by tier ASC and then also GROUP them by tier. At each GROUP break, you can
end the table and start a second one. Is that what you mean?

BUT, even easier, is just include the tier in the data. Then, when you display it do a check for the current
tier and fix the table code to show changes. Something like this:

Change the SELECT to include tier…
[php]
$stmt = $conn->prepare(“SELECT channel, description, tier FROM Channel_LineUps WHERE Market_ID = 1 ORDER BY Tier ASC”);
[/php]

And, alter the display to break the tables on tier-breaks…
[php]
// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
// start of table was already done somewhere in earlier code, we have to mark it as tier table #1
// This is just a temp variable I made up to check if it is the first table, if so, save the tier number
$first_tier=“yes”;
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
// Check if tier is first one, if so save for later…
if($first_tier==‘yes’) $current_tier=$v[‘tier’]
// If same tier continue, otherwise, break table and start new one…
if($current_tier==$v[‘tier’]){
echo $v;
} else {
echo “”;
echo “

”;
echo $v;
$current_tier=$v[‘tier’];
}
[/php]
SO, how that works? Well, it set a temp variable to indicate the first tier as there is no easy way with your
code to tell it to start at the first tier. I might not always be number 1 or whatever. So, the flag just forces
it to load the current tier from the first record. Then, if the current item matches the current tier it displays
it like before. If it does not match the current tier it is the NEXT tier and the code ends the current table and
starts a new one, displays the data and updates the current tier. When the loop finishes, no issues and the
last table still ends using your previous tag. Should work, I think. Not tested… but sound theory.
One small issue is that you are printing the entire $v array. You might have to change that to not print out
the tier field. Not sure on that part.

Hope that is what you need. Good luck!

[ErnieAlex]

Oh, sorry forgot…
If you want to just print the channel and description and not the tier, use something like this:
echo $v[‘channel’]." ".$v[‘description’]; instead of echo $v;

Let us know…