Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Support (Reduce Queries)

Status
Not open for further replies.
jgd12345

jgd12345

Technical User
Apr 22, 2003
124
GB
Hi, Basically I have a loop which does the following:

$query = mysql_query("SELECT * FROM bands");

while($band = mysql_fetch_array($query)) {
$tabs = tablinks($band['id']);
echo $tabs;
}

As it goes through the loop the tablinks function is done which contains 5 additional queries. Therefore if the innitial loop is huge then there will be lots of queries. I was wondering if there's an easier way of doing this to reduce the number. I'd be greatful for your help. Thanks

Here is the tablinks function:

function tablinks($bid) {
$output = '';
$end = '';

$nbquery = mysql_query("SELECT COUNT(*) FROM tabs WHERE bid='$bid' && filetype!='Lyrics'");
$nbrows = mysql_result($nbquery, 0);

if($nbrows != 0) {
$output .= ' - [Tabs: ';
$end = "yes";
}

$nbquery = mysql_query("SELECT COUNT(*) FROM tabs WHERE bid='$bid' && filetype='Bass Tab'");
$nbrows = mysql_result($nbquery, 0);

if($nbrows != 0) {
$previous = "yes";
$output .= '<a href="tabs_songs.php?bid='.$bid.'&filetype=bass">Bass</a>';
}

$nbquery = mysql_query("SELECT COUNT(*) FROM tabs WHERE bid='$bid' && filetype='Drum Tab'");
$nbrows = mysql_result($nbquery, 0);

if($nbrows != 0) {
if($previous == "yes") {
$output .= ' | ';
}

$previous = "yes";
$output .= '<a href="tabs_songs.php?bid='.$bid.'&filetype=drum">Drum</a>';
}

$nbquery = mysql_query("SELECT COUNT(*) FROM tabs WHERE bid='$bid' && filetype='Guitar Tab'");
$nbrows = mysql_result($nbquery, 0);

if($nbrows != 0) {
if($previous == "yes") {
$output .= ' | ';
}

$output .= '<a href="tabs_songs.php?bid='.$bid.'&filetype=guitar">Guitar</a>';
}

if($end == "yes") {
$output .= ']';
}

$nbquery = mysql_query("SELECT COUNT(*) FROM tabs WHERE bid='$bid' && filetype='Lyrics'");
$nbrows = mysql_result($nbquery, 0);

if($nbrows != 0) {
$output .= ' - [<a href="tabs_songs.php?bid='.$bid.'&filetype=lyrics">Lyrics</a>]';
}

return $output;
}
 
Sort by date Sort by votes
you can return all counts for all ids in one query
Code: 
select bid
     , filetype
     , count(*) as thecount
  from tabs 
group
    by bid
     , filetype

rudy
SQL Consulting
 
Upvote 0 Downvote
Hi thanks, how would I use that to display the count when the filetype='Lyrics' and filetype='Guitar Tab' for that particular query.
 
Upvote 0 Downvote
did you try the query? it gives you the counts for all filetypes

if you want only two of them, you can run this --
Code: 
select bid
     , filetype
     , count(*) as thecount
  from tabs 
 where filetype 
    in ('Lyrics','Guitar Tab')
group
    by bid
     , filetype

rudy
SQL Consulting
 
Upvote 0 Downvote
Hi for example I did the following:

$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 />";
}

to select 20 rows and got:

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.
 
Upvote 0 Downvote
relative count? do you mean percentages?

band id: 1 | count: 2 | filetype: Bass Tab | 0.50
band id: 1 | count: 2 | filetype: Lyrics | 0.50
band id: 2 | count: 85 | filetype: Guitar Tab | 1.00
band id: 4 | count: 3 | filetype: Bass Tab | 0.10
band id: 4 | count: 1 | filetype: Guitar Tab | 0.03
band id: 4 | count: 25 | filetype: Lyrics | 0.86
band id: 5 | count: 4 | filetype: Bass Tab | 0.67
band id: 5 | count: 2 | filetype: Lyrics | 0.33

oh my, that's going to be extremely difficult unless you are on 4.1 which allows subqueries

rudy
SQL Consulting
 
Upvote 0 Downvote
Hi no sorry didn't explain that too great. I meant I need it to display the number of guitar tabs, drum tabs, bass tabs, lyrics for each band. I understand that query gets the information but I don't know how to display it for each band.
 
Upvote 0 Downvote
ah, okay, that's different

it's just a loop in your php

unfortunately i don't do php

perhaps someone else will chime in

rudy
SQL Consulting
 
Upvote 0 Downvote
Ah right cheers for your help getting me to this stage.
 
Upvote 0 Downvote
In the writing loop, add a couple of counters which simply create the running totals as needed....when the band_ID changes echo out the totals and reset the values to 0.

Bastien

Cat, the other other white meat
 
Upvote 0 Downvote
Hi cheers worked a treat. Now I've run into a problem with the initial sql query. When I use limit to display the first 20 bands it displays the first 20 rows.

The trouble is that there is an inconsistent number of rows for each bands (depending on the filetype of the records in the tabs table)

eg

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

Therefore I'm not sure how I can do the query to only output the first 20 rows (or from rows 40 to 60 etc). I know I could count the band id through the loop but that will mean the query would have to loop through every single band each time.

I'd be greatful for your help. Thanks
 
Upvote 0 Downvote
there's no easy way to do this

you could write an extremely complex query (or series of queries with temp tables) to return only 20 bands with their different filetypes, but then this would need to be rewritten if you eanted, say, the 21st through 40th

or you could retrieve 20 bands at a time and then run a second query using the 20 retrieved ids

or you could simply return everything and just skip over the ones you don't need

rudy
SQL Consulting
 
Upvote 0 Downvote
Hi the second query idea sounds good but if you do the second query using the 20 retrieved id's then surely the only way to do this is by putting it inside the loop which goes through the rows of the band query therefore adding an extra 20 querys.
 
Upvote 0 Downvote
no, it's not done in a loop
Code: 
select bid
     , filetype
     , count(*) as thecount
  from tabs
 where bid
    in (3,24,45,67,134,...937))
group
    by bid
     , filetype
order
    by bid
     , filetype
where the ids 3,24,45,67,134,...937 are the 20 ids that you want displayed, as retrieved from the band query which uses LIMIT 0,20 and LIMIT 20,20 and so on

rudy
SQL Consulting
 
Upvote 0 Downvote
Hi with a bit of my magic I come up with:

$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 gave 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

Which is correct. However it just all seems a little long. If you're wondering why I used 3 queries then it's because initially I called the first query $bandquery but when it was referred to on the second occurance it did not work so I had to initialise it again. I can't see another way to do it really.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EdLentz
  • Locked
  • Question
How do I use a keyword in a query??
Replies
6
Views
272
EdLentz
EdLentz
trandrus
  • Locked
  • Question
incrementing a column without having to query the db first
Replies
4
Views
118
Chris Miller
Chris Miller
martinkaeser
  • Locked
  • Question
MySQL Query for incrementing field from duplicates in database
Replies
4
Views
114
martinkaeser
martinkaeser
madcelt
  • Locked
  • Question
Select query not showing error but not showing result either
Replies
2
Views
90
madcelt
madcelt
C_C_K
  • Locked
  • Question
combining queries/subqueries
Replies
0
Views
59
C_C_K
C_C_K

Part and Inventory Search

Sponsor

Back
Top