Understanding GROUP BY

tito

I am trying to display the results of a league. I have 32 teams with 8 groups of 4 each.

The below query does what I want, except that it returns only one team per group whereas it should be four. How can I overcome this problem?

     $currentteams = $DB_site->query("
		SELECT *
        FROM " . TABLE_PREFIX . "cclpinternational_teams
        WHERE disabled=1 AND name!='divadmin' AND division='$thedivision' 
	GROUP BY 'groupname'

    ");

Installer

I'm not real sure what you're after but, instead of "GROUP BY", "ORDER BY groupname" might get it.

tito

No will not do it. Order by repeats the same info over and over
Example with Order BY
Where you see Group repeated over and over

Example with Group by

This is the one that I need, but it returns only one row per group.

Sxooter

Can your database do subselects? If so, build your query with those.

Start with something like:

select distinct groupname from table

Then, wrap a query around that to get the teams, etc...

tito

Have no clue on how to extract the distinct groupnames so I could perform a query WHERE groupname = "every extracted one"

Sxooter

Wait, could you just group by groupname, teamname?

tito

That does not work. I have been sweating for 2 days now over that.

Installer

What's the structure of your table?

If that first link results from "ORDER BY", I don't see what you mean by "repeats the same info over and over", unless you want to avoid a separate group heading for each team. Doing that would be a matter of outputting the right HTML. You want four teams from each group, right?

Sxooter

Can you post a simple schema and data example for us to play with?

tito

Thanks for looking at this.

By repeating over and over I meant
Group A Group A Group A Group A

Group B Group B Group B Group B

I don't want to do it with html, because this info changes all the times.

Field Type Attributes Null Default Extra Action
id int(4) UNSIGNED ZEROFILL No auto_increment

name varchar(50) No

contact_name varchar(70) No

contact_email varchar(70) No

hphone varchar(20) No

wphone varchar(20) No

address mediumtext No

division varchar(50) No

password varchar(50) No

info longtext No

photo varchar(100) No

photo_cap mediumtext No

disabled int(1) No 1

dstats int(1) No 1

groupname varchar(255)

 	$currentteams = $DB_site->query("
		SELECT * 
        FROM " . TABLE_PREFIX . "cclpinternational_teams
        WHERE disabled=1 AND name!='divadmin' AND division='$thedivision' 
	GROUP BY groupname
        ORDER BY  groupname 

    ");  

  // to get the info in 2 columns
  	$rcount=1;
    	$rpertr= 2;
// to get the info in 2 columns

	$rrows = $DB_site->num_rows($currentteams);

	while ($current = $DB_site->fetch_array($currentteams))
	{

$currentid = $current['id'];

$currentpic = $current['photo'];
if ($currentpic == "") 
{
$currentpic = "generic.jpg";
}
    $currentname = $current['name'];
	$currentgroup = $current['groupname']; 
$currentdiv = $current['division'];

Once I figure out how to display the x amount of teams (4 in that case) under each group instead of one, then I could do the LEFT JOIN to populate the other info.

Installer

Well, I still think you can do it with HTML. A query using "ORDER BY" will get the data you need, then it's just a matter of displaying it the way you want. Try this:

$query = "SELECT * " .
         "FROM test_01 " . 
         "WHERE disabled=1 AND name!='divadmin' AND division='$thedivision' " .
         "ORDER BY groupname";        

$result = my_mysql_query($query);
$group_name = '';
echo '<table><tr>';
while ($row = mysql_fetch_assoc($result)) {
    foreach ($row as $key => $value) {
        if (($key == 'groupname') && ($value != $group_name)) {
            if ($group_name) {
                echo '</tr>';
            }
            echo '<tr><td>' . 'Group ' . $value . '</td>';
            $group_name = $value;
        } elseif ($key == 'name') {
            echo '<td>' . $value . '<br />' .
                 '<img src="international/images/flags/' . $value . '.gif>"' .
                 '</td>';
        }
    }
}
echo '</tr></table>';

If you adapt that to your table, css, etc., it should give you what you want.

tito

I still have a battery of Left Join to do in order to fill in the blank. I am doing with vbulletin templates systems.