Jump to content

Have an order by issue...


Jim R

Recommended Posts

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>';

post-44159-0-23591700-1372785756_thumb.png

Link to comment
Share on other sites

  • 5 months later...

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.