Jump to content

Jim R

Members
  • Content Count

    804
  • Joined

  • Last visited

  • Days Won

    1

Jim R last won the day on July 26 2018

Jim R had the most liked content!

Community Reputation

1 Neutral

About Jim R

  • Rank
    Prolific Member

Profile Information

  • Gender
    Not Telling

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. Getting closer: 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 = 'Commit' THEN c.college END) as commit Didn't know I didn't have an 'else' in the Case statements.
  2. I printed echo $line['list']; It produced... 0,0,0,0,0,0,0,0,0,0,0,0,0,0,Butler,Marquette There are 16 total rows with this player, 14 offers and 2 listed. I'm not sure why it would be 14 zeros. It should just produce Butler and Marquette because only two rows have List as recruit_type.
  3. I'm pretty sure I had the wrong usage of GROUP_CONCAT. Here is the current query... SELECT *,p.id as pid, GROUP_CONCAT( CASE WHEN recruit_type = 'List' THEN c.college ELSE 0 END) as list, GROUP_CONCAT( CASE WHEN recruit_type = 'Offer' THEN c.college ELSE 0 END) as offers FROM a_players_offers po LEFT JOIN a_colleges c ON po.collegeID = c.id LEFT JOIN a_players p ON p.id = po.playerID LEFT JOIN a_schools s ON p.schoolID = s.id WHERE CONCAT(nameFirst,' ', nameLast) = '".$tag."' It's getting closer in terms of output...well...I think. The two which should be List no longer list the school as an offer. It's still not right, but it might be on the output side now.
  4. I have a Player Profile that, among many other things, lists which scholarship offers he has. This was the 'offers' column in a_players (a CSV list that I exploded and sorted), but I decided I wanted an Offers table. It presumably will provide more flexibility in terms of how data is input and output. Many of the results from a_players (one row) will have about 5-6 rows from a_players_offers. To keep from printing all the profile information for each row of offers the player had, I dug around and found group_concat, which then allowed me to explode the list and sort it, much like before. I couldn't lump them all in one CASE command because offers can actually an offer or part of a list (fewer schools). $query = "SELECT *,p.id as pid, CASE WHEN recruit_type = 'List' THEN GROUP_CONCAT(c.college) ELSE 0 END as list, CASE WHEN recruit_type = 'Offer' THEN GROUP_CONCAT(c.college) ELSE 0 END as offers 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(nameFirst,' ', nameLast) = '".$tag."' "; Here is the part of the profile that produces the offers or list... (I added $line['recruit_type'] so I could see which alias it was using.) // Turn CSV into unordered list $offersList = $line['offers']; $offersList = explode(",",$offersList); asort($offersList); foreach ($offersList as $offers) { echo '<tr><td>' . $offers.$line['recruit_type']; echo '</td></tr>'; } echo '</table></div>'; Off them for print up as 'college-name'Offer. (Keep in mind, as I have changed over to a separate Offers table, I haven't redone all the output yet.) There should be 14 that say Offer after them and two that say List. Marquette and the second instance of Butler should have List after their name. Actually, I would think the way output is set up, it wouldn't list either of the List schools, just the 14 with Offer. (I want to create the separate aliases because once a player narrows down to his final list, that takes precedence over all of his offers. Most kids will have 5-6 before it's over, then reduce to two or three. In this kid's case, he has 14 offers, so far. He'll add more this summer, then come out with a list of 4-5 in the fall.)
  5. I've restructured all the tables.
  6. It's not a problem, just noting the irony of the situation. Still surprised there isn't a statement to get acronyms, or at least some way to use substring, noting a delimiter. Not anymore. All IDs now.
  7. Between LEFT, RIGHT, SUBSTRING, SUBSR and a couple of more, there seems to be 4-6 ways I could just get the first letter of each field. None to get the first letter of each word? Code gymnastics? Anymore than using CASE? Normally I'm hammered on here for having too many columns or too many times duplicating data.
  8. Hard to believe there isn't a way to get what is essentially an acronym.
  9. It would just be the space in between the two words. Is the syntax close on what I typed in my OP?
  10. I have a column = city In some instances, I just need the first the letter of each word in the city. Some city names are more than one word. So Flora would be (F) and Fort Wayne (FW). It's the WHEN toggle = 2 ... line. $query="SELECT id, (CASE WHEN toggle = 1 THEN concat(city,' ',school) WHEN toggle = 2 THEN concat(school,' (',substring(city,1,1),')') ELSE school END) as schools FROM a_schools ORDER BY schools "; Instead of substring i've tried acronym and initials. acronym(city) I found those in a search, but I got errors suggesting they weren't valid options. I've also tried regex_replace(city, [a-z], '') but got syntax errors too. Could be I didn't apply it right.
  11. 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 ";
  12. 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.
  13. 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.
  14. Removed the pgrade alias, now I'm getting Unknown column 'mostRecent.nameLast' in 'field list'
  15. You said to alias them if needed.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.