Need guidance on multiple column Group_Concat...

[Jim R]

...or if there is a better way to get the data.

 

Creating game capsules for basketball games.  They are essentially previews of upcoming games.  They will include the team name and 'key' players. 

I'm just now getting to the output, and I feel like my query is getting me the information I need from it.  School names, player names, player height (feet, inches), position he plays and what grade he is in.  I could just CONCAT that all out, but I really want to convert the numerical values for position and grade into words rather than numbers.

Position:  1 = point guard, 2 = shooting guard, etc

Grade: 22 = senior, 23 = junior, etc

Normally, I know how to do that when I'm pulling non-array data, even when it's an array.  I'm not sure, however, if I'm creating an array.

 

$query="SELECT 
h.school as home, 
a.school as away,
preview,

group_concat(
distinct CONCAT(hp.nameFirst,' ',hp.nameLast),hp.feet,hp.inches,hp.position,hp.grade
SEPARATOR ';'

) as hplayers,

group_concat(
distinct CONCAT(ap.nameFirst,' ',ap.nameLast),ap.feet,ap.inches,ap.position,ap.grade
SEPARATOR ';'

) as aplayers

FROM a_schedule sc
	
LEFT JOIN a_schools h
	on sc.home_id = h.id
LEFT JOIN a_schools a
	on sc.away_id = a.id
	
LEFT JOIN a_players hp
	on h.id = hp.schoolID and hp.grade >'21' and hp.key='1'
	
LEFT JOIN a_players ap
	on a.id = ap.schoolID and ap.grade >'21' and ap.key='1'
	
Group by sc.id
order by sc.id
";

$results = mysqli_query($con,$query);
	while($line = mysqli_fetch_assoc($results)) {
		echo $line['hplayers'];
	}	

This is what the output looks like in my query:

 

Ultimately, it will read on screen:  

• Dayveon Turner, 6'0" point guard; SR
• Reggie Bass, 6'4" shooting guard; SR
• Antonio Lisenbee, 6'6" power forward; SR 

 

Do I fetch array?  

 

Table structures:

a_schools 

a_schedule

a_players (id column too)

[Barand]

Do you have tables for position and grade?

+-----------+-------------------+            +-----------+-------------------+
| position  | description       |            | grade     | description       |
+-----------+-------------------+            +-----------+-------------------+
|     1     | Point guard       |            |    22     |  Senior           |
|     2     | shooting guard    |            |    23     |  Junior           |
| etc                           |            | etc                           |
+-----------+-------------------+            +-----------+-------------------+

 

[Jim R]

I could for position, as they could never change, but normally I use this:

 $position = array (
            '1' =>'Point Guard',
            '2' =>'Shooting Guard',
            '3' =>'Small Forward',
            '4' =>'Power Forward',
            '5' =>'Center'
        );

 

The grades change each year relative to class, and I have a Seasons table that I call to in each function I need it, taking the date of the post, then I use CASE to determine the grade.   

 

The problem I see is separating the values in the output.  Adding separators would allow me it explode it.  Could I then use it that way?  The only other times I've exploded a list is when it just came from one column.

[Barand]

3 hours ago, Jim R said:

on h.id = hp.schoolID and hp.grade >'21' and hp.key='1'

I'm guessing that queries with expressions like that above, which contain grade references, will have to be editted every year. If that's true, you really should be looking for another way.

[Barand]

Is there any relationship between players age and grade (Senior/Junior)?

[Jim R]

2 hours ago, Barand said:

I'm guessing that queries with expressions like that above, which contain grade references, will have to be editted every year. If that's true, you really should be looking for another way.

Yes, ultimately grade > '21' will be grade > $season.  I'm trying to get the desired results for right this moment.  

I'm just struggling with turning the data in the field...

Quote

 

Dayveon Turner60122;Reggie Bass64222;Antonio Lisenbee66422;Matt Spears66422

 

...into something more palatable onscreen.  

Would coding commas into the Concat help?

[Jim R]

With commas coded in:

Logan Imes,6,4,1,23;Nick Richart,6,8,5,23;Isaiah Davis,6,8,5,22;Edward Thomas,6,4,2,23

 

I could concatenate the whole thing, but it would still be, Logan Imes, 6'4", 1, 23.  How then to get the 1 = point guard and the 23 = JR.

Edited November 17, 2021 by Jim R

[Barand]

With the two tables I suggested included in your query with joins it would be job done.

[Jim R]

3 hours ago, Barand said:

With the two tables I suggested included in your query with joins it would be job done.

Ultimately, it seems, the results would still end up in hplayers or aplayers.  How would I otherwise get those two columns linked to each player?  

Edited November 18, 2021 by Jim R

[Jim R]

Added this:

$results = mysqli_query($con,$query);
	while($line = mysqli_fetch_assoc($results)) {
	
	$lines = explode(PHP_EOL,$line['hplayers']);
	$array = array();
	foreach ($lines as $player) {
		$array[] = str_getcsv($player);
		

	}
 	print_r($array);

Producing this:

Quote

Array ( [0] => Array ( [0] => Dayveon Turner [1] => 6 [2] => 0 [3] => 1 [4] => 22;Reggie Bass [5] => 6 [6] => 4 [7] => 2 [8] => 22;Antonio Lisenbee [9] => 6 [10] => 6 [11] => 4 [12] => 22;Matt Spears [13] => 6 [14] => 6 [15] => 4 [16] => 22 ) ) Array ( [0] => Array ( [0] => Logan Imes [1] => 6 [2] => 4 [3] => 1 [4] => 23;Nick Richart [5] => 6 [6] => 8 [7] => 5 [8] => 23;Isaiah Davis [9] => 6 [10] => 8 [11] => 5 [12] => 22;Edward Thomas [13] => 6 [14] => 4 [15] => 2 [16] => 23 ) )

I tried echoing $array[0], but it just produced 'array'.  

Edited November 18, 2021 by Jim R

[Barand]

If it's an array, you need print_r(), not echo

Edited November 18, 2021 by Barand

[Jim R]

The root of my issue, how to break up the hplayers and aplayers strings so I can print the player lists how I'd like them?

 

I guess the side question goes toward what you said earlier, how creating separate tables for position and grade change what's in the hplayers and aplayers fields?

Edited November 18, 2021 by Jim R

[Barand]

Perhaps something like this (my identifiers do not exactly match yours)

SELECT h.schoolname as home
     , a.schoolname as away  
     , sch.preview
     , GROUP_CONCAT(DISTINCT
            h.name, ', ', h.ht, ' ', h.pos, '; ', h.grade
            SEPARATOR ' / '
		) as home_team
     , GROUP_CONCAT(DISTINCT
            a.name, ', ', a.ht, ' ', a.pos, '; ', a.grade
            SEPARATOR ' / '
		) as away_team
FROM
	schedule sch
    JOIN (
		SELECT s.schoolname
			 , concat(p.namefirst, ' ', p.namelast) as name
			 , concat(p.feet, '\'', p.inches, '"') as ht
			 , pos.description as pos
			 , g.description as grade
             , s.school_id
		FROM school s 
			 JOIN player p ON s.school_id = p.schoolid
			 JOIN grade g USING (grade)
			 JOIN position pos USING (position)
	    ) h ON sch.home_id = h.school_id
	JOIN (
		SELECT s.schoolname
			 , concat(p.namefirst, ' ', p.namelast) as name
			 , concat(p.feet, '\'', p.inches, '"') as ht
			 , pos.description as pos
			 , g.description as grade
             , s.school_id
		FROM school s 
			 JOIN player p ON s.school_id = p.schoolid
			 JOIN grade g USING (grade)
			 JOIN position pos USING (position)
	    ) a ON sch.away_id = a.school_id

PS, output

home: School 10
away: School 11
preview: Game on!
home_team: Andrew Baker, 6'2" Point Guard; JR / Bernard Cook, 6'5" Shooting Guard; JR / Charles Draper, 6'1" Small Forward; JR / David Potter, 6'4" Power Forward; JR / Eric Fletcher, 6'0" Centre; JR
away_team: Frank Gardener, 5'11" Point Guard; JR / Graham Hatter, 6'6" Shooting Guard; JR / Harry Joiner, 6'7" Small Forward; JR / Ian Miller, 6'4" Power Forward; JR / Julie Archer, 5'6" Centre; JR

 

Edited November 18, 2021 by Barand

[Jim R]

Thanks...I'll play around with it.  

I still can't wrap my head around subqueries -- when to use them, where to use them, how to use them.  Disappointed I was that far off.  

[Jim R]

4 hours ago, Barand said:

Perhaps something like this (my identifiers do not exactly match yours)

SELECT h.schoolname as home
     , a.schoolname as away  
     , sch.preview
     , GROUP_CONCAT(DISTINCT
            h.name, ', ', h.ht, ' ', h.pos, '; ', h.grade
            SEPARATOR ' / '
		) as home_team
     , GROUP_CONCAT(DISTINCT
            a.name, ', ', a.ht, ' ', a.pos, '; ', a.grade
            SEPARATOR ' / '
		) as away_team
FROM
	schedule sch
    JOIN (
		SELECT s.schoolname
			 , concat(p.namefirst, ' ', p.namelast) as name
			 , concat(p.feet, '\'', p.inches, '"') as ht
			 , pos.description as pos
			 , g.description as grade
             , s.school_id
		FROM school s 
			 JOIN player p ON s.school_id = p.schoolid
			 JOIN grade g USING (grade)
			 JOIN position pos USING (position)
	    ) h ON sch.home_id = h.school_id
	JOIN (
		SELECT s.schoolname
			 , concat(p.namefirst, ' ', p.namelast) as name
			 , concat(p.feet, '\'', p.inches, '"') as ht
			 , pos.description as pos
			 , g.description as grade
             , s.school_id
		FROM school s 
			 JOIN player p ON s.school_id = p.schoolid
			 JOIN grade g USING (grade)
			 JOIN position pos USING (position)
	    ) a ON sch.away_id = a.school_id

PS, output

home: School 10
away: School 11
preview: Game on!
home_team: Andrew Baker, 6'2" Point Guard; JR / Bernard Cook, 6'5" Shooting Guard; JR / Charles Draper, 6'1" Small Forward; JR / David Potter, 6'4" Power Forward; JR / Eric Fletcher, 6'0" Centre; JR
away_team: Frank Gardener, 5'11" Point Guard; JR / Graham Hatter, 6'6" Shooting Guard; JR / Harry Joiner, 6'7" Small Forward; JR / Ian Miller, 6'4" Power Forward; JR / Julie Archer, 5'6" Centre; JR

 

After I got most of the descriptors to lineup, it only pulled one row.  It should've pulled two for now.  Ultimately, it will pull in however many set up on each date we post.

 

Also, the problem on the grade is their grade has to remain relative to the date of the game.  Ultimately, I'll be adding variables to the query, referencing the date of the games.  I'd think I can remedy this with a CASE statement.  

Edited November 18, 2021 by Jim R

[Barand]

You will need to add

GROUP BY sch.id