Group by chronology...

[Jim R]

I have a number of reviews for each player, and this is supposed to list of the 10 most recent players reviewed.  However, it's showing the most recent FIRST review for each player.  So if a player had the most recent review and also had the first review, he would never show up on this list because it's grouping each player by their first/oldest review. 

$query = "SELECT *, apr.grade as grade FROM a_players_reviews apr 
inner join a_players as ap
where concat(apr.nameFirst,apr.nameLast,apr.school) = concat (ap.nameFirst,ap.nameLast,ap.school)
GROUP BY concat(apr.nameFirst,apr.nameLast) ORDER BY time DESC LIMIT 10";

[Barand]

You should not be storing names and schools in both tables, they belong in the player table only along with a unique id for each player. It is that id that should be in the review table and that is what you should then join on.

 

You were told before about about those concats in your where clause (so I guess telling you anything is a waste of time). It is also completely unnecessary in the group by - just

... GROUP BY apr.nameFirst, apr.nameLast

Do not use "SELECT *", Specify the columns you need. This is particularly wasteful when joining tables as there will always be duplicate (matching) column values.

 

As or your problem stated, try

... ORDER BY MAX(time) DESC LIMIT 10

[ginerjm]

Your first sentence is rather confusing. Perhaps you could re-write it and clarify what you are trying to produce.

 

As for the query it appears you are asking for 10 records beginning with the newest ones getting the priority from the database. Not sure what you are actually getting but how about changing the order of your query? Select the players and do an outer join on reviews with the where clause in the reviews portion of the query?

 

PS - not an expert on joins but don't you need an 'on' clause to join the two tables?

[Jim R]

You should not be storing names and schools in both tables, they belong in the player table only along with a unique id for each player. It is that id that should be in the review table and that is what you should then join on.

 

You were told before about about those concats in your where clause (so I guess telling you anything is a waste of time). It is also completely unnecessary in the group by - just

... GROUP BY apr.nameFirst, apr.nameLast

Do not use "SELECT *", Specify the columns you need. This is particularly wasteful when joining tables as there will always be duplicate (matching) column values.

 

As or your problem stated, try

... ORDER BY MAX(time) DESC LIMIT 10

 

Actually I just added the concat before I copied it to see if it changed anything, and on the Select * stuff.  The query is the query.  What I print out is often most of the columns.  

 

I have my reasons for storing names in both tables.  I and others spend a lot of time typing directly into the database, and I don't want nor want them to have to worry about matching ID's, especially if they're inputting on their phone.  

 

 

The max(time) worked.  Thanks.

[Barand]

PS - not an expert on joins but don't you need an 'on' clause to join the two tables?

It is more efficient (amongst other advantages) to use

 

FROM A JOIN B ON condition[code]

but you can do inner joins using

[code]FROM A, B WHERE condition

[Barand]

I have my reasons for storing names in both tables.  I and others spend a lot of time typing directly into the database, and I don't want nor want them to have to worry about matching ID's, especially if they're inputting on their phone.  

 

That's a ridiculous reason and is an excellent example of why not to do it. It only needs a simple typo and the data will not match.

[Jim R]

That's a ridiculous reason and is an excellent example of why not to do it. It only needs a simple typo and the data will not match.

 

Certainly, but then it won't show up as it should and we'll know.  Easy enough to correct.  They don't know the ID of a player as they're reviewing and are inputting via the data table directly or a form.  There is no way around it.  

[ginerjm]

If the app was written properly there most certainly WOULD be a way around it......

 

If they make a d/e mistake you then have to correct that mistake. Hell of a master plan there.

 

To type in names in two places is just NOT what data processors do.

Edited January 15, 2018 by ginerjm

[Barand]

I and others spend a lot of time typing directly into the database, ...

That is the really frightening thing. Madness reigns.

[Jim R]

If the app was written properly there most certainly WOULD be a way around it......

 

If they make a d/e mistake you then have to correct that mistake. Hell of a master plan there.

 

To type in names in two places is just NOT what data processors do.

 

Not that I have to explain this, but as a form submits a player name and review, any new name from the form creates a new row in the master list with a separate grouping identifier.  That grouping is ultimately based on their talent level, per later consideration.  So we want to know who the new entries are.  If we see a pre-existing name due to a typo, we'll know it. 

 

So we don't type names in two places.  We either type them in the master list, or from a form we type about them while watching them play.  

 

And since I distribute to my two other writers these players names and social media handles, they import them into their phone and can dictate about 95% of their work accurately on their phone.  As I try to add features, sometimes I run into a learning curve.  

 

While I know I can insert a corresponding ID, I still like to open up the database so I can see a larger section of rows than what a normally styled web page can or should show.    

 

None of this really has anything to do with my issue.  I just couldn't figure out how change how items are grouped, relative to time.  Any time I searched "order", it would show results with Group By and Order By.  It wasn't helping.  

 

That is the really frightening thing. Madness reigns.

 

 

How else do you put data into a database other than directly, importing or via a form?  So me sitting at my desk typing into Sequel Pro from my notes is madness reigning?  (The good news is you didn't overreact.)  

[ginerjm]

Personally if I was doing d/e towards a list of player names, I would create dropdown for my form and have the user select the desired name to be entered. This dropdown of course would include an id for the names and your form would capture that id (perhaps even hidden) to use for the db updates. Now you have no typing at all except on a separate form (or not) to create new players.

 

But all of this is unimportant because you seem to have chosen this path for your design and are unwilling to understand how bad it is.

[Jim R]

Personally if I was doing d/e towards a list of player names, I would create dropdown for my form and have the user select the desired name to be entered. This dropdown of course would include an id for the names and your form would capture that id (perhaps even hidden) to use for the db updates. Now you have no typing at all except on a separate form (or not) to create new players.

 

But all of this is unimportant because you seem to have chosen this path for your design and are unwilling to understand how bad it is.

 

You want me to have a drop down for over 1,000 names?  

 

What about the new names?  

 

 

 

I'm setting this up to quick and efficient at the point of entry.  

 

I have an outstanding system.  I have a form that submits reviews, and if that player doesn't exist in my main table, it forwards the User to another form, as well as the already completed values (First name, last name, city (if applicable), school and grade), to enter his info to the main table.  I can't make it automatically because more information is needed that I don't want my Users to have to type in every time they review a player.  

 

Meanwhile, with each new player entered, it preserves them as new until I process them, in terms of caliber and geographical area.  I could pre-program the geography, but not all my Users are as adept as I am at knowing which county a school is in.  I could link geographical area to the city of the school as well, but with over 420 schools in Indiana, I'm not really interested in having my Users have to use a drop down menu or locate their school ID.  

 

However, in creating that list of new players, it's an additional resource for coaches who read our material, featuring those kids.  Once a week I go in and process them.  If I see one already in the main table due to a typo or misspelling, I can easily change it.  

 

Repeating, I'm setting this up to quick and efficient at the point of entry.  I came here to ask how I can get chronology of grouped values to the way I wanted it.  It turned out it was a simple solution, which I was unable to find easily via searches.  You'll forgive me if I don't want to push a drop down menu of 1,000 kids.  Maybe one that matches possible last names to selected first names, but the matching of the ID's then would still have to be matched.  All of this because you don't like it that I have names in two tables.  

 

 

 

I truly appreciate the help I get here, and I try to help others while I'm looking for my own solutions.  It's likely best to stick to the topic at hand.  

 

MAX(time) worked great. 

Edited January 16, 2018 by Jim R

[ginerjm]

LOL. Yes - 1000 would be a large list. Didn't realize your size. But - an interactive entry field there would perhaps be helpful - one that captures each character and filters the list to match the input so far - when you have to the time to enhance your appl.

[Jim R]

LOL. Yes - 1000 would be a large list. Didn't realize your size. But - an interactive entry field there would perhaps be helpful - one that captures each character and filters the list to match the input so far - when you have to the time to enhance your appl.

 

Interactive would be great, but that's more coding and MANY more questions.  

 

I did get rid of the concats.  

 

 

When I have to improve them, I'm going to move over to mysqli or dig into PDO.  

[Jim R]

The closest I could come would be to choose the school first.  That may be a consideration, but I still like the name in both tables.  When I'm home, I just type straight into the database.  

[gizmola]

The closest I could come would be to choose the school first.  That may be a consideration, but I still like the name in both tables.  When I'm home, I just type straight into the database.

I was under the impression that this was some sort of academic project and you were a student learning as you go. When I pointed out a significant design issue in your prior question, I just assumed that it didn't matter to you very much, because you were just doing an assignment and could live with issues that perhaps were not relavant to the assignment.

 

If you are doing this for something that you and/or others intend to use, then you would be best serve to heed the advice of experienced database design and development people.

 

Your tables need primary keys.

Don't de-normalize/duplicate columns when you aren't experienced in the tradeoffs.

 

When you have a well designed database structure, your application will be performant, easy to query and logical. These design rules are not just the best practice of experts, but literally came from the man who invented relational databases.

[Jim R]

I was under the impression that this was some sort of academic project and you were a student learning as you go. When I pointed out a significant design issue in your prior question, I just assumed that it didn't matter to you very much, because you were just doing an assignment and could live with issues that perhaps were not relavant to the assignment.

 

If you are doing this for something that you and/or others intend to use, then you would be best serve to heed the advice of experienced database design and development people.

 

Your tables need primary keys.

Don't de-normalize/duplicate columns when you aren't experienced in the tradeoffs.

 

When you have a well designed database structure, your application will be performant, easy to query and logical. These design rules are not just the best practice of experts, but literally came from the man who invented relational databases.

 

Understood, but it's still a working environment I want to be as efficient as possible for my Users.  I work in my main table directly often, so I want the names there.  My Users (me included) enter into the other table from a form.  They are going to enter the names, ranging from their phone, a tablet or a laptop.  I'm not making them look up ID's to complete their work, nor am I having them use a drop down menu.  

 

All my tables have a primary key.  I also want the names in the second table because I limit what's shown on the website, and I'd like to be able to easily locate all the information, especially if I need to find it on my phone.  A large number of players will have more evaluations over their four year high school careers than what is shown on the site.  Not only that, it's much easier to match the names to the WordPress slugs than trying to find those ID's too.  

 

 

I'm 48.  I'm self-taught, and I do my own work, outside of the help I've had here over the years.  When this site went down, I reached out to whomever ran the Twitter account and offered to host this forum if the issue of keeping it going was hosting expenses.  

 

You have to accept sometimes the needs of the User outweigh the intended purpose, especially when it's usage is narrow of focus.  Having an extra column isn't hindering anything other someone's sense of structure.