Jim R Posted April 15, 2020 Author Share Posted April 15, 2020 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 "; Quote Link to comment Share on other sites More sharing options...
kicken Posted April 15, 2020 Share Posted April 15, 2020 41 minutes ago, Jim R said: Unknown column 'mostRecent.grade' in 'field list' Because you aliased it from grade to pgrade. Quote Link to comment Share on other sites More sharing options...
Jim R Posted April 15, 2020 Author Share Posted April 15, 2020 21 minutes ago, kicken said: Because you aliased it from grade to pgrade. You said to alias them if needed. Quote Link to comment Share on other sites More sharing options...
Jim R Posted April 15, 2020 Author Share Posted April 15, 2020 Removed the pgrade alias, now I'm getting Unknown column 'mostRecent.nameLast' in 'field list' Quote Link to comment Share on other sites More sharing options...
Jim R Posted April 15, 2020 Author Share Posted April 15, 2020 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Jim R Posted April 15, 2020 Author Share Posted April 15, 2020 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. Quote Link to comment Share on other sites More sharing options...
Jim R Posted April 15, 2020 Author Share Posted April 15, 2020 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 "; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.