MySQL & PHP: Convert Strings From All Uppercase to Uppercase Only First Letter of Each Word

I’m not sure if it is possible to make that title much shorter than that… Anyway, I had some old data that I imported to an installation of Magento. All of the product titles were all uppercase, which was quite ugly. I needed a way to convert all of those values to only have uppercase first letter of each word.

Programming that was quite easy, but I noticed that there were also some parts of the string which were not converted like “On/off”, “D.e.”, “Quick-connect”, etc. The ucwords() function doesn’t recognize that kind of stuff. So, I had to find a way around those. I created an array that houses whatever characters you’d like to capitalize after (as long as there is a letter immediately after it). So, those three examples become “On/Off”, “D.E.”, and “Quick-Connect”.

Here’s what I came up with. Feel free to use it if you wish – you’ll just have to change the database credentials, change the SELECT and UPDATE queries to suit your needs, and edit the $also_after array to uppercase after any characters you want:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<?php
// Database Connection
$db = new mysqli('localhost','username','password','database');
 
// What Ever Query You Want
$query = $db->query("SELECT value_id, value FROM catalog_product_entity_varchar WHERE attribute_id = 45");
 
// Run For Each Result
while ($line = $query->fetch_object()) {
	$value_id 	= $line->value_id;
	$value		= $line->value;
 
	// Populate this array with characters that you want this script to capitalize after.
	// If in the string there is "onn/off", put the "/" character in the array to make "On/Off"
	// otherwise, it would only do "On/off".
	$also_after	= array('-', '/', '(', '.');
 
	// Run this function for each value. Returns new string.
	$string = convert_to_uc($value, $also_after);
 
	// Escape characters for database entry
	$string = $db->real_escape_string($new_string);
 
	// Update the database with your new string
	$db->query("UPDATE catalog_product_entity_varchar SET value = '$string' WHERE value_id = '$value_id'");
}
 
// Convert To Uppercase function
function convert_to_uc($value, $also_after)
{
	$string 	= '';
 
	// Convert thet string to all lowercase, then uppercase the first letter of every word.
	$string 	= ucwords(strtolower($value));
 
	// For each $also_after character, run the uc_match function
	foreach ($also_after as $char) {
		$string = preg_replace_callback('/(\\'.$char.')(\w)/',"uc_match",$string);
	}
 
	// Returns new formatted string
	return $string;
}
 
// Uppercase Match function - used by preg_replace_callback above
function uc_match($match)
{
	return $match[1].strtoupper($match[2]);
}
This entry was posted in MySQL, PHP. Bookmark the permalink.

Comments are closed.