Skip to main content
W
waltoncreative
Inspiring
November 9, 2010
Answered

How to Capitalize the First Letter in Every Word in Mysql

  • November 9, 2010
  • 1 reply
  • 3805 views

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??

This topic has been closed for replies.
Correct answer David_Powers

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!


<?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.

1 reply

W
waltoncreativeAuthor
Inspiring
November 9, 2010

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
David_Powers
Inspiring
November 9, 2010

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)))
David_Powers
David_Powers
Inspiring
November 9, 2010

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


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']);
}
?>
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices