Calling SQL wizards - Proper Capitalization

[greasemonkey]

Hey all, it would seem I've got in a bad habit of capitalizing my titles.... and as I try and set up my first a Google Merchant campaign, it's causing me all sorts of grief.

 

So, I've found a couple of examples around the web to change to the capitalization "proper" format;

 

MY GREAT WIDGET

 

To;

 

My Great Widget

 

And have written a little script.... But I'm having an issue with special characters.

 

The script fails on, for example, apostrophe.

 

IT'S MY GREAT WIDGET.

 

How can I build exceptions for special characters? Is there an easier way to do this?

<?php

  require('includes/configure.php');
  require('includes/functions/database.php');
  
tep_db_connect() or die('Unable to connect to database server!');

$convertir = tep_db_query("select * from products_description");
if ($row = tep_db_fetch_array($convertir))
{ 
 	do 
  	{ 
		$str = $row["products_seo_title"];
		$str = mb_convert_encoding(mb_convert_case($str, MB_CASE_TITLE), "UTF-8"); 
		$str = utf8_decode($str);
 		$sql = "UPDATE products_description SET products_seo_title = '" . $str . "' WHERE products_id = '" . $row["products_id"] . "'";

		tep_db_query ( $sql ); 
     }
  	while ($row = tep_db_fetch_array($convertir)); 
}
?>

[♥raiwa]

Hello Scott @@greasemonkey,

 

you may find some inspiration in the sloppy words cleaner:

http://addons.oscommerce.com/info/5550

 

regards

Rainer

[greasemonkey]

@@raiwa just what I was looking for...

[MrPhil]

If you're using ' in your code or query to delimit strings, you would have to find and escape any ' in your data:

 

$str = $row["products_seo_title"];
$str = preg_replace("'", "\\'", $str);

or something like that (I haven't tried it out).

[greasemonkey]

@@MrPhil thank you kindly Phil.

 

The ' is not in the code its in the table/column description

 

 

IT'S A BOY GIRL

 

gets returned like

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's A Boy/girl' WHERE products_id = '593'' at line 1

UPDATE products_description SET products_seo_title = 'It's A Boy/girl' WHERE products_id = '593'

Does that make sense? How can I ignore the 's? In the description?

 

In the sloppy words cleaner @@raiwa has built a function to catch 's as a $suffixes upon entry into the DB (at create_account.php as an example) with

    $street_address = RemoveShouting($street_address);

However, I'm trying to clean data already in the DB.

 

Rainer's function looks like;

function RemoveShouting($str, $is_name=false) {
	
   // exceptions to standard case conversion
   if ($is_name) {
       $all_uppercase = '';
       $all_lowercase = 'Or|And';
   } else {
   	 // address abreviations and anything else
       $all_uppercase = 'Aly|Anx|Apt|Ave|Bch|Blvd|Bldg|Bsmt|Byu|Ch|Cors|Cswy|Cr|Crk|Crt|Cts|Cv|Cvs|Est|Ests|Expy|Frnt|Fl|Frks|Fwy|Gdn|Gtwy|Hbr|Hbrs|Hts|Hwy|Ii|Iii|Iv|Jct|Jcts|Lk|Lks|Ln|Ldg|Mnt|Mnr|Mnrs|Msn|Mtwy|Mtn|Mtns|Ne|Nw|Pkwy|Pl|Pln|Plns|Ph|Po|Pob|P.o.b.|P.O.b.|p.O.b.|p.o.B.|p.O.B.|Rm|Rr|Se|Skwy|Smt|Sw|Sta|Ste|Sq|Ter|Tpke|Trpk|Trlr|Trl|Trwy|Vl|Vlg|Vlgs|Vly|Vlys|Vi|Vii|Viii|Xi|Xing|Xrd';
       $all_lowercase = 'A|And|As|By|In|Of|Or|To|Y|En|De|La|Del|Du|Am|An|Der|Die|Das|Von';
   }
   
   $prefixes = '';
   $suffixes = "'S";
   
   // captialize all first letters
   $str = mb_convert_case($str, MB_CASE_TITLE, CHARSET);

   if ($all_uppercase) {
   	 // capitalize acronymns and initialisms i.e. PO
       $str = preg_replace_callback(
   	 		   "/\\b($all_uppercase)\\b/",
           function ($m) {
             return mb_convert_case($m[1], MB_CASE_UPPER, CHARSET);
           },
           $str
           );
   }
   
   if ($all_lowercase) {
   	 // decapitalize short words i.e. and
       if ($is_name) {
       	 // all occurences will be changed to lowercase
           $str = preg_replace_callback(
           	 	"/\\b($all_lowercase)\\b/",
           	 	function ($m) {
           	 		return mb_convert_case($m[1], MB_CASE_LOWER, CHARSET);
           	 	},
           	 	$str
           	 	);   
       } else {
       	 // first and last word will not be changed to lower case (i.e. titles)
           $str = preg_replace_callback(
           	 	"/(?<=\\W)($all_lowercase)(?=\\W)/",
           	 	function ($m) {
           	 		return mb_convert_case($m[1], MB_CASE_LOWER, CHARSET);
           	 	},
           	 	$str
           	 	);   
       }
   }
   
   if ($prefixes) {
   	 // capitalize letter after certain name prefixes i.e. 'Mc'
       $str = preg_replace_callback(
          "/\\b($prefixes)(\\w)/",
          function ($m) {
          	return $m[1] . mb_convert_case($m[2], MB_CASE_UPPER, CHARSET);
          },
          $str
          );   
   }

Maybe it would be easier for me to just use his function - I'm just not sure how to call the function on data already in a table?

 

Any hints?

[MrPhil]

If there is a ' in the table's text, you end up with SET products_seo_title = 'It's A Boy/girl' WHERE... See the 'It's A...? You had an extra ' in there (the original apostrophe) that ends the SQL expression's string early, and MySQL has no idea what s A Boy/girl means. That's what it's complaining about. So, you need to fix up that $str first to escape the ' before feeding it to MySQL. You end up with 'It\'s a Boy/girl'.

 

A minor omission in my code: the first string is a regular expression, so it should be "/'/" and not "'".

[greasemonkey]

@@MrPhil that works.... thank you once again.

 

I've taken @@raiwa sloppy words exceptions functions and added some of those, and added other columns that also should be cleaned (see below).... also, I had some issues with french accents....

 

My Frénch Title

 

Was changing to;

 

My Fr

 

So I've included application_top

  include('includes/application_top.php');

in place of connecting directly to the DB with 

require('includes/configure.php'); 
require('includes/functions/database.php'); 

So I could use CHARSET

		$str = mb_convert_case($str, MB_CASE_TITLE, CHARSET); 

In place of UTF-8

		$str = mb_convert_encoding(mb_convert_case($str, MB_CASE_TITLE), "UTF-8"); 
		$str = utf8_decode($str);

I will continue to test and inspect the results.....

 

Only question i have is if;

tep_db_connect() or die('Unable to connect to database server!');

Is required? To tell I'm not sure I understand its purpose.....

 

WARNING TO ANYONE COPY & PASTING THIS CODE.... THIS CODE WILL WRITE OVER CERTAIN FIELDS DATABASE!!!!! CONSIDER THIS EXPERIMENTAL!!!!!!

  include('includes/application_top.php');
  
tep_db_connect() or die('Unable to connect to database server!');

//add uppercase exceptions to change back to uppers
$all_uppercase = 'Jem|Pme|Fmm';
//add lowercase exceptions to change back to lowers
$all_lowercase = 'A|And|As|By|In|Of|Or|To|The|Pkg';
	   
$convertir = tep_db_query("select * from products_description");
if ($row = tep_db_fetch_array($convertir))
{ 
 	do 
  	{ 
        $str = $row["products_name"];
        $str = preg_replace("/'/", "\\'", $str);
		$str = mb_convert_case($str, MB_CASE_TITLE, CHARSET); 
		//change upper exceptions 
		$str = preg_replace_callback("/\\b($all_uppercase)\\b/", 
	              function ($m) {
             return mb_convert_case($m[1], MB_CASE_UPPER, CHARSET);
           },
           $str
           );
		// change lower exceptions   
        $str = preg_replace_callback("/(?<=\\W)($all_lowercase)(?=\\W)/",
           	 	function ($m) {
           	 		return mb_convert_case($m[1], MB_CASE_LOWER, CHARSET);
           	 	},
           	 	$str
           	 	);   		   
		  
 		$sql = "UPDATE products_description SET products_name = '" . $str . "' ,products_seo_title = '" . $str . "', products_seo_keywords = '" . $str . "'  WHERE products_id = '" . $row["products_id"] . "'";
		tep_db_query ( $sql ); 
     }
  	while ($row = tep_db_fetch_array($convertir)); 
}

[MrPhil]

A "connect" is necessary for using your database. I'm not sure I understand what your question is about.

[♥kymation]

The use of tep_db_connect() here is not needed as application_top.php already does that.

 

Regards

Jim