When working with MySQL databases it’s often necessary to convert a regular field into one that refers to a second table using a foreign key. The process for this requires several steps:

1. Insert distinct column values into new secondary table.
2. Replace (update) existing table fields with proper foreign key for the related record in the secondary table.

This process can be time consuming if attempted manually. Luckily, MySQL 4.1+ allows for subqueries as well as the INSERT… SELECT… syntax.
(more…)

At Trellis Development we’re working on several applications which need to frequently convert dates between various time zones.

By default, both PHP and MySQL operate using a single timezone which is identified during installation and is quite often the same as the host server. Both tools allow you to specify a different timezone on the fly, and all following date operations work based off of that locale.

The primary goal is to preserve a base standard time so that we can easily convert between different timezones, without loosing the ability to convert again. Two choices emerged – storing a Unix Time stamp or a date string stored using GMT/UTC time.
(more…)

Recently I was doing some local development on my Mac OS X Leopard machine. When running apache2 with php I was able to connect to the database just fine, and from the command line I was able to run mysql commands.

However, when running a php script from the command line I was getting an error:

Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)

After spending some time thinking it was the code, I discovered that the mysql.sock file was missing.

Others on the net found the file elsewhere, like /private/tmp/mysql.sock or /tmp/mysql.sock but I had absolutely no .sock file anywhere.

Turns out, it did exist, but was named differently. According to the php.ini, the file was located at

/usr/local/mysql/run/mysql_socket

So, the solution was to create a symlink in /var/mysql to the proper file.

sudo ln -s /usr/local/mysql/run/mysql_socket /var/mysql/mysql.sock

Could have sworn that I’ve posted about this before, but it seems to be missing. To change the minimum word length that MySQL uses for a fulltext index search, just set a configuration and restart.


$ vi /etc/my.cnf


Then add in:


ft_min_word_len=3


Or whatever minimum word length you’re looking for. Then, restart:


/etc/init.d/mysqld restart


Then rebuild your fulltext index.


ALTER TABLE `mytable` DROP INDEX `myfulltext_index`
ALTER TABLE `mytable` ADD FULLTEXT `fulltext` (
`myfieldname` ,
`anotherfieldname` ,
)


While I was at zendcon I needed to setup my macbook with a typical apache/php/mysql server, but I was unsatisfied with both the default server that apple included and with the install system provided by XAMPP. I’ve since setup a server on both my MacBook and my iMac.

In order to get support for mysql as well as tools necessary for compiling, you need to install the apple developer tools. Check your original mac disc as you may already have a copy.

Anyway, on with our install:

Apache

First, download the latest version of the apache httpd web server.

./configure --prefix=/usr/local/apache --enable-module=so --enable-module=rewrite

Then, run the standard make, sudo make install.

MySQL

Then download the latest mysql source file. Make sure you download the source “Compressed GNU TAR archive (tar.gz)” as we’ll need to custom compile it.

However, PHP5 no longer includes mysql shared libraries needed during the configure process. To get these, download the source file for the mysql version you just installed. Once downloaded, decompress it and run the following:

./configure --prefix=/usr/local/mysql --with-unix-socket-path=/usr/local/mysql/run/mysql_socket --with-mysqld-user=mysql --with-comment --with-debug

make

sudo make install

sudo /usr/local/mysql/bin/mysql_install_db --force

sudo mkdir /usr/local/mysql/run
sudo mkdir /usr/local/mysql/data

sudo chgrp -R mysql /usr/local/mysql

sudo chown -R mysql /usr/local/mysql/run /usr/local/mysql/var /usr/local/mysql/data

Then start the server with:

sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

zlib

Then, install zlib if it’s not already installed on your system. Download zlib, and it should be a basic ./configure, make, sudo make install process.

Once zlib is installed, make sure you know the path as we’ll need it during the php configure process.

PHP

Next, download the latest php package. Decompress it, and now we’re ready to configure.

./configure --prefix=/usr/local/apache --enable-cli --with-mysql=/usr/local/mysql --with-zlib-dir=/usr/local --with-apxs2=/usr/local/apache/bin/apxs

Then, run make and sudo make install to complete the process.

Restart apache and test everything out. If php didn’t do it automatically, add in AddType application/x-httpd-php .php to your httpd.conf file – commonly located in /usr/local/apache/conf/httpd.conf.

Then, start your webserver.

sudo /usr/local/apache/bin/apachectl start

And finally, once everything is done you’ll want to setup your account to properly allow access to mysql and apache commands. Just run the following to add their paths to your bash profile:

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile
echo 'export PATH=/usr/local/apache/bin:$PATH' >> ~/.bash_profile

Many of my clients need a quick backup solution before we implement anything on a larger more permanent scale. I wrote this simple script that simply make a copy of a database and then created a timestamped tarball of the file.

#!bin/sh

date=`date +%Y-%m-%d_%Hh%M`

cd /my/path/for/backups/

mysqldump -umy_user -p"mypass" my_database > $date.sql

tar -zcvf $date.tgz *.sql

# uncomment this line to import the sql into another database, for example as a mirror
#mysql -umy_user -p"mypass" mirror_database < $date.sql

rm *.sql

Then, just setup a cron job to run this at whichever interval you want this to run.

I’ve come to design every database table with an auto-increment id field that is a primary key. However, sometimes I work on projects where tables have no such column and may have duplicate rows. In order to drop the existing primary key definitions I run:

[mysql]
ALTER TABLE mytable DROP PRIMARY KEY;
[/mysql]

Then, I add a new one:

[mysql]
ALTER TABLE `mytable` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;
[/mysql]

I’m still rather disappointed with my options for weighting random results in MySQL, but I’ve done the best I can do with it. It’s more about figuring out what numbers you use for the weighting rather than the calculation itself. The query itself ends with:

[mysql]
ORDER BY RAND() * weight_fieldname
[/mysql]

Now, my possible weights from weight_fieldname are 5, 20, 100. 5 is the default weight for every record – 20 increases the chances of the record a bit and 100 pretty much sets the record as an exclusive result. Now, if you only have a single weight applied then setting it to 0 will reduce the weight of an item, however this is very difficult when you start having multiple weights.

One additional problem is that if you have a serious amount of records, using ORDER BY RAND() is going to be a performance hit.