Complicated (for me) Order By issue...

[Jim R]

I didn't see p.grade wasn't in there, but I was looking for mostRecent.grade.  

I put it in, bottom of the sub select.  Same error shows up.  

 

Unknown column 'mostRecent.grade' in 'field list'

$query = "SELECT *,mostRecent.grade,
		mostRecent.nameLast
		
		FROM (SELECT p.id as pid,
		b.playerID as bplayerID,
		b.userID as buserID,
		s.toggle AS stoggle,
		o.toggle AS otoggle,
		p.city as pcity,
		p.school as pschool,
		s.city as scity,
		s.school as sschool,
		r.id as rid,
		r.opp_city as roppcity,
		r.opp_school as roppschool,
		o.city as ocity,
		o.school as oschool,
		r.city as rcity,
		r.school as rschool,
		r.nameFirst as rfirst,
		r.nameLast as rlast,
		p.nameFirst as pfirst,
		p.nameLast as plast,
		p.grade as pgrade
		
		
		FROM a_players_reviews r 
		
		LEFT JOIN a_players p 
			ON CONCAT (r.nameFirst,r.nameLast) = CONCAT (p.nameFirst,p.nameLast)
	
		LEFT JOIN a_schools s 
			ON CONCAT(r.city,r.school) = CONCAT(s.city,s.school)
		LEFT JOIN a_schools o	
		 	ON CONCAT(r.opp_city,r.opp_school) = CONCAT(o.city,o.school)
		
		LEFT JOIN a_player_bookmark b
			ON p.id = b.playerID && '". $userID ."' = b.userID
		
		WHERE bookmark>0
		ORDER BY r.id desc
		LIMIT 15) mostRecent
 			ORDER BY mostRecent.grade,mostRecent.nameLast	
		";

 

[kicken]

41 minutes ago, Jim R said:

Unknown column 'mostRecent.grade' in 'field list'

Because you aliased it from grade to pgrade.

 

[Jim R]

21 minutes ago, kicken said:

Because you aliased it from grade to pgrade.

 

You said to alias them if needed.  

[Jim R]

Removed the pgrade alias, now I'm getting 

 

Unknown column 'mostRecent.nameLast' in 'field list'

[Jim R]

Removed the alias from p.nameLast, and now the errors are gone.  Still not fully there, as some of the content isn't being printed.  It's not longer recognizing the player's name, his school and the opponent.  I've added columns to the sub select, and I've used the aliases.  

The name alias rfirst and rlast aren't showing.  

Edited April 15, 2020 by Jim R

[Jim R]

It's not printing any of my name variables or the school and city of the player.  It's printing the opponent now.  

 

So it's not printing the ones where I have duplicate entries.  

[Jim R]

Well...I removed all the duplicate columns and replaced them IDs, and while it took some trial and error to reconfigure the joins and output, I was able to get it to work.  

Below is the final query.  I'm glad it was at least something I hadn't done before and just couldn't get it figured out.  

 

Thank you for your time.  

	$query = "SELECT *,mr.nameFirst,mr.grade,
		mr.nameLast
		
		FROM (SELECT p.id as pid,
		b.playerID as bplayerID,
		b.userID as buserID,
		s.toggle AS stoggle,
		o.toggle AS otoggle,
		p.city as pcity,
		p.school as pschool,
		s.city as scity,
		s.school as sschool,
		r.id as rid,
		o.city as ocity,
		o.school as oschool,
		oppID,
		nameFirst,
		nameLast,
		feet,
		inches,
		position,
		grade,
		time,
		event,
		review
		
		
		FROM a_players_reviews r 
		
		LEFT JOIN a_players p 
			ON p.id = r.playerID
	
		LEFT JOIN a_schools s 
			ON p.schoolID = s.id 
		LEFT JOIN a_schools o	
		 	ON r.oppID = o.id 
		
		LEFT JOIN a_player_bookmark b
			ON p.id = b.playerID && '". $userID ."' = b.userID
		
		WHERE bookmark>0
		ORDER BY r.id desc
		LIMIT 15) mr
 			ORDER BY mr.grade,mr.nameLast
		";