Have an order by issue...

[Jim R]

First of all, I have a lot of code and WordPress shortcodes determining which User can see which content.   My issue is the query and how it's ordered.

 

There are three primary groups:

1)  (Top 10) Ordered numerically based on the ranking I give them, typically 1-10.

2)  (Best of the Rest) Should be ordered alphabetically based on nameLast.

3)  (Names to Know) Is divided into Regions of the state (I have headers for each region), then listed alphabetically.  

 

 

The issue is Group 2.  It's being ordered the same as group 3, just without the headers.  I tried

flipping 'region' and 'nameLast', but that really screwed up group 3.  

 

I attached a screenshot of what it looks like.  I was surprised I could get the 3rd group the way I wanted it, but now I can't wrap my head around the logic of how to alphabetize the 2nd group.  

$query = 'SELECT * FROM a_playerRank WHERE year="2014" and position="2" and grouping<4  ORDER BY grouping DESC,rankPos,region,nameLast';

$results = mysql_query($query) or trigger_error('MySQL error: ' . mysql_error());

$currentGrouping = false; 
$currentRegion = false;

while($line = mysql_fetch_assoc($results)) {

	    if($currentGrouping != $line['grouping'])
        {
            //Status has changed, display status header
			
            $currentGrouping = $line['grouping'];
				
			if($line['grouping']==2)  {
				
					echo '<thead>
					<tr>
					<th class="num">#</th>
					<th class="top"></th>
					<th class="height">HT</th>';
					
					if (current_user_can("access_s2member_level4")){
					echo '<th class="level">Level</th>';
					}
					echo'
					<th class="school">City (School)</th>';
					
					if (current_user_can("access_s2member_level4")){
					echo '<th class="summer">Summer Team</th>';
					}
					echo'
					<th class="college">College</th>
					</tr>
					</thead>';
				}
							
			elseif($line['grouping']==1) 
			{
				
				echo '<tr><th colspan="7">Best of the Rest</th></tr>';
				
			}
			
			elseif($line['grouping']==0) echo '<tr><th colspan="7">Names to Know</th></tr>';
			
		}
		

// Determine what region the player plays		

if($currentGrouping=="0") {		
if($currentRegion != $line['region'])
					{
						$currentRegion = $line['region'];
					
					echo '<tr><th colspan="7" class="region">Region '. $line['region'] . ' :: ';		if($line['region']==1) echo 'The Region';
			if($line['region']==2) echo 'South Bend, Elkhart';
			if($line['region']==3) echo 'Fort Wayne, Marion';
			if($line['region']==4) echo 'Lafayette, Kokomo, Greencastle';
			if($line['region']==5) echo 'Indianapolis metro area';
			if($line['region']==6) echo 'East Central Indiana - Muncie, Richmond, as I-74 hits Ohio';
			if($line['region']==7) echo 'Bloomington, Terre Haute, Bloomington';
			if($line['region']== echo 'Columbus, Madison, Louisville area';	
							
					
					echo '</th></tr>';}			
}

echo '
<tbody>';

if (empty($line['pro']) && ($current_user->ID == 1587)) {
	
}
else
{


//if (isset($line['pro'])) {

echo '<tr>
<td>'. $line['rankPos'] . $line['devPos'] . '</td>
<td class="nowrap">';

if ($line['wpID'] > '0') {
echo '<a href="/tag/' . $line['wpSlug'] . '">'. $line['nameFirst'] . ' ' . $line['nameLast'] . '</a>';
}
else
{
echo  $line['nameFirst'] . ' ' . $line['nameLast'];
}
echo '</td><td>'. $line['feet'] . '\'' . $line['inches'] . '"</td>';

if (current_user_can("access_s2member_level4")){
echo '<td>'. $line['level'] . '</td>';
}

if ($line['city'] !='') {
	echo '<td class="nowrap">' . $line['city'] . ' (' . $line['school'] . ')</td>';
	}
else {
echo '<td class="nowrap">'. $line['school'] . ' HS</td>';
}

if (current_user_can("access_s2member_level4")){
echo '<td>'. $line['summer'] . '</td>';
}
//else
//{ echo '<td></td>';
//}

echo '<td>';
if ($line['commit'] == 'y') {
echo '
<strong>'. $line['college'] . '</strong>
';}

echo '</td></tr>';

if (current_user_can("access_s2member_level4")){
	if (!empty($line['pro'])) {
		
echo '<td></td><td colspan=6><span class="analysis">'. $line['pro'] . ' :: ' . $line['con'] .'</span></td>';
	}
}

}		
}
echo '</tbody></table></div>';

[Jim R]

Any help with this?  I've looked up "conditional order by", but I'm not sure it applies to what I'm trying to do. 

[Barand]

A dump file of your data would help us to help you

[Jim R]

Do you mean from the database itself? I tried to attach a CSV file.

Edited December 17, 2013 by Jim R

[Barand]

nm. I set up some test data.

 

You are only interested in the region in grouping 0

You are only interested in the rankPos in grouping 2

 

so discard those values in the other groups so you have a query like this

SELECT 
    grouping
    , CASE
            WHEN grouping <> 0 THEN 0
            ELSE region
      END as region
    , CASE 
            WHEN grouping <> 2 THEN 0
            ELSE rankPos
      END as rankPos
    , nameFirst
    , nameLast
    , feet
    , inches
    FROM a_playerRank 
    WHERE year="2014" and position="2" and grouping<4  
    ORDER BY grouping DESC,rankPos,region,nameLast

[Jim R]

Can I have a Select *?  

 

I added the other columns I needed.  It's printing 0 for the players with NULL in the "rankPos" column, even though in grouping 1 and 0 I don't have code for it to print rankPos.

Edited December 17, 2013 by Jim R

[Barand]

never

[Jim R]

It's printing 0 for the players with NULL in the "rankPos" column, even though in grouping 1 and 0 I don't have code for it to print rankPos.

[Barand]

Guess I'll need that dump file after all before I go any further.

[Jim R]

I tried to attach a CSV file earlier, but it wouldn't let me.

[Barand]

rename it as .txt instead of .csv

 

I'll also need your table structure from

SHOW CREATE TABLE a_playerRank