How do I modify an attribute across all rows in a table?

Question

Hi folks,

My apologies for asking such a novice question but, I need help building a script using either PHP or directly in MySQL that can do the following:

1. Take the values of a column in a table (text)
2. Change them into capitalized words (from "this is a title" to "This Is A Title")
3. Replace the old values (uncapitalized) with the new values (capitalized).

Thanks for the help and support.

Answer 1

This is not a function that is native to MySQL, so using PHP's ucwords might save us some time.

Note: This will run a single UPDATE query for each row in your table.

<?php

  $r = mysql_query("SELECT id, name FROM users");
  while($u = mysql_fetch_object($r)){
    $r2 = mysql_query("UPDATE users SET name=".ucwords($u->name)." WHERE id={$u=>id}");
  }

?>

Answer 2

What you'll want to do is fetch all the values, and for each one, run the PHP function ucwords on it. Code sample coming shortly...

EDIT: @smotchkkiss beat me to it -- use his code :-)

Answer 3

1. MySQL doesn't have a function like Oracle's initcap - you have to create the function yourself:

   DELIMITER $$
   DROP FUNCTION IF EXISTS `test`.`initcap`$$
   
   
   CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
   BEGIN
   
   
     SET @str='';
     SET @l_str='';
   
   
     WHILE x REGEXP ' ' DO
       SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
       SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
       SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
     END WHILE;
   
   
     RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
   
   
   END$$
   
   
   DELIMITER ;
   

   Mind that the text length on the parameter is as long as your longest text field.

2. Use an UPDATE statement:

   UPDATE TABLE 
      SET text_column = initcap(text_column)
   

Answer 4

Select your rows with SQL along the lines of:

SELECT <string> FROM <table> [WHERE <field> = <whatever>]

Update the string using PHP's ucwords() function:

$UpperCaseString = ucwords($lowercase);

Update each record:

UPDATE <table> SET <fieldname> = <UpperCaseString> WHERE id=<id>