Jump to content

Jim R

Members
  • Posts

    988
  • Joined

  • Last visited

  • Days Won

    1

Jim R last won the day on July 26 2018

Jim R had the most liked content!

Profile Information

  • Gender
    Not Telling

Recent Profile Visitors

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

Jim R's Achievements

Prolific Member

Prolific Member (5/5)

1

Reputation

7

Community Answers

  1. I saw an example of that, but it had braces around field and blanked out my page. Thank you!
  2. level is the column in question of the query. Right now I have order by -level. I want it to be... I tried... order by -level desc ...when I do that, it push KJ and Mark to the bottom. Am I going to have to dig into this with CASE?
  3. I apologize. I omitted that I'm pulling these from a database, and that's the mental issue I was having with the array. id | ranking | team The += is clearly not anything I knew existed.
  4. Looking for advice on what to look for. Likely sorting arrays, but I can't find an example anywhere that matches the basic gist of what I need. Multiple people will provide a list. Person 1: 1. Team A 2. Team B 3. Team C 4. Team D Person 2 1. Team C 2. Team B 3. Team D 4. Team A It goes on from there. Each of those teams get a value based on where they are on the list. 1 = 1, 2 = 2, etc. So Team A would have 5, Team 4, Team C 4, Team D 7. I want to rank them according to lowest total value to highest. I assume I'll be using asort, as well as a foreach loop. However, I can't wrap my head around how I would set up the array.
  5. Soooo...even using an alias, such p.id as pid and s.id as sid caused it to be uneditable because I had two columns with the same name. Makes sense regardless, since changing s.id would create issues because I wouldn't be interested in changing the id of that field in a_schools. Rather use schoolID on the a_players table when a player changes schools. I do appreciate your time. 90% of time you guys help directly. The other 10%, you bring new ideas to the table for me check...and I get lucky.
  6. Appreciate it, but that's not feasible for my needs. I don't update multiple players to the same value all that often, never in the rankPos column, rarely in a bulk way for any other column. That was the other nice thing I had. When I'd change rankPos for a player, the View would reload, so my rankings were always in order. Hopefully someone else will wander and take a look.
  7. Example: This view is allowing me to edit: CREATE ALGORITHM=UNDEFINED DEFINER=`courtana_jwr`@`47.227.210.171` SQL SECURITY DEFINER VIEW `_offers` AS SELECT `p`.`id` AS `pid`, `p`.`nameFirst` AS `nameFirst`, `p`.`nameLast` AS `nameLast`, `p`.`grade` AS `grade`, `s`.`city` AS `city`, `s`.`school` AS `school`, `c`.`college` AS `college`, `c`.`level` AS `level`, `po`.`recruit_type` AS `recruit_type`, `po`.`time` AS `time` FROM (((`a_players_offers` `po` left join `a_players` `p` on((`po`.`playerID` = `p`.`id`))) left join `a_schools` `s` on((`p`.`schoolID` = `s`.`id`))) join `a_colleges` `c` on((`po`.`collegeID` = `c`.`id`))) order by `po`.`time` desc; That's with two left joins and two order by criteria. I've tried the other View with left joins, still won't edit. So what's the difference?
  8. I did have Order By with Joins in these views before, and I have other views, that definitely have Order By with Joins, which I can edit. I didn't see anything in the documentation you linked that spoke of Order By and Limit. I saw a reference to "certain types of joins", but it I didn't see the information on it.
  9. Any thoughts on which part of the order by? Because it's pretty essential to the view. IIRC, that's the same Order By I've had the whole time. The only thing that changed was joining the two aau_ tables. I forgot to change that. I really gutted how I was handling players' summer teams. I wanted more flexibility to create content.
  10. The View in question: CREATE ALGORITHM=UNDEFINED DEFINER=`courtana`@`%` SQL SECURITY DEFINER VIEW `_26_5` AS SELECT `p`.`id` AS `pid`, `p`.`grouping` AS `grouping`, `p`.`position` AS `position`, `p`.`rankClass` AS `rankClass`, `p`.`rankPos` AS `rankPos`, `p`.`nameFirst` AS `nameFirst`, `p`.`nameLast` AS `nameLast`, `p`.`feet` AS `feet`, `p`.`inches` AS `inches`, `p`.`level` AS `level`, `p`.`grade` AS `grade`, `s`.`school_name` AS `school_name`, `s`.`id` AS `sid`, `t`.`aau_team` AS `aau_team` FROM (((`a_players` `p` join `a_schools` `s` on((`p`.`schoolID` = `s`.`id`))) join `a_aau` `a` on((`p`.`id` = `a`.`playerID`))) join `a_aau_teams` `t` on((`t`.`id` = `a`.`aau_team_id`))) where ((`p`.`grade` = '26') and (`p`.`position` = '5') and (`p`.`grouping` is not null)) order by `p`.`grouping` desc,-(`p`.`rankPos`) desc,`p`.`nameLast`; a_aau table CREATE TABLE `a_aau` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `playerID` int(2) DEFAULT NULL, `aau_uniform` int(3) DEFAULT NULL, `aau_team_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `Team/Player` (`aau_team_id`,`playerID`) ) ENGINE=InnoDB AUTO_INCREMENT=360 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; a_aau_teams CREATE TABLE `a_aau_teams` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `aau_program_id` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `aau_team` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `aau_year` int(2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; a_players CREATE TABLE `a_players` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `grouping` int(1) DEFAULT NULL, `position` int(1) DEFAULT NULL, `nameFirst` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `nameLast` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `feet` int(1) DEFAULT NULL, `inches` int(2) DEFAULT NULL, `schoolID` int(30) DEFAULT NULL, `grade` int(4) DEFAULT NULL, `key_player` int(2) DEFAULT NULL, `top_player` int(2) DEFAULT NULL, `varsity` int(1) DEFAULT NULL, `level` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, `rankClass` int(2) DEFAULT NULL, `comment` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `devPos` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL, `rankPos` int(3) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `Duplicate Player` (`nameFirst`,`nameLast`,`schoolID`) ) ENGINE=MyISAM AUTO_INCREMENT=2283 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; a_schools CREATE TABLE `a_schools` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `gym` int(1) DEFAULT NULL, `toggle` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL, `region` int(4) DEFAULT NULL, `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `school` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `school_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=445 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  11. In order they should be... the view a_aau a_aau_teams a_players a_schools
  12. (Disclosure: These kids have all had their names published in game programs for the public to view.) The error is noted in the picture. "Field has no or multiple table or database." I would get it if it didn't let me edit the columns I used to join the tables, but I'm not. I can't edit any of them. In my other views, I can.
  13. I don't see anything off hand either. It doesn't query other Views, just real tables. It doesn't have repeated columns, requiring aliasing other than id. I had read that page you linked a couple of times throughout the day. I use this version for 20 different views, 5 positions across the 4 different grades, and I edit grouping and rankPos a great amount as they progress through high school. This views had worked before, but I added the joins to a_aau and a_aau_teams, changing how I derived which summer/travel/AAU basketball team they play for. It broke the previous view, which I figured it would. When I use the query to create the View. I can edit the query results. (I use Sequel Pro.)
  14. I already did. That's why I came here to ask my question.
  15. I've created a number of views that were editable. I reconfigured how I handled one aspect and had to redo some of my views. They are now uneditable. Specifically, two columns (grouping and rankPos) are updated a lot. Occasionally s.id is too. What about this structure would make it uneditable? select p.id as pid, p.grouping, p.position, p.rankClass, p.rankPos, p.nameFirst, p.nameLast, p.feet, p.inches, p.level, p.grade, school_name, s.id as sid, aau_team from a_players p JOIN a_schools s ON p.schoolID = s.id JOIN a_aau a ON p.id = a.playerID JOIN a_aau_teams t ON t.id = a.aau_team_id WHERE grade = 26 && position = 5 && grouping IS NOT NULL ORDER BY grouping desc,-rankPos desc,nameLast
×
×
  • 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.