Update Feb 21 2011: New module at a new home! See here for details

Upon the release of Kohana 3, one of the first things I wanted to do was install Doctrine. The following is a short tutorial on how to do; and keeping with Kohanas modular style, it will be placed in its own reusable module. Time to begin.

Create the following files/folder:
/modules/doctrine
/modules/doctrine/init.php
/modules/doctrine/classes
/modules/doctrine/classes/doctrine

Inside /modules/doctrine/classes/doctrine drop the official latest build (see here) of Doctrine such that Doctrines CHANGELOG, COPYRIGHT etc files etc are inside.

Enter the following into init.php. Note – this may not be the optimal bootstrap file – feel free to tweak to your hearts content.

Read More »

When it comes to online shopping carts, LemonStand is the hot new thing. Still in beta, this piece of software boasts a surprisingly large feature set and high level of customization options usually only seen in much larger CMS’s. The one narking issue I’ve had with it so far is its choice of URL separator – preferring underscores over dashes. After speaking to the very polite and friendly developer behind LemonStand, a solution was quickly devised.

To convert the default URL separator in LemonStand from underscores to dashes simply add the following line of code to your config/config.php file.

$CONFIG['URL_SEPARATOR'] = '-';

Update 3 Jun 2010: I’ve received word from the author behind LemonStand that he’ll be publishing an update later today changing the default URL separator from underscores to dashes. The change will not affect any existing installations and only apply to new installations. For developers wishing to change the default separator on new pages on existing installations, the fix above still applies. This is great news that I’m sure many developers out there will be pleased with.

Read More »

WordPress is great on its own but if you want to do alot of database work with it you’ll quickly realize how frustratingly limited the wpdb class is. In this tutorial I’ll show how to integrate Doctrine into WordPress through the use of a plugin and make your developing life so much easier.

Read More »

A common request with MySQL is to import/export CSV files. CSVs are merely delimited text files and so this is relatively easy to accomplish. To import we’ll be using a LOAD DATA query and for exporting the mysqldump tool bundled with MySQL. We’ll be delimiting our CSV with commas and enclosing each field with double quotation marks.

Read More »

The MySQL ORDER BY clause let’s you easily order in fields in ascending or descending order however occasionally you’ll want to have a specific order

Take for instance if you have a list of products on a sales site ordered by name. Your boss tells you he wants to show one (or several) products before all others on the page. At this point you have 2 options – add a ‘special’ column in the table and do an ORDER BY special, name – which would only work if he wanted these special products in alphabetical or reverse alphabetical order – or use a UNION with multiple SELECT queries. Neither are great options. This is where the FIELD() function comes in.

The rows ordered by FIELD() will appear at the end of your table, so to get them appearing at the top just add DESC and put enter your FIELD() arguments in reverse order. All rows not specifically ordered with FIELD() will remain unordered unless specified otherwise. Here’s an example.

If the ID of the products your boss wants to appear first are 36, 40, 12 respectively, we can order like so:

SELECT *
FROM products
ORDER BY FIELD(id, 12, 40, 36) DESC, name

There is no limit to the number of arguments you can add and they must be the same data type as your field.

Read More »

When paginating data on your website you’ll probably have a query something like the following:

SELECT *
FROM my_table
LIMIT 0,50

However in doing so, chances are you’ll want to have a ‘Showing x-y of z rows‘ clause at the top of your table. Retrieving that z value can be quite a hassle – it’d be very ugly to need to execute the same query again sans the LIMIT clause. Recently I’ve come across an easy way of accomplishing this in MySQL – the FOUND_ROWS() function.

To use it is simple. In your first query place SQL_CALC_FOUND_ROWS after the SELECT keyword like so:

SELECT SQL_CALC_FOUND_ROWS *
FROM my_table
LIMIT 0,50

Then immediately afterwards execute this query to return the total number of rows before the LIMIT clause was executed:

SELECT FOUND_ROWS()

That’s it. Make sure to use the second query straight after the first or the result count will be lost.

Read More »

UPDATE: This script has been superseded by my Cloud Database Backup script. It’s advised you use that instead.

If you’re a developer you should back up your data. Go ahead – do it, do it now. Okay, now that we’ve got that out of the way here’s a nice automated solution to backing up your MySQL databases and optionally uploading to Amazon S3 for added safety.

Read More »