Jump to content

Jim R

Members
  • Content Count

    809
  • 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. Even simpler as it turns out. This returns every coach whose tenure hasn't ended yet. select s.id as sid,coachFirst,coachLast,season_hired,season_end,username,s.server,toggle,city,school,nickname,bsect from a_coach c left join a_schools s on s.id = c.school_id where season_end is null order by s.id
  2. I also have a seasons table. I could join that for the match, right?
  3. OK...I'll play around with it, and I see what you're saying. I'lll have to set up the coaching tenure table before I can visualize how it will really help me with queries, but as always, I appreciate your input. When you suggested that, I wasn't ready to start adding coaches, and this is the first off-season I've messed with coaching changes. Everything is working well except this view, but I'd like to get the view set up for my writers. There is one issue with this though. When there is a coaching change, there is a change of contact information. I have the server name of each school in the school table, which for the most part is static (some use a personal email address instead of a school address). I have the Username (before the @) in the coach table. So even if using this extra table, I will have to type a new user name on each change. So either I change in the coach table with each change, or add it with a new coaching tenure line.
  4. I am. I have a school table and coach table. In the coach table, I have school_id. Each time there is a coaching change, I add a new row and fill in the end season of the coach he replaces. I've read some things saying I can have a subquery in a view. Maybe it was because the subquery was in the From statement. MySQL program won't allow it, and there is something about MySQL workbench that I don't like. I don't remember at the moment though, but I've played around with it. I'm using Sequel Pro.
  5. ...and my host isn't upgrading to 5.7 anytime soon evidently. So I'm looking for an alternative way to query the most recent instances of a each result. I have the subquery that works for my site, but I'm wanting to create a View since I've eliminated all duplicated data 😉 Here is the query that creates the list pictured below: select s.id as sid,coachFirst,coachLast,season_hired,username,s.server,toggle,city,school,nickname,bsect from a_coach c inner join a_schools s on s.id = c.school_id order by s.id,season_hired desc sid = school ID sids 1, 4, 7 and 10 all made coaching changes this past summer. I want to reflect them as the current coach for the school (sid) based on the season_hired column. Group By, of course, shows the first instance, which really seems counterintuitive. I have messed around with using MAX, e.g. max(season_hired): max(season_hired) in the SELECT statement with a GROUP BY s.id after the JOIN. but it shows the past coach with the current hire date, so it would show for sid(1) = Rodney Williams hired in 2021. Is my only option the nested subquery? Here is the query with the subquery that gets me what I want for my website, but you can't create views with subqueries in MySQL 5.6. select * FROM ( select s.id as sid,coachFirst,coachLast,season_hired,username,s.server,toggle,city,school,nickname,bsect from a_coach c left join a_schools s on s.id = c.school_id order by sid,season_hired desc) mr group by mr.sid
  6. 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.
  7. 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.
  8. 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.
  9. 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.)
  10. I've restructured all the tables.
  11. 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.
  12. 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.
  13. Hard to believe there isn't a way to get what is essentially an acronym.
  14. It would just be the space in between the two words. Is the syntax close on what I typed in my OP?
  15. 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.
×
×
  • 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.