Search and Replace with MySQL

Every once in a while, you might find that you need to find a string or value in your MySQL database and replace it with a new value throughout. For instance, let’s say you decide to move your MySQL-based Web site from one domain to another, and now all of the links you had stored in the database are broken, because they still point to your old domain.

MySQL offers an extremely simple solution to this problem. Here is a code snippet that shows you how to do so:

Back Up A Large MySQL Database

Anyone that’s dealt with a Web application using a MySQL backend has probably dealt with incomplete backups. Those of you that use phpBB have almost certainly encountered this issue. Unfortunately, within phpBB and quite a few other PHP/MySQL-based applications, the backup scripts fail miserably with any reasonably large amount of data. Most of them fail because of either PHP time outs or file size limits. This article will show you two different ways to successfully back up that data. At least one of these two methods should be available in almost every remote hosting package that uses MySQL and PHP.

XML Vs. Databases

I recently had occasion to consider whether to use XML files or a traditional database when constructing a Web-based application at work. It took a lot of careful consideration and research to decide which way I was going to go.

In the end, I chose to use XML files, and I will explain why.

Quick warning about MS Access data types

The other day, I discovered a minor inconvenience in the way MS Access handles the “Yes/No” (or boolean) data type.

You should be aware, when working with MS Access, that any “Yes/No” field in your table can only accept boolean true and boolean false (or “true” and “false”, “yes” and “no”, 1 and 0, etc.). It will not accept NULL.

Standard programming practice is, when there is not a “default” value for a field, to insert NULL if the user does not explicitly declare a value. Unfortunately, the fact that Access throws an error if you try to insert NULL into a “Yes/No” field really puts a damper on that logic.

As the title says, this is just a quick warning for people that may be using MS Access for their databases. I’m not sure if the same is true for MS SQL or not, but it’s obviously a non-factor with MySQL, since MySQL doesn’t offer a boolean data type.

Sorting database results

Databases offer a lot of flexibility when dealing with data. By spending some time upfront on the database design, it will save you plenty of headaches later on. Since the object of this to tutorial is not to elaborate on the advantages of using a database, I will skip to its limits.

The operations you can run on a database are always based on logic and numbers. So for example, when you run a store and sell T-Shirts, and you sort by size, “M” will always be sorted before “S”, even though it is not correct from a humanistic point of view.

Assuming we have a table called “Products” in the database, here is a set of data that you would typically deal with and a solution for sorting the data in a more useful way using the MySQL database.

Name    | Size

T-Shirt | L

T-Shirt | M

T-Shirt | S

T-Shirt | XL

T-Shirt | XS

And here is how you typically retrieve records:

SELECT Name, Size FROM Products ORDER BY Size

This query returns all products, however, not sorted “XS, S, M, L, XL” but in “L, M, S, XL, XS”.

The solution to this problem is a simple CASE (see: Control Flow Functions):

SELECT Name, Size,

CASE Size

WHEN XS THEN 1

WHEN S THEN 2

WHEN M THEN 3

WHEN L THEN 4

WHEN XL THEN 5

END

AS Sort_Order

FROM Products

ORDER BY Sort_Order ASC

This returns the products in order of their sizes. Use ASC (ascending) or DESC (descening) to change the order from smallest size to largest size or vice-versa.

That’s it, enjoy.