Importing a MySQL DB into WAMP

If you are like me and other Windows users, you most likely use WAMP or XAMPP as a PHP localhost environment stack. And in most cases, you use the MySQL service along with phpMyAdmin. For the most part phpMyAdmin handles all the database management tasks you need as well as a pretty great import and export feature. However, there are times when your DB dump is far too large for phpMyAdmin to handle, and twiddling with php.ini settings doesn't help fix it -- but there is a solution!

The MySQL services comes installed with a MySQL console which you can use to import the SQL file. After you turn WAMP on, click on the system tray icon, navigate to the MySQL folder and open the MySQL console. This should open a command prompt that asks for your password (usually empty if you use the default install settings). To import simply run the following commands (you can type \h to view more all the commands).

use databaseName;
\. C:\path\to\sql\dump.sql

And that's it! Be sure to have a raw SQL dump as the import won't work on gzipped files.

Naming your cache keys

Everyone caches, that's a pretty well known fact. However, the problem I always seemed to have was how to properly name my cache keys. After much trial and tribulation, I believe I have found a great way to properly name cache keys. To make things easy, my keys usually follow this format.

<model|table>__<function|method>[-<params>]

To clear up some confusion, it goes as follows. The first word of your cache key should be your model name (or database table name), as most cached data relates to a database query result. The model name is followed by a double underscore, which is then followed by the function/method name (which helps to identify exactly where the cache is set), which is then followed by multiple parameters (optional). Here's a quick example:

public function getUserProfile($id) {
	$cacheKey = __CLASS__ .'__'. __FUNCTION__ .'-'. $id;
	// Check the cache or query the database
	// Cache the query result with the key
	// Return the result
}

The $cacheKey above would become: User__getUserProfile-1337, assuming the user's ID is 1337. Pretty easy right? Besides the verbosity that it takes to write these constants, it works rather well (unless you want to write the method and class manually). You may also have noticed that I used __FUNCTION__ over __METHOD__ -- this was on purpose. The main reasoning is that __METHOD__ returns the class and method name, like User::getUserProfile, while __FUNCTION__ just returns the method name.

The example above will work in most cases, but there are other cases where something more creative is needed. The main difficulty is how to deal with array'd options. There are a few ways of dealing with that, the first is checking to see if an ID or limit is present, if so, use that as the unique value. If none of the options in the array are unique, you can implode/serialize the array and run an md5() on the string to create a unique value.

User::getTotalActive();
// User__getTotalActive
Topic::getPopularTopics($limit);
// Topic__getPopularTopics-15
Forum::getLatestActivity($id, $limit);
// Forum__getLatestActivity-1-15
Post::getAllByUser(array('user_id' => $user_id, 'limit' => $limit));
// Post__getAllByUser-1-15
User::searchUsers(array('orderBy' => 'username', 'orderDir' => 'DESC'));
// User__searchUsers-fcff339541b2240017e8d8b697b50f8b

In most cases an ID or query limit can be used as a unique identifier. If you have another way that you name your cache keys or an example where creating the key can be difficult, be sure to tell us about it!

Retrieving tables within your Cake database

I have been working lately on adding an installation process to my forum plugin. For this process I needed a way to retrieve a list of tables within a selected database, to make sure there are no table name conflicts. After asking the question on Twitter, I got the answer thanks to @pierremartin and @mark_story (of course!). You simply use the ConnectionManager class.

$db = ConnectionManager::getDataSource('default');
$tables = $db->listSources();

Very handy, and a lot easier then writing a custom query with execute().

Databasic 2.1, now with more operator support!

I got pretty bored the other day and also noticed this contest over at NetTuts, and thought to myself, "Why not enter Databasic into the contest?". Well that is my plan, but I also wanted to fix some problems and restraints in the current version. The new version supports AND/OR operators in the conditions, as well the column operators (!=, <=, etc) have been rebuilt. With this change the new version is not backwards compatible! Sorry, but it shouldn't be too hard to fix your scripts to work correctly.

Here's a quick example of the new operator support in conditions.

$conditions = array(
    'OR' => array(
        array('name' => 'Miles'),
        array('name' => 'Johnson')
    ),
    'status' => 'active',
    'age >=' => 21
);

Download the new 2.1!
View the full change log and features