Case problems

[mondeo79]

My customers have an annoying habit of entering their details as either lower case or upper case rather than sentence case so I would like to be able to convert all the name and address fields to sentence case (capitalise first letter of each word) and post code to upper case when they enter the details and therefore ensure it goes into the database in the format I would like for printing labels etc and I also need to be able to do this for all my existing customers which are already set up.

 

I had a look through PHP.net and seached here and found references to some functions like UPPER, strtoupper and ucwords but I cant get them to work for new entries let alone even knowing where to begin to convert all the existing records. Also Im not sure which other files other than create_account.php I should be adding them to etc

 

Can anyone help / point me in the right direction??

 

TIA

 

Jase

[Stevis2002]

Sorry, I can't help you out there, but it sure would be a good contribution, or even an even better integration, controllable through admin maybe?

[FalseDawn]

Look for the following in create_account.php:

$sql_data_array = array('customers_firstname' => $firstname,

'customers_lastname' => $lastname,

'customers_email_address' => $email_address,

'customers_telephone' => $telephone,

'customers_fax' => $fax,

'customers_newsletter' => $newsletter,

'customers_password' => tep_encrypt_password($password));

 

$sql_data_array = array('customers_id' => $customer_id,

'entry_firstname' => $firstname,

'entry_lastname' => $lastname,

'entry_street_address' => $street_address,

'entry_postcode' => $postcode,

'entry_city' => $city,

'entry_country_id' => $country);

 

And any other fields (state is in there somewhere)

 

Just wrap anything you want converting in ucwords(strtolower())

 

Do the same in the files where they edit the name/address (address_book_process?)

 

 

I don't think there an equivalent of ucwords in SQL, so you'd have to write a code loop to read all the records, explode each column value into an array and apply the technique above on each, then write back to database.

[user99999999]

I would just leave it alone but thinking back I cant count how many databases I have done this on. I think most people know that names should be capitalized, especialy their own name, but for some reason they dont apply that knowledge to the computer.

 

My Name Is: BLIND FOOL

My Name Is: lazy bum

 

$firstname = tep_db_prepare_input($HTTP_POST_VARS['firstname']);

$firstname = ucwords(strtolower($firstname));

 

I dont think there is any mysql function to do this so you would have to write a script to update your db. You could also change your label printing code like above.

 

 

dAVE...

[user99999999]

LOL Well thats what I get for writing a post and watching TV at the same time.

[FalseDawn]

You can use sql to achieve this affect if the column contains only 1 word - firstname, lastname etc - but it won't work on addresses - eg, it will convert

"MYFIRSTNAME" to "Myfirstname", but not "ADDR1 ADD2 ADD3" to "Addr1 Addr2 Addr3"

:

 

UPDATE customers SET

customers_firstname=CONCAT(UCASE(SUBSTRING(customers_firstname,1,1)),LCASE(SUBST

RING(customers_firstname,2)));

 

 

change customers_firstname to whatever column you want.

[user99999999]

Try a script instead...

 

<?php
 require('includes/application_top.php');
 $sql = "select customers_id, customers_firstname, customers_lastname from " . TABLE_CUSTOMERS;
 $query = tep_db_query($sql);
 while ($customers = tep_db_fetch_array($query)) {
   $firstname = ucwords(strtolower($customers['customers_firstname']));
   $lastname = ucwords(strtolower($customers['customers_lastname']));
   $sql = "update " . TABLE_CUSTOMERS . " set customers_firstname='" . $firstname . "', customers_lastname='" . $lastname . "' where customers_id='" . $customers['customers_id'] . "'";
   tep_db_query($sql);
 }
 $sql = "select address_book_id, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_city from " . TABLE_ADDRESS_BOOK;
 $query = tep_db_query($sql);
 while ($address = tep_db_fetch_array($query)) {
   $frstname = ucwords(strtolower($address['entry_firstname']));
   $lastname = ucwords(strtolower($address['entry_lastname']));
   $street = ucwords(strtolower($address['entry_street_address']));
   $suburb = ucwords(strtolower($address['entry_suburb']));
   $city = ucwords(strtolower($address['entry_city']));
   $sql = "update " . TABLE_ADDRESS_BOOK . " set entry_firstname='" . $firstname . "', entry_lastname='" . $lastname . "', entry_street_address='" . $street . "', entry_suburb='" . $suburb . "', entry_city='" . $city . "' where address_book_id='" . $address['address_book_id'] . "'";
   echo $sql;
   tep_db_query($sql);
 }
?>

[Guest]

Dave's code is preferable...real nice.

 

However, I prefer to pull the data into an array and loop that instead of looping the SQL query array.

 

Also, you should free the resource after you're done with the query. This helps keep the amount of server resources to a minimum.

 

Either way Dave's code will do what you want...be sure to thank him for his time.

 

Bobby

[FalseDawn]

Yeah - the script is good, but to make it safe, you need to wrap the updates in tep_db_input () to handle cases where there are apostophes, quotes etc in the text.

[FalseDawn]

Or better yet, use the tep_db_perform() function, which I think does this automatically

[mondeo79]

Thanks to everyone who replied - in the end I decided not to modify anything in the database but instead I used ucwords(strtoupper()) in the labelling contribution and also in packingslip.php and invoice.php so irrespective of how the customer enters their address it is printed in upper case.

 

Jase