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.
Including every file in a specific directory automatically is a useful way of auto-loading classes in PHP. This could be done manually of course but we’re developers and we’re lazy so here’s a better solution utilizing PHP’s glob() function:
<?php foreach( glob(dirname(__FILE__) . '/classes/*.php') as $class_path ) require_once( $class_path );
In this script, glob() searches for all pathnames matching the given pattern (In this case all files with a .php extension in the ‘classes/’ directory). Please note that if glob() doesn’t find any matches it will return false instead of the expected array, so unless you’re iterating over its results with a function that checks for this – such as foreach – either typecast or perform some other form of validation before use. You’ve been warned.
UPDATE 15 May 2010: Found this informative article related to the various ways of loading all files in a directory and their respective speeds – performance matters, people!.
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.
There is a bug in some version of PHP causing large integers to print in scientific notation. Here’s how to replicate:
<?php $i = 6395360031312041; echo $i; //Outputs: 6.39536003131E+15
There is a quick and easy fix for this – simply number_format your integer whilst printing like so:
<?php $i = 6395360031312041; echo number_format($i, 0, '.', ''); //Outputs: 6395360031312041
This issue has been fixed in the latest version of PHP 5.2 (as of the time of writing that’s PHP 5.2.12).
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.
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:
That’s it. Make sure to use the second query straight after the first or the result count will be lost.
If you’re running WordPress on multiple servers, chances are you’ve come across an issue where once you’ve copied WordPress to the new domain, none of your scripts, styles or images are loading. One of the most common occurrences of this happening is running an installation on your local machine (localhost) and allowing people from an internet or network IP to view it. If you dig a little further by viewing page source you’ll notice the issue is caused by WordPress using your old domain (or localhost) for loading – resulting in extreme load on a single server or worse; a HTTP 404 error.
By default, CodeIgniter only allows segment-based URLs, however it is also possible with a little fiddling to use querystrings and even to make the change application-wide or controller-specific.
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.
Have you ever wanted to require a script in PHP without it inheriting all your current scripts’ variables? Did you instead want to pass different variables to it? With this nifty little function you’ll be able to do just that.