Sort products by second word

[skyace888]

I am finishing up work on an OSCommerce setup for someone and they had a request. Most of the products are named by a person's name such as "John Doe" or "Joseph Dean". By default, the products are sorted by the first name in each category. Is there a way I can sort the products by last name so "Joseph Dean" comes before "John Doe"? I don't want to have to change the names of the products to a "lastname, firstname" format. Also, all of the items are already in the database and we are talking about 5000+ products. Any suggestions are greatly appreciated.  I already posted this in the OSCommerce forum, but figured I'd ask here as well since it's more of a database question.

 

Thanks,

Al

[toplay]

As long as you have a space or something separating the first part of the product name with the second, then you can use SUBSTRING_INDEX().

 

SELECT SUBSTRING_INDEX('John Doe', ' ', 1); # Returns John

SELECT SUBSTRING_INDEX('John Doe', ' ', -1); # Returns Doe

 

# So, here's an example query:

SELECT   SUBSTRING_INDEX(`column_name`, ' ', -1) AS last_name

           , SUBSTRING_INDEX(`column_name`, ' ', 1) AS first_name

  FROM

             `table_name`

ORDER BY

       last_name, first_name

;

 

 

See:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

 

 

So, this hard coded example does return Joseph Dean first:

 

SELECT    SUBSTRING_INDEX('John Doe', ' ', -1) AS last_name

            , SUBSTRING_INDEX('John Doe', ' ', 1) AS first_name

UNION

SELECT    SUBSTRING_INDEX('Joseph Dean', ' ', -1) AS last_name

            , SUBSTRING_INDEX('Joseph Dean', ' ', 1) AS first_name

 

ORDER BY

      last_name, first_name

;

 

[skyace888]

Thanks for the reply, toplay.  This query doesn't appear to be working properly for me.  I forgot to mention that many of the rows have more than two strings in the name field such as "John Doe 1985" or "Chris Davis Special Edition".  I noticed that the "-1" appears to take the last string from the field.  When I run the query, all but a few of the last names are shown under last_name.  Is there a way to nest this function?  Again, I am trying to sort this by the second string in the field.  Thanks...

[skyace888]

I think I got it.  This seems to work for me.

 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(products_name, ' ', 2), ' ', -1) AS 

last_name, SUBSTRING_INDEX(products_name, ' ', 1) AS first_name FROM 

products_description ORDER BY last_name, first_name;

 

Is there a better way to do this?  Also, if you happen to have worked with the OSCommerce package before, where on the index.php page (and any other pages) do I need to make my modifications?  Thanks again...

[fenway]

The "better way" would be to split this non-atomic field.