Having an unexpected issue with CASE in my query...

[Jim R]

The below query has worked up to now fully as intended until yesterday when I added one additional CASE.  It's the final one, dealing with 'recommit'.  I've stripped out all the other output.  

This code ultimately looks what scholarship offers are being made from colleges, if the player made a final list, then finally a commitment.  However, we've had a couple of players break free from their commitment, and we want to note that.  Code wise, I'm looking to have the 'decommit' trump the 'commit'.

 

However, that's not the issue I'm having up to this point.  Right now, it's not recognizing the Decommit in the database.  

So my output at the bottom:

echo $line['decommit'];

...doesn't show anything.

 

Here is the query and output.

$query = "SELECT *,p.id as pid, concat(nameFirst,' ',nameLast) as nameFull,
			GROUP_CONCAT(
				CASE
					WHEN recruit_type = 'Offer' THEN c.college	
				END) as offers,
			GROUP_CONCAT(
				CASE
					WHEN recruit_type = 'List' THEN c.college		
				END) as list,
				
			
			GROUP_CONCAT(
				CASE
					WHEN recruit_type = 'Offer' THEN date_format(po.time, '%m.%d.%y')
				END) as time,		
			GROUP_CONCAT(
				CASE
					WHEN recruit_type = 'List' THEN date_format(po.time, '%m.%d.%y')
				END) as timeList,		
			
				
			CASE
				WHEN other !='' THEN concat(c.college,' (',other,')')
				WHEN recruit_type = 'Commit' THEN c.college
			END	as commit,	
			
			CASE
				WHEN recruit_type = 'Decommit' THEN c.college
			END as decommit		
				
				
			FROM a_players p
			
			LEFT JOIN a_schools s 
				ON p.schoolID = s.id	
			
			LEFT JOIN a_players_offers po
				ON p.id = po.playerID				
			
			LEFT JOIN a_colleges c
				ON po.collegeID = c.id	
		
			
			WHERE CONCAT(lower(nameFirst),'-',lower(nameLast)) = '".$tag."' || CONCAT(nameFirst,' ',nameLast) = '". $search ."'
			ORDER BY list,offers
			";



		
		// End tag vs search term, finish the query


		$results = mysqli_query($con,$query);
		echo mysqli_error($con);
		while($line = mysqli_fetch_assoc($results)) {	

			echo $line['decommit'];

}

 

This is what the last few rows in the database look like:

 

I've checked spelling.  I've made sure there isn't whitespace.  

 

I didn't think adding the extra case with an alias would be the problem I'd have, but I'm having it. 

[Jim R]

I left column 'other' out of the database picture.  It's for those who commit to a college in a sport other than basketball or commit as a walk-on (no scholarship).

[Barand]

You are using an aggregation function (GRUP_CONCAT) in a query without a GROUP BY clause. The query, therefore, will only return a single row. The values in that row that are not aggregated will be arbitrary.

[Jim R]

19 minutes ago, Barand said:

You are using an aggregation function (GRUP_CONCAT) in a query without a GROUP BY clause. The query, therefore, will only return a single row. The values in that row that are not aggregated will be arbitrary.

How does it separate the 'commit' and work as expected but not the 'decommit'?

 

This is what the output looks like:

https://www.courtsideindiana.com/tag/kamari-lands/

The 'commit' triggers the top line of the profile, as well as the asterisks next to the college of choice.  He recently de-committed from Syracuse.

There are no 'list' instances for this player.  

 

Until I added the CASE for 'decommit', this code has been unchanged, working and live on my site since May of 2020. 

Edited August 24, 2021 by Jim R

[Jim R]

4 hours ago, Barand said:

You are using an aggregation function (GRUP_CONCAT) in a query without a GROUP BY clause. The query, therefore, will only return a single row. The values in that row that are not aggregated will be arbitrary.

So we're clear on the GROUP_CONCAT, I have the following code dealing with that row:

This one deals with the time components.  I have a similar one dealing with the colleges.  

 

CASE as commit is working and has been since May of 2020.  CASE as decommit, which I added last night, is not working.

		if (isset($line['offers'])) {
			echo '<div class="college-list">';
			
			// This is for a player who has a List
			if (isset($line['list'])) {
						
				echo '<table><th>Final List</th>';
				
				$listSchools = $line['list']; 
				$listDate = $line['timeList'];
				
				// Turn CSV into unordered list
				$listSchools = explode(",",$listSchools);
				$listDate = explode(",",$listDate);
								
				
				array_multisort($listSchools,$listDate);
				foreach (array_combine($listSchools, $listDate) as $list => $ldate) {
							

 

[Barand]

38 minutes ago, Jim R said:

CASE as commit is working and has been since May of 2020.

That is either by good luck or poor checking.

If a player has only a single "commit/decommit" record in players_offers, it will work fine. Once you get multiple records you will only see the results from one of them because of the grouping. If you get a result for "commit" you won't see the one for "decommit", and vice versa.

Quote

The values in that row that are not aggregated will be arbitrary.

 

Edited August 24, 2021 by Barand

[Jim R]

18 hours ago, Barand said:

That is either by good luck or poor checking.

If a player has only a single "commit/decommit" record in players_offers, it will work fine. Once you get multiple records you will only see the results from one of them because of the grouping. If you get a result for "commit" you won't see the one for "decommit", and vice versa.

 

When I removed the CASE/commit, the decommit still doesn't show up, and those two cases aren't group_concat.

[Barand]

Removing the commit column doesn't change the fact that you have three records for the one player in players_offers. As I said, you'll only get values from one of them as other columns are aggregated.

 

3 minutes ago, Jim R said:

and those two cases aren't group_concat.

... exactly. Try making them GROUP_CONCAT (or some other aggregation,eg MAX) like the others

[Jim R]

13 minutes ago, Barand said:

Removing the commit column doesn't change the fact that you have three records for the one player in players_offers. As I said, you'll only get values from one of them as other columns are aggregated.

 

... exactly. Try making them GROUP_CONCAT (or some other aggregation,eg MAX) like the others

Making those group_concat worked.  

 

I understood that I have multiple types in that column, but it was all working accept the decommit part.  Still don't understand why decommit part didn't work, even after I removed the commit CASE, but it works now.  

 

Thank you.

[Barand]

16 minutes ago, Jim R said:

Still don't understand why decommit part didn't work, even after I removed the commit CASE,

Your three player_offers results effectively contain

+----------------+-------------------+----------------+----------------+--------------+
|  player_id     |  offers           |  time          |  commit        |  decommit    |
+----------------+-------------------+----------------+----------------+--------------+
|    808         |                   |                |                |   College B  |
|    808         |                   |                |  College B     |              |
|    808         | College A         | 2021-04-15     |                |              |
+----------------+-------------------+----------------+----------------+--------------+

Because the grouping only gives you one row per player, only results from one of them will be chosen by the query to appear. You see the offer data because that is aggregated (GROUP_CONCAT) to one value for the player, but you will process either the commit or decommit (if they are not aggregated.). All you are doing by removing the commit CASE is choosing not to show the data from the row it's chosen to use.