How to create a WordPress compatible slug in MySQL

Sometimes it is convenient to import terms or posts into a WordPress database.  When you do, you may need to create a ‘slug’ column. To do this, I’ve modified a user defined function I found on StackOverflow or some similar site (possibly here: ).  That version had some bugs. So I fixed them.

Here is my final script to create the UDF slugify.

Usage: select slugify(name), name from externaldata;


CREATE DEFINER=`root`@`localhost` FUNCTION `slugify`(`dirty_string` VARCHAR(200)) RETURNS varchar(200) CHARSET latin1
DECLARE x, y , z Int;
Declare temp_string, allowed_chars, new_string VarChar(200);
Declare is_allowed Bool;
Declare c, check_char VarChar(1);

set allowed_chars = “abcdefghijklmnopqrstuvwxyz0123456789-“;
set temp_string = lower(dirty_string);

Select temp_string Regexp(‘&’) Into x;
If x = 1 Then
Set temp_string = replace(temp_string, ‘&’, ‘ and ‘);
End If;

Select temp_string Regexp(‘[^a-z0-9]+’) into x;
If x = 1 then
set z = 1;
While z <= Char_length(temp_string) Do
Set c = Substring(temp_string, z, 1);
Set is_allowed = False;
Set y = 1;
Inner_Check: While y <= Char_length(allowed_chars) Do
If (strCmp(ascii(Substring(allowed_chars,y,1)), Ascii(c)) = 0) Then
Set is_allowed = True;
Leave Inner_Check;
End If;
Set y = y + 1;
End While;
If is_allowed = False Then
Set temp_string = Replace(temp_string, c, ‘-‘);
End If;

set z = z + 1;
End While;
End If;

Select temp_string Regexp(“^-|-$|'”) into x;
If x = 1 Then
Set temp_string = Replace(temp_string, “‘”, ”);
Set z = Char_length(temp_string);
Set y = Char_length(temp_string);
Dash_check: While z > 1 Do
If Strcmp(SubString(temp_string, -1, 1), ‘-‘) = 0 Then
Set temp_string = Substring(temp_string,1, y-1);
Set y = y – 1;
Leave Dash_check;
End If;
Set z = z – 1;
End While;
End If;

Select temp_string Regexp(“–“) into x;
If x = 1 Then
Set temp_string = Replace(temp_string, “–“, “-“);
End If;
Until x <> 1 End Repeat;

Return temp_string;

How to quickly update WHMCS on WiredTree hosting and others.

NOTE: This script has been updated to remove the premature deletion of the install directory.

Over the last month or two, WHMCS has been updated many times. This is a GOOD THING, but it can be annoying to go through the normal update process every week or two.

So, I created a small function that I’ve added to /etc/bashrc on my server.

It does the following:
1. Verifies the script is running from the right server.
2. Changes directories to the location where I have WHMCS installed.
3. Removes any files and folders from a previous update
4. Downloads the latest update from the password protected WiredTree repository
5. Unpacks it
6. Renames the admin directory to a new name for security purposes (you may need to do the cron dir too)
7. Changes the owner and group of the new files to be the one required for this website.
8. Copies in all of the new files, overwriting what is there.

So updates now take a few seconds rather than 20 distracting minutes. Maybe you will find this useful.

Here is the function I added to /etc/bashrc:


function upwhmcs(){
  curhostname=$(hostname -s)

  if [ "${curhostname}" == "$desiredhost" ]; then
    rm -Rf whmcs
    rm -f
    unzip -o
    cd whmcs
    cp -Rf * ../www
    cd ../www
  echo "Wrong Server. Update skipped"


To use that listing you need to replace the following text
REPLACE_WITH_HOSTNAME – Replace this with what you get when you run ‘hostname -s’ from the command line.
REPLACE_WITH_DIRNAME – Replace this text with the directory of your whmcs installation. Maybe it is /home/whmcs/ or /home/hosting/ or /home/sales/
REPLACE_WITH_USERNAME – This should become the username that WiredTree gave you to login and get WHMCS updates
REPLACE_WITH_PASSWORD – This should become the password that WiredTree gave you to login and get WHMCS updates
REPLACE_WITH_ADMIN_DIR_NAME – This is the custom name of the admin directory on your server. If you just use admin, delete this mv line entirely.
REPLACE_WITH_DIROWNER – This is quite likely the same value as REPLACE_WITH_DIRNAME

This script assumes that you have WHMCS installed as its own website/subdomain. You can probably adjust it to your needs if you run it in a sub-directory. If you make this customization, send it back to me and I’ll add it to the article.

After the upgrade process, you must visit the site, follow the update steps and then go back and delete the “install” directory from the installation.  That should do it!

How To: List the lines that exist in one file and not another in Linux or Windows Command Line

This solution uses grep.

Grep is a search tool that exists by default in just about every Linux installation. You can also search Google for Windows Grep.

for a Demo, Here is FileA.txt

1. In Both
2. In Both
3. In Both
4. In File A Only
5. In Both
0. Out of Order In Both
-1.  Out of Order In A Only
In A only

Here is FileB.txt

1. In Both
2. In Both
3. In Both
4. In File B Only
5. In Both
0. Out of Order In Both
-1.  Out of Order In B Only
In B only

To show lines that only exist in File B and not in A. Do this:

# grep -v -xFf FileA.txt FileB.txt

4. In File B Only
-1.  Out of Order In B Only
In B only

To show lines that only exist in File A and not in B. Do this:

# grep -v -xFf FileB.txt FileA.txt

4. In File A Only
-1.  Out of Order In A Only
In A only


In windows, Grep you can drop the capital F from the command line. But it really shouldn’t matter.

The command line arguments are:

-v = Reverse the compare so you get what doesn’t match instead of what does

-x = Compare entire lines at a time instead of characters

-F = Do not parse the contents of each line for any mid-line regex commands

-f = Compare the specified files


Hope that helps someone!

HOW TO: In PHP the MySQL Client API version doesn’t match the MySQL Server version

This is a fairly common situation. The short answer is that you usually don’t need to fix anything. This is a non-issue.  As long as your MySQL and MySQL Client have the same major version, you can and probably should just ignore the issue. There are no compatibility or performance issues involved.

This situation occurs when you use a package installer such as Yum to install a previously compiled version of PHP rather than compiling the version locally yourself. The package creator will have compiled PHP with a version the MySQL client that is compatible with that major release of MySQL.

To quote a 2008 article from the IUS Community project:

That said, I can tell you that in the last 4+ years of providing packages in this ‘mixed’ kind of fashion… we’ve never really come across any major issues of using php built against an older version of mysql… talking to a new version of mysql.

Now if you had to fix this, you could check to see if there is a specially compiled version of PHP with your particular MySQL client installed, but that’s unlikely.  You’re more than likely going to need to recompile php from your command line.  That slightly mismatched version is just the price you pay for convenience.

How do you delete all tables in a database in MySQL?

Sometimes you just have to start over.  I had a database that had a bunch of tables in it, almost 9000. The problem was that I didn’t know if they were the right tables.  I had a mysqldump files that did have the right tables, but I didn’t want any extras around. So I wanted to start over with the fresh import.  In MySQL you would normally just do a drop database x; create database x; and you are done.  However, I didn’t have a user with the rights to create a database.

With a small database you can do a command line like

SELECT group_concat(table_name order by table_name desc separator ‘ ‘) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ‘DATABASENAME’ ;

and then type drop table  and paste in the resulting table list.  This does not work with anything more than a dozen or so tables. This question gets to be very tricky when you are dealing with a LARGE database.

I found an article from back in 2006 that had a rather unique idea and I’ve expanded it to be faster and to work better with extremely large database.  You will still have to wait a bit for completion, but when you need to remove a large number of tables  from a database, try this command line:

mysqldump -u[USERNAME] -p[PASSWORD] [DATABASE]–add-drop-table –no-data –skip-lock-tables | grep ^DROP | mysql -A -u[USERNAME] -p[PASSWORD] [DATABASE]

MySQL dump will create a list of drop commands for every table in the database, the grep will isolate those and feed them back into mysql for execution.  It’s a rather slick solution and while you may still have to wait for 5 minutes (or even much more) as all the tables are listed and dropped, it will get the job done.


Find what week of the month a date is in PHP


The need for this routine comes up every now and then and it confuses people because they thing date(“W”) will do the trick.

Once they realize that’s the week of the year, they start to get all elaborate in finding ways to get which week in the month it is.

Applying a little logic, you can come up with the simple solution of subtracting away all of the weeks before the start of the month an the remainder is your answer


Like so:

$weekNum = date(“W”) – date(“W”, strtotime(date(“Y-m-01”, now()))) + 1;

(Drop the + 1 if you want it to be zero based.

HOW TO: Installing MySQL on RedHat/CentOS/Amazon Linux in 5 easy steps.

This seems like it should be a really complicated tasks, but once you have done a few it sinks in that this is really simple…


Just run each of the following 5 steps as root.  Answer any questions with the common sense response, and you are done

yum groupinstall “MySQL Database”
/usr/bin/mysql_install_db –user=mysql
service mysqld start
chkconfig mysqld on


Step 1 uses the groupinstall  command to install all of the most common mysql daemons.  This one line eliminates perhaps half a dozen steps.

Step 2 does the initial DB configuration for the software.  You can select the defaults for all most all options, but when it asks about security, say no because we are gonna do it in the next two lines.

Step 3 starts the MySQL server for the first time

Step 4 when run with all the defaults secures your server nicely.

Step 5 is one that lots of  the tutorials miss.  When you reboot your DB server, it would be nice if MySQL ran right? Well this line does that.


If you want to change your server configuration from the default, run this line:

nano /etc/my.cnf

After saving, if you are done restart the MySQL daemon:

service mysqld start

Simple as that, you are done… Any Questions?