MySQL edit values

Question

Ok, here's an easy one:

I have a table in MySQL where I keep some country names (inserted by the user). The "problem" is that some of the users inserted the country name having no first capital letter.

A need a script (query+PHP) that selects all the values from 1 column inside the table, and apply a PHP script that makes the first letter of every word a capital letter, and the rest in small letter.

Example: Before edit:

Name oNe
cOUNTRY
VaLue
value

After edit:

Name One
Country
Value
Value

I want to know two things: ->easiest way to edit a value inside a mySQL table ->easiest PHP way to capitalize the first letter of every word (space separated) inside a script

Thanks!

CristiC · Accepted Answer · 2011-05-25 11:34:22Z

3

You can use this function:

DELIMITER $$

DROP FUNCTION IF EXISTS `ICap`$$

CREATE FUNCTION `ICap`(mystring varchar(1000))
    RETURNS VARCHAR(1000)
BEGIN

DECLARE i INT DEFAULT 1;
DECLARE myc, pc CHAR(1);
DECLARE myoutstring VARCHAR(1000) DEFAULT LOWER(mystring);

WHILE i <= CHAR_LENGTH(mystring) DO
    SET myc = SUBSTRING(mystring, i, 1);
    SET pc = CASE WHEN i = 1 THEN ' ' ELSE SUBSTRING(mystring, i - 1, 1) END;
    IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN
        SET myoutstring = INSERT(myoutstring, i, 1, UPPER(myc));
    END IF;
    SET i = i + 1;
END WHILE;

RETURN myoutstring;

END$$

DELIMITER ;

After that, you can retrieve results like this:

SELECT 
  ICap(name)
FROM
  MyTable

or just update all the rows:

UPDATE 
  MyTable
SET 
  name = ICap(name)

answered May 25, 2011 at 11:34

CristiC

22.7k13 gold badges62 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

CristiC Over a year ago

@Midas: NO, it is just MySQL. You don't need php for your needs. You can achive this only in MySQL.

Midas Over a year ago

And then how do you include this in PHP?

XCS Over a year ago

Actually, I have a problem... In the names there were special characters: ş , â , ă , etc... and they were replaced with ��

XCS Over a year ago

@Midas, you don't include that in PHP, you simply run that using SQL command in phpmyadmin.

Midas Over a year ago

But in PHP this is impossible?

|

NA. · Accepted Answer · 2011-05-25 11:36:43Z

3

To fix the database with only one sql statement use this:

UPDATE country_table SET country_name = CONCAT(UCASE(MID(country_name,1,1)),MID(LOWER(country_name),2));

This will uppercase the first letter of each country name and lowercase the rest. All without needing any PHP at all.

answered May 25, 2011 at 11:36

NA.

6,59910 gold badges38 silver badges36 bronze badges

1 Comment

XCS Over a year ago

This only capitalizes only the first letter of the name, not each first letter, but thanks!

Brice Favre · Accepted Answer · 2011-05-25 11:33:26Z

1

->The easiest way to edit a value inside a mySQL table

 UPDATE TABLE SET COL="NEW VALUE" where COL="OLD VALUE";

Be careful, it will replace all line wher COL is equal to OLd VALUE

->easiest PHP way to capitalize the first letter of every word (space separated) inside a script

There's a function for that : http://php.net/manual/en/function.ucwords.php

answered May 25, 2011 at 11:33

Brice Favre

1,5271 gold badge17 silver badges36 bronze badges

Comments

Johann du Toit · Accepted Answer · 2011-05-25 11:34:49Z

1

To Edit the values in in the database just run a Update Query (I don't know how your table is designed) from php -- http://www.w3schools.com/php/php_mysql_intro.asp.

To Capitalize the first character of every word you can use ucwords.

answered May 25, 2011 at 11:34

Johann du Toit

2,6672 gold badges17 silver badges31 bronze badges

Comments

Stephane Gosselin · Accepted Answer · 2011-05-25 11:54:40Z

You can do it all in mysql if you want:

SELECT CONCAT(UPPER(SUBSTRING(CountryName, 1, 1)), LOWER(SUBSTRING(CountryName FROM 2))) from countries AS properCountryname;

The resultset returned would be your CountryName field with uppercased first letter.

You coud even do the update in one sql query, if you wanted.

If you really want php to do the job, something like this:

<?php
set_time_limit(0);
$cnt=0;
$con=mysql_connect('localhost','root','');
mysql_select_db('fakeDB',$con);
$query="SELECT `name`,id FROM countries";
$result=mysql_query($query);

while ($row=mysql_fetch_array($result))
{
  $name= $row["name"];
  $key = $row["id"];
  $field = strtolower($name);
  $array=explode(" ",$name);

  for ($i=0;$i<count($array);$i++)
  {
    $array[$i] = ucfirst($array[$i]);
  }
  $field = implode(" ",$array);
  $query2 = "UPDATE countries SET `name`='$name' where id=$key";
  mysql_query($query2);
  $cnt++;
  }
  echo $cnt." Records updated";
?>

Just a quick untested thingie but it should give you an idea.

Collectives™ on Stack Overflow

MySQL edit values

5 Answers 5

8 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related