Find and Replace a letter/number and batch update with MySQL

I had the need to do a batch update of a large MySQL data table that held thousands of products and their information, including SKU numbers. Recently the company has changed how they manage the SKU numbers and needed to replace the leading ‘2’ with a ‘4’ for simple accounting tracking.

It was easy enough to use REGEX to find which ones started with ‘2’, but I was still unsure of how to replace the ‘2’ with a ‘4’ in all of the necessary records.

I knew that if the SKU started with a ‘2’, that it had to be changed to a ‘4’ so I started out using the IF() function to compare SKU’s as follows.

SELECT sku, IF(TRIM(LEADING '2' FROM sku) <> SUBSTRING(sku FROM 2),sku,INSERT(TRIM(LEADING '2' FROM sku),1,0,'4')
)
FROM product

So what this does is select all the SKU’s in the table and return the ones that need to be changed with what the change would look like. The first TRIM() function takes all of the SKUs and trims off the first 2, then it is compared to a substring of the original SKU starting at the second position. If there is no leading ‘2’ in the SKU, then it will not match the substring since nothing was trimmed.

The next argument is telling the function what to return if the TRIM() and SUBSTRING() values differ. It will return the original SKU since we don’t need to change it if it doesn’t start with ‘2’.

The last argument is telling it what to return if TRIM and SUBSTRING are equal. For this value I’m trimming the leading ‘2’ and INSERTing a ‘4’ at the first position of the SKU.

The above query lists the original SKU and what it should look like once I put it into an UPDATE query.

The UPDATE doesn’t look much different:

UPDATE product
SET sku =
IF(
  TRIM(LEADING '2' FROM sku) <> SUBSTRING(sku FROM 2),sku,INSERT(TRIM(LEADING '2' FROM sku),1,0,'4')
)

This actually updates the table with the changes that I wanted. There may have been a more elegant way to do this with REGEXP but this did the trick. I verified that only the SKUs with a leading 2 were changed to a leading 4 and the others were left as they were.

Ubuntu 10.04 LTS: First Thoughts

I have Ubuntu installed on my laptop as a dual-boot and on my work PC inside a VirtualBox. I use it for all kinds of things that Ubuntu just does better – especially heavy development.

After yesterday’s public release of Ubuntu 10.04 LTS I decided it was time to get my hands dirty and install the new version.

A few things stuck out during the first boot and installation process:

  • Slick Graphical Interface during installation (see screenshot)
    4-30-2010 1-39-49 PM
  • More emphasis on making the user aware of features: sliding panels explaining features and benefits during installation just like Windows OS install process
  • Seems more readily geared towards a wider user base – features like music store, built-in social networking and instant messaging platforms
  • It boots up in seriously just a few seconds. Really great benefit.
  • Default theme and desktop configuration are attractive…and surprisingly not orange/brown!4-30-2010 1-53-32 PM
  • Tweaked window layout: note the ‘close’, ‘minimize’ and ‘maximize’ buttons in the top left
    4-30-2010 1-56-47 PM
  • The feel of the OS seems much more familiar than it used to. I’m impressed and I think this is a great leap towards a wider user-base.

Well, those are some first-impressions. I’m really excited about the level of polish that has been reached with Ubuntu 10.04. Props to everyone involved in the project.

Dynamically Sync Your Files With Amazon S3

I’ve been getting used to using the cloud over the past week or so and I’ve got to say it’s much easier to manage than I had imagined. I know there are tons of great desktop clients to manage your S3 buckets and files, but I wanted something to do all of the management for me – to make things simple.

I’ve put together a script using Undesigned’s awesome PHP S3 class that will scan a directory, compare all of the files in the directory to those in a given directory on your bucket and then upload missing files or replaced unmatched files.

You could set this PHP script up to do a lot more than it does currently, as it was set up to be a quick and dirty solution. Here’s the basis of it. You can download the full script and classes below.

//check to see if the file exists and can return file info
if (($info = $s3->getObjectInfo($bucket, $uri)) !== false) {
//if the local hash doesnt match the remote hash, upload and replace the file
if($info['hash'] <> $local_md5){
print_r('Can\'t match file: '.$the_file.' -- Now uploading');
echo "Putting $the_file . . . at $uri ";
//this will upload files with private permissions - you need to set the permissions as
//needed by changing S3::PRIVATE to whichever level you need.
if($s3->putObject(S3::inputFile($object), $bucket, $uri,  S3::ACL_PRIVATE,
array(),
array( // Custom $requestHeaders
"Cache-Control" => "max-age=315360000",
"Expires" => gmdate("D, d M Y H:i:s T", strtotime("+5 years"))
)))
echo "OK\n";
else
echo "ERROR!\n";
}
else{
echo "The hash values for $the_file are equal ";
}
}else{
//file doesn't exist in your bucket, so upload it
print_r('Can\'t find that file: '.$the_file.' -- Now uploading');
echo "Putting $the_file . . . at $uri ";
if($s3->putObject(S3::inputFile($object), $bucket, $uri,  S3::ACL_PUBLIC_READ,
array(),
array( // Custom $requestHeaders
"Cache-Control" => "max-age=315360000",
"Expires" => gmdate("D, d M Y H:i:s T", strtotime("+5 years"))
)))
echo "OK\n";
else
echo "ERROR!\n";
}
Get the Class Get the sync script

Google gets a facelift

Google search results It looks like Google has finally come up with a search results page to go along with their revised home page with fade-in effect. The UI is a little bit different, most notably the filters along the left sidebar, as you can see in the screenshot. Coincidently the first search result on that page is for Bing.

I’m a big fan of the filters they provide. The time sensitive filter is probably something I’ll use a lot, especially when researching anything programming related. I can filter to make sure the information is current and still applicable.

google search results with screenshot filterYou can also sort results along with a screenshot of the page itself. I really like this feature for the simple fact that you can get a quick glance of a page design and main content elements without diving all the way in.

Overall it’s a big move for what everyone across the net is used to seeing. At the same time it’s still pretty vanilla – but useful in all the right ways. I’m curious to see if a lot of people even notice.

Head in the clouds: Amazon s3 and CloudFront with Joomla! and PHP

I’m managing a LAMP server on a virtual dedicated setup and have recently run into some performance issues. During periods of higher user visits the CPU usage shoots up This could be partly because of the platform running on it, but I’m not sure that the PHP scripting is the root cause.

To alleviate some of my concerns, I’ve moved most of the static images, css, and javascript over to Amazon s3 and set up a CDN for that data with CloudFront. It’s fast. Really fast compared to what we’re used to. It has drastically helped load times and from what I can tell it is improving the performance of the site altogether by reducing the load on the server.

Caching is out in full force for the main joomla site while the content being served from amazon s3 is gzipped, minified, and speedy. I’m still working through YSlow to get things even more streamlined, but I’m more satisfied now than before.

I was using a minify component to grab scripts on-the-fly and combine them but that’s just a nightmare for performance so I’m going to revamp and get the remainder of those files out to the CDN and compressed.

For those who are interested, I’m serving gzipped js and css but using some varied methods also depending on if the connection is secure or not. Since S3 does not function over https using cnames, I had to set up a check to see which URL to use for my CDN files.

Something like this did the trick for my Joomla! template (placed in the template’s index.php):

//check to see if the browser supports gzip content
$gz = strpos($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip') !== false ? 'gz' : '';
if ($_SERVER['HTTPS'] != "on") {
//insert your code to execute over http here
$document->addStyleSheet('https://yourbucketname.s3.amazonaws.com/templates/css/style.'.$gz.'css');
}
else{
//enter your https code here
$document->addStyleSheet('http://cdn.yoursite.com/templates/css/style.'.$gz.'css');
}

I’ve got this loading the CNAME urls such as cdn.yourdomain.com/css/style.gzcss for the non-secure connection but using the secure URLS that amazon provides for the secure connection.

And no, style.gzcss is not a typo. There were issues with how the browser was grabbing files with the regular style.gz.css extension, even when I had set the content-encoding to gzip and the content-type to text/css. This has worked for me, but there may be a more elegant solution floating around. Cloudberry explorer has been a great asset in doing this quickly and efficiently.

The Morning After

It looks like a lot of iPad hopefuls have been let down. The high expectations for the device have led people like Jeff Jarvis, an admitted Apple fan-boy and well-known media pundit, to send the device back. Jarvis even recorded the reboxing process and even threatened to “Jimi Hendrix it on stage”.

I’m sure a lot of other people will be doing the same. It’s a novelty device for now – more of a proof of concept that someone had to produce. Wait until about four generations of the product from now until there is something really desirable. Everyone else just got some extra time to play catch-up. Or start a technology war.

Apples to Apple

During the pep rally for iPhone OS4 I was pondering how Apple has carefully crafted a dedicated culture of users, both with their computers and gadgets. It reminds me, although on a much less elaborate scale, of the method that Nintendo has used to enhance the Nintendo DS over the years and stimulate demand for new hardware and games.

I think there are at least 3 generations of Nintendo DS systems on the market now, compared with a half-dozen iPod generations and 3 going on 4 generations of the iPhone. Nintendo has made it a point to prevent the total alienation of users by maintaining backwards compatibility so users of older systems still have a similar experience to those with the newer gadgets. We all know this isn’t the case with Apple.

Once a new iPhone OS comes out, those with older handsets usually get left in the dust. They need to upgrade their OS if possible – or just fork over a fistful of Benjamins to get on the boat. Development of apps gets focused on making use of the new OS features further leaving users alienated.

Everyone is paying attention to what happens in the mobile device arena. Big things are happening and everyone wants a piece. Apple has a big piece for good reason, but now that a few other big boys are in the arena they may need to revisit their method of keeping a user base. It may not last now that people have comparable alternatives.

Integrating VirtueMart with Constant Contact

This week I’ve been tackling a project that has been in the back of my mind for a few months now. I was able to leverage the Constant Contact API to integrate Constant Contact user records with VirtueMart/Joomla user profiles. Now when a user is viewed from within the VirtueMart user manager you will also be able to see/edit their related constant contact record and list subscriptions.

New contacts can be created at the same time as Joomla users and it will auto fill information into the constant contact record based on the VirtueMart information. If an existing user profile that does not have a constant contact record is viewed, then when the profile is saved a new constant contact record will be created for that user. I’ll eventually be making more use of the API to allow users to select from various newsletters when registering so that they will be able to have more targeted communications.

The only issue I ran into was when I uploaded the changes to the live web server with PHP 5.2.11. It seems that on my local server with PHP 5.2.9 everything worked well but the comments in the constant contact class file were causing parse errors. I was able to get around the errors by deleting some of the comments.

Ordering VirtueMart Child Items

Edit: This is easily my most popular post and solicits a lot of questions. This method sorts children in a table on the parent product page. It DOES NOT sort children in a drop down list. 1/10/2011

UPDATE: New methods here: I am updating with another post that will try to address how to universally sort attribute/child drop down lists.

I’ve been pulling my hair out the past couple of hours trying to find a built-in way to order child items in VirtueMart. There are plenty of ways to sort built-in to the category view of multiple products, but there is no built in function to sort child products (items). The guidance on the VirtueMart forums was lacking and would produce errors when including external child ids to be included, so I had to do some digging.

Around line 488 in ps_product_attribute I was able to add the following code after the if($child_ids){} clause:

elseif(!$child_ids){
$q .=” ORDER BY product_id”;
}

Now my child items are sorted by ID. You could easily product_id to be any other field, but for my purposes this worked great.

I don’t like PEARS or mail today

One of the websites I work a lot on has files offered for download. When a user clicks to download it runs through a script and verifies the user’s access prior to sending the file to the browser, then it sends an email to a group of admins to notify them that the user has downloaded the file.

Everything was dandy until Friday. I noticed today that there have been no download notifications since then…

There was also another element of the site that wasn’t working and it just so happened to be another mailer script.

Both scripts use the PHP PEAR mail and mime includes.

Based on some preliminary troubleshooting it appears that the script will execute just fine as long as this line is commented out:

$mail_object->send($recipients, $headers, $message);

Which kind of defeats the purpose since it is responsible for sending the mail message. I still can’t find the problem, but I suspect it has to do with the mail host.

For now I am using the built-in Joomla! mailer, which for some reason is working just fine.