Jump to content


Photo

Have an order by issue...


  • Please log in to reply
10 replies to this topic

#1 Jim R

Jim R

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 02 July 2013 - 12:30 PM

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']==8) 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>';

Attached Files



#2 Jim R

Jim R

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 17 December 2013 - 01:41 PM

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. 



#3 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,637 posts
  • LocationCheshire, UK

Posted 17 December 2013 - 03:30 PM

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


moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#4 Jim R

Jim R

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 17 December 2013 - 03:57 PM

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


Edited by Jim R, 17 December 2013 - 04:01 PM.


#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,637 posts
  • LocationCheshire, UK

Posted 17 December 2013 - 05:07 PM

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

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#6 Jim R

Jim R

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 17 December 2013 - 05:20 PM

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 by Jim R, 17 December 2013 - 05:24 PM.


#7 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,637 posts
  • LocationCheshire, UK

Posted 17 December 2013 - 05:23 PM

never


moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#8 Jim R

Jim R

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 17 December 2013 - 05:55 PM

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.



#9 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,637 posts
  • LocationCheshire, UK

Posted 17 December 2013 - 06:43 PM

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


moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#10 Jim R

Jim R

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 17 December 2013 - 10:09 PM

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



#11 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,637 posts
  • LocationCheshire, UK

Posted 18 December 2013 - 03:59 AM

rename it as .txt instead of .csv

 

I'll also need your table structure from

SHOW CREATE TABLE a_playerRank

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com