Split Names Stored in MySQL

This one is rather handy for anyone who forgot to design his / her database and stores user names in one field rather then splitting it as first name last name combination. Jeff who runs an online store based on oscommerce recently posted a request that he wanted to show Artists name as Last name, First Name instead of generic First name Last Name in drop down. Rather easy thing to achieve if you have stored the names separately, but that was not the case.

The original query for fetching names was using order by name, so result set had names in ascending order as per first name.

MySQL (or any SQL for that matter) has options to use substrings, and I made use of that function to modify query, so that in order by clause, I made use of substring to extract last name, and that was then used to order names. Second step to display names as last name, first name was achieved by using explode() function, and then echo names as name[1] ." ".name[0].

I hope you find this useful if you ever wish to split string stored in database, and then manipulate the names.

Here are the code chunks for anyone interested to try it out on their stores.
***********************************************************************
// Original Query

// $manufacturers_query = tep_db_query("select manufacturers_id, manufacturers_name from " . TABLE_MANUFACTURERS . " order by manufacturers_name");

Replace
" order by manufacturers_name"
With
"order by substr( manufacturers_name, instr( manufacturers_name, ' ' ) )"

**********************************************************************
Next to split names, stored as a single string in database (which is coming as manufacturers_name in the query shown above, I added a variable array as shown below

$newname=explode(" ",$manufacturers_name);
Now first name gets stored in newname[0], and last name gets stored in newname[1]
So to display in PHP, all I had to do was reverse the display order and it was all set.

By the way the store in question for all art lovers is THIS.

Happy Shopping & OSCommerce rocks.

Late edition : .IN domain registration is picking up now, I notice more folks are keen to get good and easy to remember domain names, and .in extension is going to give a serious competition to .com. If you haven't already registered one for yourself, you can get them at a very attractive discount on www.finehost.in !!!

So long,
Pubdomains