Save me
Moderator: General Moderators
Save me
Before all of you screem at me for my short site, realize that I was just following orders. I have a user database with one field for a name. That name contains first,middle,last and perhaps a title (PhD). Well, after everything was done a settled my client wants to sort by last name. Do I have to recode everthing that involvs user name or does someone have some sort of formula that would actually save me.
Thanks
Mike B
Thanks
Mike B
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
You don't _really_ need to change or recode everything that involves user name in the database, but that doesn't mean you shouldn't. It depends on the complexity and effort that change will require and on the total benefit that change will bring.
You can treat the user name (Tit. First [Middle] Last) with PHP exploding it and then applying some kind of sort or array_multisort iteration routine against the resulting array. This also require a bit of complexity and effort to build a code that treat all kind of names.
Otherwise you can recode your table design to break this unique filed into three or four (title, first, middle, last) and then fetch data from the database ordered by last name.
Personally, I would redesign the database and let all sorting party up to the DBMS.
-- Scorphus
You can treat the user name (Tit. First [Middle] Last) with PHP exploding it and then applying some kind of sort or array_multisort iteration routine against the resulting array. This also require a bit of complexity and effort to build a code that treat all kind of names.
Otherwise you can recode your table design to break this unique filed into three or four (title, first, middle, last) and then fetch data from the database ordered by last name.
Personally, I would redesign the database and let all sorting party up to the DBMS.
-- Scorphus
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
You could potentially use the php Explode command on the results to get the last name. Stick this is an array to sort. The difficulty with this approach is what happens when you have duplicate names, especially if you are not returning any other sort of index.
To solve this I would recombine the elements as Surname, Forname, Middle, Title.
Obviously it depends on the complexity of your system (and political considerations) but I would take the opportunity to restructure the code and the DB to split the name into forename, surname, middle(s), title. This structure is a lot more useful if you need to expand the system at a later stage.
To solve this I would recombine the elements as Surname, Forname, Middle, Title.
Obviously it depends on the complexity of your system (and political considerations) but I would take the opportunity to restructure the code and the DB to split the name into forename, surname, middle(s), title. This structure is a lot more useful if you need to expand the system at a later stage.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
personally I'd use a usort() with a preg_ to quickly extract the last name. As said previously, taking some time to restructure is a very good direction.
You don't even have to change much for a full change.. just the query calls asking mysql to concat the different parts together to return what was normal originally.
You don't even have to change much for a full change.. just the query calls asking mysql to concat the different parts together to return what was normal originally.
whatever happened to good old "ORDER BY":
That way you can let SQL handle the sorting and don't need to recode anything.
Code: Select all
SELECT `name` FROM `user_extended` ORDER BY name ASC-
Hi,
For sure you don't have to, but think about misentered names (and sure user will enter rubbish), that don't follow your expected format, (e.g. trying to split at space " "):
Bill Gates
Gates Bill
Bill, Gates
Gates, Bill
Michelle van Dijk
van Dijk Michelle
djot
-
Hi,
I totally agree with that. Seperate data as much as possible, you will have flexibility (and speed) advantages later. Depending on how many names are stored I recommend to split up name in prename and name.You don't _really_ need to change or recode everything that involves user name in the database, but that doesn't mean you shouldn't. It depends on the complexity and effort that change will require and on the total benefit that change will bring.
For sure you don't have to, but think about misentered names (and sure user will enter rubbish), that don't follow your expected format, (e.g. trying to split at space " "):
Bill Gates
Gates Bill
Bill, Gates
Gates, Bill
Michelle van Dijk
van Dijk Michelle
djot
-
We've already talked about how the design could and should be improved so i'm not going to repeat that 
this is how i would do it
notice that if there is someboy "tim van wassenhove" the query fails.. but you never know if the van belongs to the surname or the family name -> so i repeat: change your db design
this is how i would do it
Code: Select all
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 1), ' ',-1) AS orderfield
FROM user_extended
ORDER BY orderfield- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Nice posts on this topic. All thoughts are instructive, even for a non-newbie. Let me thank you, fellas.
We can see what a single post with a particular problem may cause: nice thoughts, great points of view (some of them different), good recommendations, teachings, etc. And it happens often. There are plenty of good topics on this forum. Everyday we learn a lot of things, some of them I learn here. I'm glad to be part of this community.
Thanks,
Scorphus.
Thanks, CoderGoblin, these were the exact terms that totally lacked me when writing the previous post.CoderGoblin wrote:(...) This structure is a lot more useful if you need to expand the system at a later stage.
We can see what a single post with a particular problem may cause: nice thoughts, great points of view (some of them different), good recommendations, teachings, etc. And it happens often. There are plenty of good topics on this forum. Everyday we learn a lot of things, some of them I learn here. I'm glad to be part of this community.
Thanks,
Scorphus.