...

View Full Version : Print once if chained event!?!?



jmansa
09-17-2009, 06:23 PM
I have this script which loops through all golf matches reported by a user, and it works fine, but... Some of the matches are chained together, and for those matches I only want to show 1 records instead of lets say 2 if 2 matches are chained together...

Table looks like this:
id, clubid, matchstart, reported, season, chainid, title

5 records from table:
1, 1, 2009-04-05 07:20:00, 1, 1, ,Single1
2, 1, 2009-04-09 07:20:00, 1, 1, ,Single2
3, 1, 2009-04-12 08:00:00, 1, 1,5645 ,Multi1
4, 1, 2009-04-12 13:00:00, 1, 1,5645,Multi2
5, 1, 2009-04-05 07:20:00, 1, 1, ,Single3

This I prints like this:

$sql="SELECT * FROM ".$prefix."_matches WHERE clubid='$clubid' AND reported=1 AND season='$season' GROUP BY chainid ORDER BY matchstart DESC";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
echo 'Match: '.$row['title'].' ('.$row['id'].')<br>';
}

This prints out 5 records like this:
Single1 (1)
Single2 (2)
Multi1 (3)
Multi2 (4)
Single3 (5)

What it should look like is this:
Single1 (1)
Single2 (2)
Multi1 (3)
Single3 (5)

Hope this makes sense...

Fumigator
09-17-2009, 07:23 PM
I don't see how that data, run through that script, would produce those 5 rows. I'd have to see it to believe it, because you are grouping by chainid, of which there are only two values (5645 and "") so you should only be getting two rows back.

jmansa
09-17-2009, 10:31 PM
I don't see how that data, run through that script, would produce those 5 rows. I'd have to see it to believe it, because you are grouping by chainid, of which there are only two values (5645 and "") so you should only be getting two rows back.

Sorry... Thats correct.. This is something I have tested but with no luck. As you say... It only produces 2 rows... The script I was referring to is this:

$sql="SELECT * FROM ".$prefix."_matches WHERE clubid='$clubid' AND reported=1 AND season='$season' ORDER BY matchstart DESC";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
echo 'Match: '.$row['title'].' ('.$row['id'].')<br>';
}

The problem is still the same...

Fumigator
09-18-2009, 06:15 PM
Alright... well your first query is very close to your answer. You want to group by that chainid, but only when there is a value. OK... what you need is a column value that is unique when chainid = '', and is the actual chainid value when chainid > ''. You can then group by that column. We can use the MySQL if() function for this.

For example:



select *, if(chainid = '', id, chainid) as chaingroup from t_codes
WHERE clubid='1' AND reported=1 AND season='1'
GROUP BY chaingroup
ORDER BY matchstart

Fumigator
09-18-2009, 06:26 PM
Forgot to mention-- be aware that when you do a wildcard select with a GROUP BY, you have no way of determining what data will be in the columns not included in your GROUP BY. This is because your results are a combination of all rows with the same chainid, so whichever row MySQL decides to use for the other values, it'll use.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum