"If / else" in a query?

[Jim R]

Really it would just be an IF.

 

Here is a link to my page:

http://hoosierhoopsreport.com/pu

 

I want the instance of...

 

Bryant McIntosh   6'4"   Greensburg

Committed to Indiana State

 

...in the Offered group.  This will allow me to reflect where a player decided to attend who was offered a scholarship by the college in the current view.  

 

The trigger is the column "commit".  

 

When pr.college != pc.colleges (which matches the WordPress page title / $page), it basically says that player did not commit to the college in the current view.  They committed somewhere else.  Somehow when Bryant McIntosh' instance comes through the query, pr.commit needs to not equal "y"

// Gets Page title
$page = get_the_title();
//echo $page;


$sql ="SELECT * FROM a_playerRank AS pr, playerCollege AS pc
WHERE pr.id = pc.id_player
AND pr.year = '2014'
AND pc.colleges = '". $page ."'
ORDER BY pr.commit desc,pc.offer desc,pr.nameLast
";

$results = mysql_query($sql);


$position = array (
'1' =>'PG',
'2' =>'SG',
'3' =>'SF',
'4' =>'PF',
'5' =>'C'
);

$currentCommit = false;
$currentOffer = false;


echo '<div class="recruitWrap">';


while ($line = mysql_fetch_assoc($results)) {
	
	
	if ($currentCommit != $line['commit']) {
		$currentCommit = $line['commit'];
		
		if ($line['commit'] == 'y') {
			
			echo '<div class="recruitHeader">Committed</div>';
			}
		else  {
			echo '<div class="recruitHeader">Offered</div>';	
			}	
	}
	if ($currentOffer !=$line['offer']) {
		$currentOffer = $line['offer'];
		
		if ($line['offer'] == 'y') {
			
			}
		else {
			echo '<div class="recruitHeader">Also recruiting</div>';
			}
		}

//if((ISSET ($line['college']) && $line['college'] == $line['colleges']) || !ISSET($line['college']))

echo '<div class="recruitRow">
<div class="recruit">' . $line['nameFirst'] . ' ' . $line['nameLast'] . '</div>';

echo '<div class="recruit recruitHeight">' . $line['feet'] . '\'' . $line['inches'] . '"</div>';
echo '<div class="recruit recruitPosition">' .  $position[$line['position']] . '</div>';


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


echo '<div class="clear"></div>';

echo '</div>';

if($line['college'] != $page) {
	echo '<div>Committed to '. $line['college'] .'</div>';
	}

}
echo '<div class="clear"></div>';
echo '</div>'; // recruit wrap

[trq]

Indeed.

[Jim R]

Indeed what?

[trq]

Your post makes little sense is all I'm saying.

[Jim R]

Is there a way to take my results assigning value to a variable so I can change where it shows up on my list without altering the database?

 

I can make it not echo the instance I noted above, but I can't change where it shows up.

 

The page title controls one of the query variables, in this case Purdue University.  Any player in the database recruited by Purdue will eventually show up on that list.  However, most of them will commit to play for another school.  

 

I want to be able to reflect that a player has been recruited by one school yet committed to another.  

[Jim R]

Could I utilize Case in the Order By part of my query?  

 

Order By "commit" where "college" (the college committed to) != "colleges" (the college recruiting him)

[Psycho]

You can do an IF() within your select. I as well have a hard time following what you are doing. But, here is an example of how you can use it

SELECT field1, field2, IF(field3='$someVar', 'trueValue', 'falseValue') as variableName
FROM table

You can also ORDER BY the results by ONLY using the condition

 

 

SELECT field1, field2
FROM table
ORDER BY (field3='$someVar')

Edited August 22, 2013 by Psycho

[Jim R]

I can understand why it would not have enough information

 

 

More detail about my tables and columns. 

 

a_playerRank is my main table, noted as pr

playerCollege is a table used to link a player's ID to each school that may be involved with him, noted as pc

 

pr.commit tells us whether or not the player is committed, while pr.college tells which college it is if pr.commit = 'y'.

 

pc.colleges is a school recruiting a player (each kid's id might have 2-12 rows, each with a different school).  If a school offers a scholarship, pc.offer = 'y'.

 

 

It's the ORDER of the results I'm looking to alter. 

 

 

A list of players recruited by the same school.  In this case $page = Purdue

I'm trying to have three groups within that list

• Players committed to that school (pr.commit = 'y' pc.offer = 'y'  pr.college = $page   pc.colleges = $page)
• Players offered a scholarship by that school (pc.offer = 'y')
• Players the schools are still evaluating (pc.offer = 'y')

 

That part is easy.  Where it gets complicated is if a player commits to another school, I would like to note that.  So among players recruited by $page (Purdue), my ORDER BY puts players commit = 'y' first, even though they may be committed to another school.  The second two groups will have a mix of players who are or aren't committed to any school, but definitely not committed to $page (Purdue in this instance).

 

(I'll be applying this code on about 20-30 school pages down the road.)

 

 

Right now, among the list on the page I linked above, two players out of ten are committed.  One is committed to $page (Purdue), the other is not.  I want the one who isn't committed to $page to show up in one of the other two groups (with the note that he is committed to another school).

 

 

Can I use a WHILE?  WHILE pr.college = $page { }  There needs to be a while...else.  

[Jim R]

If I use

$line = mysql_fetch_assoc($results)

 

instead of...

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

 

 

Can I use multiple WHILEs to parse my data?

[Jim R]

Disregard the previous post.  :-)

 

What about a Case statement in the Order By?

[Psycho]

Use a CONDITION in your ORDER BY clause. This may work for you - I'm not going to build a database to test it, but it might get you pointed in the right direction

 

$sql ="SELECT *, (pr.commit AND (pr.college = pc.colleges)) as commitedToThisCollege
       FROM a_playerRank AS pr
       JOIN playerCollege AS pc ON pr.id = pc.id_player
       WHERE pr.year = '2014'
         AND pc.colleges = '". $page ."'
       ORDER BY (pr.commit AND (pr.college = pc.colleges)) DESC, pc.offer desc, pr.nameLast";

[Jim R]

I looked for those types of conditions in Order By and came up with Case statements.  Before I dig too deep, if what I'm ultimately interested in the top group is pr.college = pc.colleges.  At that point pr.commit would be implied, so I wouldn't have to sort by pr.commit.  

 

Do you think just using (pr.college = pc.colleges) as the condition would work?  

 

 

Right after the SELECT * statement, what does the condition do for me there?  I understand what it does in the Order By statement.

 

 

Thanks for your time.  

[Jim R]

I tried your query, but it didn't change the order.  (I took out the headers and just had it print the list.)  

 

Mathias should be on top.  He is

pr.commit = 'y' pr.college = 'Purdue' /  pc.colleges = 'Purdue' pc.offer = 'y'

 

McIntosh should be at the bottom

pr.commit = 'y' pr.college ='Indiana State' / pc.colleges = 'Purdue' pc.offer = 'y'

 

The other guys on this list are

pr.commit = ''  pr.college = '' / pc.colleges = 'Purdue' pc.offer = 'y'

 

Dakota Mathias

6’4″

SG

Elida HS

 

Bryant McIntosh

6’4″

PG

Greensburg HS

 

Committed to Indiana State

Alec Brennan

6’9″

PF

Milton (Academy)

 

Michael Chandler

6’10″

C

Niceville (NW Florida State (JC))

 

Jabari Craig

6’10″

C

Waynesboro (Fishburne Military)

 

Vince Edwards

6’6″

SF

Franklin (Middletown)

 

Isaac Haas

7’2″

C

Piedmont (Hokes Bluff)

 

Ronnie Harrell

6’6″

SF

Denver (East)

 

JP Macura

6’5″

SG

Lakeville (North)

 

 

 

[Psycho]

Well, this is part of the problem

 

 

pr.commit = 'y'

 

You should be using the logical Boolean values of 1 and 0, not some string value that you have to always test to see if it is true of not. the sample query I provided was predicated on the assumption that you would be using an appropriate value. Stop thinking you need a case statement - you do not. I already stated you can do this with a simple condition in the ORDER BY. When using a condition, the value of the condition will be a 1 or 0 (for True/False). Either fix the value you are storing for the commit field (which you should do) or change the ORDER BY clause to something like this:

 

 

ORDER BY (pr.commit='y' AND (pr.college = pc.colleges)) DESC, pc.offer desc, pr.nameLast";

 

This will order the records such that:

1. Players who have a commit='Y' AND .college = pc.colleges will come first..

2. Records will be further sorted based upon pc.offer in descending order (I'm assuming you are also storing the value here incorrectly as Y/N instead of 1/0)

3. Finally, records are sorted by last name

[Jim R]

I can easily change the 'y' to '1'.  Would you recommend I make it so NULL isn't possible?  So it's 1 or 0?

[Psycho]

I can easily change the 'y' to '1'.  Would you recommend I make it so NULL isn't possible?  So it's 1 or 0?

 

 I can't say. NULL should be used when there are situations where you don't/can't know what the value should be. If you will always know if the player is committed or not, then you shouldn't use NULL. But, in that case, I would think the code would prevent a NULL from ever being set anyway.

[Jim R]

What you suggested for the query worked.  It took some trial and error to get it print out the way I wanted it.  The troublesome part continued to be that 'pr.commit' applied to players who weren't committed to the school featured in the current Page.  

 

http://hoosierhoopsreport.com/pu

 

Here is the final code:

// Gets Page title
$page = get_the_title();
//echo $page;

if(!$con = mysql_connect("localhost","jwrbloom_","redcoach")) {
   die("Could not connect to database: ".mysql_error());
}

mysql_select_db("jwrbloom_hhrplayers", $con);

$sql ="SELECT *
       FROM a_playerRank AS pr
       JOIN playerCollege AS pc ON pr.id = pc.id_player
       WHERE pr.year = '2014'
         AND pc.colleges = '". $page ."'
ORDER BY (pr.commit='1' AND (pr.college = pc.colleges)) DESC, pc.offer desc, pr.nameLast";

$results = mysql_query($sql);


$position = array (
'1' =>'PG',
'2' =>'SG',
'3' =>'SF',
'4' =>'PF',
'5' =>'C'
);

$currentCollege = false;
$currentOffer = false;


echo '<div class="recruitWrap">';


while ($line = mysql_fetch_assoc($results)) {
	
 	$collegeCommit = $line['college'];
	
	if($line['college']	!= $page) {
		$line['college'] = '';
		}	
		
	if($line['college'] == $page && $line['commit'] == '1') {
		
		
			if ($currentCollege !=$line['commit']) {
			$currentCollege = $line['commit'];
		
			echo '<div class="recruitHeader">Committed</div>';
				}
	}
		
		elseif	
			($currentOffer !=$line['offer']) {
			$currentOffer = $line['offer'];
			
				if($line['offer'] == '1') {
					echo '<div class="recruitHeader">Offered</div>';	
				}
					
				elseif ($line['offer'] != '1')  {
					echo '<div class="recruitHeader">Also recruiting</div>';
				}
			}
		

//if((ISSET ($line['college']) && $line['college'] == $line['colleges']) || !ISSET($line['college']))

echo '<div class="recruitRow">
<div class="recruit">' . $line['nameFirst'] . ' ' . $line['nameLast'] . '</div>';

echo '<div class="recruit recruitHeight">' . $line['feet'] . '\'' . $line['inches'] . '"</div>';
echo '<div class="recruit recruitPosition">' .  $position[$line['position']] . '</div>';


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


echo '<div class="clear"></div>';

echo '</div>';

if($line['commit'] == '1' && $line['college'] != $page) {
	echo '<div class="otherCollege">Committed to '. $collegeCommit .'</div>';
	}

}
echo '<div class="clear"></div>';
echo '</div>'; // recruit wrap
?>

Thanks for your time on this issue.  I always get great help here, as it seems anything I try comes with a learning curve.  At least the issues get more complicated, and I'm pretty good at porting anything I've done in the past over to new projects.  

Edited August 23, 2013 by Jim R