Replacing Unwanted Characters in MySQL Field

I recently ran into an issue where and automated import wasn’t clearing newline characters before inserting entries into my database. This resulted in thousands of entries in the database containing unwanted newlines in certain fields. To resolve this issue I could have looped through all the entries with a PHP script, but this was a one-time fix (I corrected the issue in the import so it won’t happen again) and I didn’t want to write what would be a resource-consuming looping script in PHP.

What’s the solution, then? Using MySQL’s built-in REPLACE() function. It works like this:

UPDATE table SET field = REPLACE(field, “\n”, “”) WHERE id > “10201”;

Simple but effective. The REPLACE() function takes three parameters:

  1. The field to be searched.
  2. The string to be replaced.
  3. The replacement string.

In my case, I’m searching for the string “\n” in the field “field” and I’m replacing it with “” (nothing). I’m also restricting the replacements to only those rows with an id greater than 10201 since that’s where the import began to experience it’s issue. Why make MySQL do more work than it has to?

So, that’s the solution to this issue.

Restarting a MySQL Server from the Command Line

While it doesn’t come up frequently, every once in a while you’ll need to restart a MySQL server from the command line. On my current webserver we’re running a VPS with WHM. What this means is that accessing the MySQL daemon is not as simple as using “mysqld” from the shell. So here’s how to get it to work, based on information found at this blog post, and my own experience:

#/etc/init.d/mysql start

#/etc/init.d/mysql stop

#/etc/init.d/mysql restart

That will start, stop, and restart, respectively, the MySQL server on our WHM server.

Magento 1.7: The Beginning

In spinning up a new store for I decided to go with Magento (the free community edition) because it seems to be an extremely flexible base to build on from a design perspective. I have worked with Zen Cart for about 6 years and enjoyed the speed at which you could spin up a store (you could have a fully running and functional store within 6 hours, if you had a payment processor ready to go) but have been frustrated by difficulties in setting up custom templates or adding functionality because either of these activities seemed to require (sometimes extensive) modifications of core files. Version 2.0 of Zen Cart has been promised for a few years and seems like it would address those issues. However, I wanted to try out a new platform so I chose Magento.

Installing Magento was easy enough, I need a database, a user for that database, and needed to allow write access to a few folders during the installation. It took about 15 minutes all told. This is where I diverged from the simple path, however, since I didn’t want our site to look like very other site out there. This meant creating a new design package and theme. In Zen Cart this was easy (if my theme doesn’t have a needed file, the default is used). In Magento the process took over half an hour and I ended up with absolutely no styling. Since my goal was to be able to style the site myself from the ground up, I celebrated!

I used this guide to walk through the basics, and I’ll post later on how easy it was to add my own styles and functionality. However, at the moment, I have an non-functioning but entirely unstyled site to work with. We’ll see how it goes from here!

Splitting a String in MySQL

Sometimes you’ll end up working with a MySQL field that contains multiple pieces of information. This can be a delimited list, or, in my case, nested categories. I’ve been using a single field to contain information on the genre of a book which is often a subcategory of a larger genre. For example, an Historical Romantic Fiction book would have the genre “Fiction/Romance/Historical” in my genre field. When giving our employees a list of genres to select when adding product information to a book, I don’t want to just give them all 3,000 sub-genres, instead I want to give them level-by-level genres to choose from. i.e. “Select from Nonfiction or Fiction” and if they select Fiction: “Select from Romance, Fantasy, or Classic” and if they select Romance: “Select from Historical or Contemporary” etc.

In doing so I need to pull out, level by level, the existing genres. To do this for the first level I use the following query:

SELECT SUBSTRING_INDEX(genre,”/”,1), COUNT(eAN) FROM `inventory_product_info` GROUP BY¬†SUBSTRING_INDEX(genre,”/”,1) ORDER BY COUNT(eAN) DESC

I’m using the SUBSTRING_INDEX() MySQL function to get a substring from the beginning of a string (in this case the contents of the genre field) until the 1st occurrence of the “/” character.¬†I’m both returning the results of the SUBSTRING_INDEX() and grouping by it to get a count of all products which use that top-level genre.