Displaying records correctly from array

[jgd12345]

Hi, I need to display the number of guitar tabs, drum tabs, bass tabs, lyrics for each band. I understand that query below gets the information but I don't know how to display it for each band. Here's the code so you can see what I'm on about:

$query = mysql_query("
SELECT
b.id
, t.filetype
, count(t.id) AS thecount
FROM
bands b
tabs t
WHERE
b.id=t.bid
GROUP BY
b.id
, t.filetype
LIMIT 0, 20

");

while($band = $db->fetch_array($query)) {
echo "band id: $band[id] | count: $band[thecount] | filetype: $band[filetype]<br />";
}

This is the output:

band id: 1 | count: 2 | filetype: Bass Tab
band id: 1 | count: 2 | filetype: Lyrics
band id: 2 | count: 85 | filetype: Guitar Tab
band id: 4 | count: 3 | filetype: Bass Tab
band id: 4 | count: 1 | filetype: Guitar Tab
band id: 4 | count: 25 | filetype: Lyrics
band id: 5 | count: 4 | filetype: Bass Tab
band id: 5 | count: 2 | filetype: Lyrics
band id: 6 | count: 1 | filetype: Lyrics
band id: 8 | count: 1 | filetype: Lyrics
band id: 10 | count: 1 | filetype: Bass Tab
band id: 11 | count: 1 | filetype: Bass Tab
band id: 12 | count: 8 | filetype: Guitar Tab
band id: 12 | count: 94 | filetype: Lyrics
band id: 13 | count: 1 | filetype: Bass Tab
band id: 13 | count: 4 | filetype: Guitar Tab
band id: 14 | count: 3 | filetype: Lyrics
band id: 15 | count: 1 | filetype: Bass Tab
band id: 17 | count: 11 | filetype: Bass Tab
band id: 17 | count: 1 | filetype: Guitar Tab

The values look correct to me but the bands come in seperate rows (for each filetype) of the array now. Therefore in the loop I did a check to see that the band had not been echoed in the previous statement:

while($band = $db->fetch_array($query)) {
if($bandid != $band['id]) {
echo "band id: $band[id] | count: $band[thecount] | filetype: $band[filetype]<br />";
}

$bandid = $band['id']
}

That was ok but now I wish to display the relative count for each filetype for that band. This is where I'm stuck because sometimes the value doesn't exist so I get errors.

I'd be greatful if someone could help. Thanks

 

[danomac]

I'm assuming you are looking to have output similar to:

Band id: 15
count: 1 | filetype: Bass Tab
Band id: 17
count: 11 | filetype: Bass Tab
count: 1 | filetype: Guitar Tab

If this is what you are looking for, then you need to go through the array and keep track of when your band id changes. Something like this would probably work (untested):

$currentband = 0;
while($band = $db->fetch_array($query)) {
  if($currentband == $band['id']) {
    echo "&nbsp;&nbsp;count: ${band['thecount']} | filetype: ${band['filetype']}<BR>";
  } else {
    echo "band id: ${band['id']}<BR>";
    echo "&nbsp;&nbsp;count: ${band['thecount']} | filetype: ${band[filetype]}<BR>";
    $currentband = $band['id'];
  }
}

Basically all that happens there is that when it notices a band id change, the header is echoed as well.

 

[jgd12345]

Hi the above was exactly what I was after then I ran into another problem. I wish to limit the initial query to only display 20 bands (eg rows 21-40 or 41-60). Since the query contains multiple rows for each band and this is not a consistent number for each band this can not be done in a single query I believe.

Therefore I went for:

$bandids = array();
$query = mysql_query("SELECT * FROM bands LIMIT 0, 20");

while($band = mysql_fetch_array($query)) {
$bandids[] = $band['id'];
}

$bandids = implode(', ', $bandids);

$count = array();
$query = mysql_query("SELECT bid, filetype, count(id) AS thecount FROM tabs WHERE bid IN ($bandids) GROUP BY bid, filetype");

while($tab = mysql_fetch_array($query)) {
$count["$tab[bid]"]["$tab[filetype]"] = $tab['thecount'];
}

$query = mysql_query("SELECT * FROM bands LIMIT 0, 20");

while($band = mysql_fetch_array($query)) {
if(!isset($count["$band[id]"]['Bass Tab'])) {
$count["$band[id]"]['Bass Tab'] = 0;
}

if(!isset($count["$band[id]"]['Drum Tab'])) {
$count["$band[id]"]['Drum Tab'] = 0;
}

if(!isset($count["$band[id]"]['Guitar Tab'])) {
$count["$band[id]"]['Guitar Tab'] = 0;
}

if(!isset($count["$band[id]"]['Lyrics'])) {
$count["$band[id]"]['Lyrics'] = 0;
}

echo "band id: " . $band['id'] . " | bass tabs: " . $count["$band[id]"]['Bass Tab']
. " | drum tabs: " . $count["$band[id]"]['Drum Tab']
. " | guitar tabs: " . $count["$band[id]"]['Guitar Tab']
. " | lyrics: " . $count["$band[id]"]['Lyrics'] . "<br />";
}

Which gives the output:

band id: 1 | bass tabs: 2 | drum tabs: 0 | guitar tabs: 0 | lyrics: 2
band id: 2 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 85 | lyrics: 0
band id: 3 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0
band id: 4 | bass tabs: 3 | drum tabs: 0 | guitar tabs: 1 | lyrics: 25
band id: 5 | bass tabs: 4 | drum tabs: 0 | guitar tabs: 0 | lyrics: 2
band id: 6 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 0 | lyrics: 1
band id: 7 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0
band id: 8 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 0 | lyrics: 1
band id: 10 | bass tabs: 1 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0
band id: 11 | bass tabs: 1 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0
band id: 12 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 8 | lyrics: 94
band id: 13 | bass tabs: 1 | drum tabs: 0 | guitar tabs: 4 | lyrics: 0
band id: 14 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 0 | lyrics: 3
band id: 15 | bass tabs: 1 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0
band id: 17 | bass tabs: 11 | drum tabs: 0 | guitar tabs: 1 | lyrics: 0
band id: 16 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0
band id: 18 | bass tabs: 1 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0
band id: 19 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 0 | lyrics: 35
band id: 20 | bass tabs: 16 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0
band id: 21 | bass tabs: 0 | drum tabs: 0 | guitar tabs: 0 | lyrics: 0

This is exactly what I am after however I feel that it is still too long. I tried using just 2 queries intially and calling the first query $bandquery and referring to that one on the second occassion aswell but for some reason it did not work.

I'd be greatful for your advice. Thanks

 

[chessbot]

How about using javascript to create expandable tables, which are by default closed? Then the page has 20 (or however many) ids on it and looks short when first loaded.

I will probably be shouted down for mentioning javascript on this forum, though. (Not universally supported! Buggy! Dependent! Changing! Can be disabled! Enough? )

--Chessbot

"See the TURTLE of enormous girth!"
-- Stephen King, The Dark Tower series

 

[jgd12345]

Ooh noo. It has it good points but generally aint too keen on it.

 

[danomac]

I think in this case it'd be best to use PHP to keep track of how many subrows are outputted, and limit them to say, 3 maximum. If there are more than that, display a "Show All" link or something similar that pops up another page with the full details.

So limit to 10 band_id's and 3 subrows. that would be 30 rows at max and 10 minimum.