January 2010: There really isn't an upgrade path for MySQL when moving from 32-bit Windows XP to 64-bit Windows 7 Home Premium, especially if the old password format is in effect in the XP installation. The best thing to do is export your data, verify the export files are readable, backup them up, uninstall MySQL, rename the old installation directory, reboot, install the current 64-bit version of MySQL and import data which was exported earlier. Do not import tables `mysql` and `information_schema`. Manually re-enter all the user names, passwords, and privileges using MySQL Administrator. See OK packet 6 bytes shorter than expected.

In my humble opininon, Apache 2.2 cannot be installed on 64-bit Windows 7 *and* play well with PHP and MySQL. My recommendation if creating a WAMP environment on 64-bit Windows is to use Apache 2.0 and not version 2.2. Either version is installable and works perfectly well independently ... but 2.2 won't play well with PHP and MySQL and you'll waste 2-3 days trying to figure out why things aren't working as they should.

Portions of the following are quoted from the MySQL™ web site. Observations and opinions expressed below may be useful to MySQL™ newbies (use this information at your own risk). Tips appearing below are believed by the author to be accurate, but may contain flaws.

Use the underscore character in your column names, e.g. first_name. Subsequently in your PHP scripts the underscore can be easily replaced with a space. Use only lower case letters to ensure portability to other system configurations.

Avoid using hyphens in the names of databases, tables, and columns. Situations can arise where the schema is not portable to another system configuration if upper case letters and/or hyphens are used. Hyphens appearing in a double-quoted line of PHP code may be interpreted as a minus sign (crashing the script).

Usage of SET and ENUM fields is tricky when writing PHP scripts, but once mastered, works really well.

MySQL™ will auto-generate sequential record number id's for auto_increment (integer) primary key fields. IMHO, always make an "id" field as the first column (MySQL™ column 0) and make it an auto_increment primary key field (MySQL™ utilities often expect the first column to be an auto_increment field). Vary the name of the field so that you don't end up with a field named "id" in each table (using "id" in every table may cause confusion when working with SQL queries involving more than one table).

Conserve MySQL™ resources and optimize your table schema. The smallest suitable column type should be employed whenever possible. Do not use a TINYINT column as the auto_increment primary key field unless you're sure the table will contain fewer than 128 records. Do not use an INT column as the auto_increment primary key field unless the table is actually going to hold a billion records. Using SMALLINT whenever possible will help reduce the amount of storage space required. Refer to the chart on this page that reflects the impact of defining a column as UNSIGNED. I am guessing that most auto_increment primary key fields should be SMALLINT() UNSIGNED NOT NULL DEFAULT '0' ... or possibly MEDIUMINT() UNSIGNED NOT NULL DEFAULT '0'.

Assign the field attribute NOT NULL unless you have a specific reason for inserting NULL's.

If the table being defined does not contain BLOB fields, use CHAR instead of VARCHAR. CHAR is supposedly faster than VARCHAR if the data length is about the same for all records. Note however that you can't mix CHAR & VARCHAR in the same table. MySQL™ will silently convert CHAR fields to VARCHAR fields under certain conditions (such as if a BLOB field is added to the table).

TIMESTAMP usage conserves storage space (as compared with using both a DATE and TIME field). Note that the formatting of TIMESTAMP columns is handled differently across different versions of MySQL™, with respect to hyphens and colons used to separate elements.

MySQL Utilities

Popular utilities for working with MySQL™ include MySQL Administrator, phpMyAdmin, and phpMyEdit. For those of you who intend to create a development environment on a Windows PC, WAMP5 will quickly install Apache 2, MySQL™ 5, and PHP 5 (users can optionally select a document root that matches your web server). Adminer is a great MySQL interface which offers much the same functionality as phpMyAdmin using only 1 script. dBug.php is a great debugging script for PHP.

Before you leave this site, please check out phpMyEdit, MySQL Table Editor.

Search and Replace Text in MySQL

UPDATE `tableName`
SET `ColumnName` = replace(`ColumnName`, 'OldString', 'ReplacementString');

UPDATE `tableName`
SET `content` = replace(`content`, '<h3', '<h1');

UPDATE `tableName`
SET `content` = replace(`content`, '/h3>', '/h1>');

Miscellaneous PHP Examples

phpMyAdmin or MySQL Administrator are probably the most reliable utilities for exporting MySQL data. The following examples create a batch file that can be used with mysqldump.exe on a Windows PC to export data and move the .SQL file into a .GZ archive.

Script 1 creates a batch file used to export MySQL data.

Script 2 moves the exported .SQL file to a .GZ archive.

Sample output from the batch file created using Script 1.

Old example: How to use mysqldump.exe with a batch file in order to backup a MySQL database on Windows.

PHP Script Example - FULLTEXT Search of MySQL Database Table

Related MySQL info can be found on this web site under Reserved Words, MySQL Schema I, MySQL Schema II, and MySQL Image Tables - Inserting images into a MySQL database table.

UTF8 and upgrade headaches with MySQL v3 or v4.0 --> v4.1 or v5

The TIMESTAMP column is handled differently between version 4.0 and 4.1 or 5.0, leaving me to conclude that as regards portability, storing PHP's TIME() value in a VARCHAR(10) column makes more sense than using TIMESTAMP columns. Hyphens are used to separate elements of the TIMESTAMP beginning with 4.1. Several years ago, I had MySQL™ v4.0 on my PC and my web host upgraded to 4.1x, certain queries are returning unexpected results when run on the web.

You might upgrade your MySQL™ version to 4.1 or 5.x on your Windows PC, and when you try to connect to the database you are greeted with Client does not support authentication protocol requested by server; consider upgrading MySQL™ client. Be sure to clear your browser cache after following the instruction in the prior link. Sometimes Firefox won't let go of old content.

It may be that all you need to do is add "old-passwords" to your my.ini file and restart MySQL™.

[mysqld]
...
old-passwords
...

See also this page.

If you have MySQL™ 3.x or MySQL™ 4.0.x on your PC's development environment and you want to upgrade, be aware there is no upgrade available. You have to back up all your databases, uninstall the lesser version, install the new version, and then import the data that you backed up. You should check out your backup files before you uninstall the old version of MySQL™.

If using UTF-8, LATIN2 (ISO-8859-2), or CP1250 (WINDOWS-1250) character sets, after connecting to MySQL and selecting database but before querying a table you may need to execute the appropriate query below.

// UTF8 connection
if(!@mysql_query('SET NAMES UTF8')){ die(mysql_error()); }
if(!@mysql_query('SET COLLATION_CONNECTION=UTF8_GENERAL_CI')){ die(mysql_error()); }

// ISO-8859-2 connection
if(!@mysql_query('SET NAMES LATIN2')){ die(mysql_error()); }
if(!@mysql_query('SET COLLATION_CONNECTION=LATIN2_GENERAL_CI')){ die(mysql_error()); }

// WINDOWS-1250 connection
if(!@mysql_query('SET NAMES CP1250')){ die(mysql_error()); }
if(!@mysql_query('SET COLLATION_CONNECTION=CP1250_GENERAL_CI')){ die(mysql_error()); }

Forcing UTF-8 Connections in MySQL 5.x or higher can potentially be accomplished using a statement in the my.cnf file calling init_connect.

With UTF8, you also need to be sure to apply the appropriate HTML META tags to your document.

<meta http-equiv="charset" content="UTF-8">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">

Connecting To Multiple MySQL Databases Or Hosts

// Increment $k below, once for each database/server connection
$k = 0;
$k++;
$opts['server'][$k]['hn'] = 'localhost';
$opts['server'][$k]['db'] = 'datbase_1';
$opts['server'][$k]['un'] = 'user_1';
$opts['server'][$k]['pw'] = 'password_1';
$k++;
$opts['server'][$k]['hn'] = '127.0.0.1';
$opts['server'][$k]['db'] = 'datbase_2';
$opts['server'][$k]['un'] = 'user_2';
$opts['server'][$k]['pw'] = 'password_2';
$db_count = $k; // Important
$stop = $db_count + 1;
for($k = 1; $k < $stop; $k++){
   // do you stuff here, e.g. exporting data from multiple servers
}

Update an empty field using a permutation of data present in another field

Create a filename in the `rewriterule`field, using text from the `heading` field.

UPDATE `table_name` SET rewriterule = CONCAT( LOWER( REPLACE( heading, " ", "-" ) ) , "-", page_id, ".html" ) WHERE rewriterule = '';

INFORMATION_SCHEMA is slow to respond

MySQL™ 5+ offers an INFORMATION_SCHEMA database containing extensive information on the configured databases, tables, and fields. However, if you have a lot of databases configured, the response time is terrible, making it essentially unusable with phpMyAdmin. See the Bug Report.

PHP Form Generator

PHP Form Generator can save you hundreds of hours of coding basic forms. Alternate PHP Form Generator is also provided to those who purchase the original form generator project.

Web Content Management System - Web Developer Resume