Page 1 of 1

Save me

Posted: Thu Feb 10, 2005 7:29 am
by meadmaker
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

Posted: Thu Feb 10, 2005 7:46 am
by Chris Corbyn
What's your code so far to get the data out of the database? If you're pulling it into an array you can probably just add a tiny adjustment to sort the array... (you may need to change the way you pull the data out of the DB into an array however)

Save me

Posted: Thu Feb 10, 2005 8:15 am
by meadmaker
I just need to sort on one page. I just pull out the name through a sql result. If you have example code to sort it from there, I would appreciate it.

Posted: Thu Feb 10, 2005 8:19 am
by feyd
please post some example data of these names and at least the query you use to pull the information out.

Example

Posted: Thu Feb 10, 2005 8:48 am
by meadmaker
SQL:

SELECT `name` FROM `user_extended` WHERE 1

name examples:

Michael Beale
Joe Smith
Darren K. Belisle
Mark Smith
Thad Harshbarger
Larry King
JJ Walker
Johnny Thai, PhD

Posted: Thu Feb 10, 2005 8:56 am
by scorphus
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

Posted: Thu Feb 10, 2005 9:06 am
by CoderGoblin
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.

Posted: Thu Feb 10, 2005 9:09 am
by feyd
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. :)

Posted: Thu Feb 10, 2005 11:17 am
by patrikG
whatever happened to good old "ORDER BY":

Code: Select all

SELECT `name` FROM `user_extended` ORDER BY name ASC
That way you can let SQL handle the sorting and don't need to recode anything.

Posted: Thu Feb 10, 2005 11:51 am
by feyd
he needs to order by the last name, not the full name.. since they are stored together.... :?

Posted: Thu Feb 10, 2005 11:52 am
by patrikG
ah. I knew my eyes were tired. I'd still advise changing the database if he can, implementating that in PHP just complicates things. Anyway.
Time to go home for me after a long day.

Posted: Thu Feb 10, 2005 11:58 am
by feyd
absolutely, on all counts.. have a pint for me ;)

Posted: Thu Feb 10, 2005 4:39 pm
by djot
-
Hi,
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.
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.

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
-

Posted: Thu Feb 10, 2005 5:06 pm
by timvw
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

Code: Select all

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 1), ' ',-1) AS orderfield
FROM user_extended
ORDER BY orderfield
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

Posted: Thu Feb 10, 2005 9:21 pm
by scorphus
Nice posts on this topic. All thoughts are instructive, even for a non-newbie. Let me thank you, fellas.
CoderGoblin wrote:(...) This structure is a lot more useful if you need to expand the system at a later stage.
Thanks, CoderGoblin, these were the exact terms that totally lacked me when writing the previous post.

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.