How to Capitalize the First Letter in Every Word in Mysql

[waltoncreative]

Hi,

I have been trying to tidy up a massive database where visitors have been sloppy when entering text. The main thing I want to do is to Capitalize The First Letter In Every Word In Mysql.

I have found the code below in PHP but it keeps finding an error on the WHILE line:

<?php ini_set('display_errors', '1'); ?>

<?php require_once('Connections/maison_connection.php'); ?>

<?php

$result = mysql_query ("SELECT column, id FROM table");

while ($row = mysql_fetch_array($result)) {

$id = $row["id"];

$column2 = ucwords($row["column"]);

$query2 = "UPDATE table SET column = '$column2′ WHERE id = '$id'";

mysql_query($query2);

}

?>

My table is called MailingList and the Column is called Name, so I have altered the script to this: but it still shows the same WHILE error:

<?php ini_set('display_errors', '1'); ?>

<?php require_once('Connections/maison_connection.php'); ?>

<?php

$result = mysql_query ("SELECT Name, id FROM MailingList");

while ($row = mysql_fetch_array($result)) {

$id = $row["id"];

$Name2 = ucwords($row["Name"]);

$query2 = "UPDATE MailingList SET Name = '$Name2′ WHERE id = '$id'";

mysql_query($query2);

}

?>

Any ideas??

[waltoncreative]

I got it to work this way in SQL: I am SURE there is an abbreviated way to do it, but at least it works

UPDATE MailingList SET Name = replace(Name," a"," A");

UPDATE MailingList SET Name = replace(Name," b"," B");

UPDATE MailingList SET Name = replace(Name," c"," C");

UPDATE MailingList SET Name = replace(Name," d"," D");

UPDATE MailingList SET Name = replace(Name," e"," E");

UPDATE MailingList SET Name = replace(Name," f"," F");

UPDATE MailingList SET Name = replace(Name," g"," G");

UPDATE MailingList SET Name = replace(Name," h"," H");

UPDATE MailingList SET Name = replace(Name," i"," I");

UPDATE MailingList SET Name = replace(Name," j"," J");

UPDATE MailingList SET Name = replace(Name," k"," K");

UPDATE MailingList SET Name = replace(Name," l"," L");

UPDATE MailingList SET Name = replace(Name," m"," M");

UPDATE MailingList SET Name = replace(Name," n"," N");

UPDATE MailingList SET Name = replace(Name," o"," O");

UPDATE MailingList SET Name = replace(Name," p"," P");

UPDATE MailingList SET Name = replace(Name," q"," Q");

UPDATE MailingList SET Name = replace(Name," r"," R");

UPDATE MailingList SET Name = replace(Name," s"," S");

UPDATE MailingList SET Name = replace(Name," t"," T");

UPDATE MailingList SET Name = replace(Name," u"," U");

UPDATE MailingList SET Name = replace(Name," v"," V");

UPDATE MailingList SET Name = replace(Name," w"," W");

UPDATE MailingList SET Name = replace(Name," x"," X");

UPDATE MailingList SET Name = replace(Name," y"," Y");

UPDATE MailingList SET Name = replace(Name," z"," Z");

[David_Powers]

colinwalton wrote:

I got it to work this way in SQL: I am SURE there is an abbreviated way to do it, but at least it works

UPDATE MailingList SET Name = replace(Name," a"," A");

UPDATE MailingList SET Name = replace(Name," b"," B");

Sorry to be the bearer of bad news, but that changes all lowercase letters to uppercase, regardless of where they are in the word.

To get back to normality, you need to run the following query:

UPDATE MailingList SET Name =
CONCAT(UPPER(LEFT(Name, 1)), LOWER(SUBSTRING(Name, 2)))

[waltoncreative]

Thanks - but all is actually not lost!

There is a space before the letters each time, so I am searching and replacing letters that are preceded by a space.

I was rather hoping there was an abbreviated way of doing it rather than laboriously doing them for all the letters of the alphabet.

C

[David_Powers]

colinwalton wrote:

I was rather hoping there was an abbreviated way of doing it rather than laboriously doing them for all the letters of the alphabet.

Well, the easier way is to use PHP. I didn't look very closely at your previous code, but the following should work (but make a backup of the data first).

<?php
$conn = mysql_connect($host, $user, $pwd);
mysql_select_db('database_name');
$sql = 'SELECT * FROM MailingList';
$result = mysql_query($sql);
$updates = array();
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
    $updates[$i]['id'] = $row['id'];
    $updates[$i]['Name'] = ucwords($row['Name']);
    $i++;
}
for ($i = 0, $len = count($updates); $i < $len; $i++) {
    mysql_query("UPDATE MailingList
                SET Name = '" . $updates[$i]['Name'] . "'
                WHERE id = " . $updates[$i]['id']);
}
?>

[waltoncreative]

That's great thanks. Can I be a real pain and ask you to annotate the parts so I can try to understand what each part does please?

Thank you!

[David_Powers · Accepted solution]

<?php
// connect to database and select all items in MailingList
$conn = mysql_connect($host, $user, $pwd);
mysql_select_db('database_name');
$sql = 'SELECT * FROM MailingList';
$result = mysql_query($sql);

// create an empty array to store the results
$updates = array();

// initialize a counter to keep track of each row
$i = 0;

// loop through the database results
while ($row = mysql_fetch_assoc($result)) {
    // assign the current ID to the current array element
    $updates[$i]['id'] = $row['id'];
    // convert the current Name to initial caps
    // and assign it to the current array element
    $updates[$i]['Name'] = ucwords($row['Name']);
    // increase the counter
    $i++;
}

// after assigning all database results to $updates,
// loop through the $updates array and update each row
for ($i = 0, $len = count($updates); $i < $len; $i++) {
    mysql_query("UPDATE MailingList
                SET Name = '" . $updates[$i]['Name'] . "'
                WHERE id = " . $updates[$i]['id']);
}
?>

The key is to use a multidimensional array. The first time the while loop runs through the database results, $i is 0, so the values are assigned to $updates[0]['id'] and $updates[0]['Name']. The next one becomes $updates[1]['id'] and $updates[1]['Name'], and so on.

The for loop resets $i to 0, and uses count($updates) to get the length of the array. It then updates each row using  $updates[0]['Name'] and $updates[0]['id'],  $updates[1]['Name'] and $updates[1]['id'], and so on.

[waltoncreative]

Very clever thanks!