MySQL multiple insert statements

If you need to insert multiple records into a MySQL database at once, you can perform each insert as a separate query or combine them into one query as follows. Suppose you want to insert 3 records into a users table. Here's the standard separate query method:

INSERT INTO users (name, email) VALUES ('Ed','ed@internet.com');
INSERT INTO users (name, email) VALUES ('Jon','jon@intertubes.net');
INSERT INTO users (name, email) VALUES ('Jamey','jameyd@web.org');

Or you could do this alternatively as one INSERT statement:

INSERT INTO users (name, email) VALUES 
('Ed','ed@internet.com'), 
('Jon','jon@intertubes.net'), 
('Jamey','jameyd@web.org');

PHP: wordwrap

PHP has so many functions available that it's very easy to overlook some of them. In my case one of these functions is wordwrap which I'd never heard of until just recently. This function will wrap a string to a given number of characters using a user-defined line-break string (or just \n by default) at natural word breaks (spaces). For example:

$text = "supercalafragilisticexpealadocious is a very long word";
$newtext = wordwrap($text, 18, "<br />\n");

echo $newtext;

This returns the following.

supercalafragilisticexpealadocious<br />
is a very long<br />
word

Notice how the first word is longer than the specified maximum of 18 characters, but is not broken. This default behavior can be turned off by specifying an optional 4th parameter as follows:

$text = "supercalafragilisticexpealadocious is a very long word";
$newtext = wordwrap($text, 18, "<br />\n",true);

echo $newtext;

This forces any word longer than the 18-character maximum to break at 18 characters:

supercalafragilist<br />
icexpealadocious<br />
is a very long<br />
word

MySQL: WITH ROLLUP

MySQL's WITH ROLLUP modifier when used in conjunction with GROUP BY allows users to produce multiple levels of summary values. For instance, suppose you need to generate a listing of the average and total salary by department for a company but also the average and total for the entire company. You could accomplish this using 2 separate queries as follows:

->SELECT department, avg(salary) AS salary_average, sum(salary) AS salary_total
->FROM employees
->GROUP BY department
+------------+----------------+--------------+
| department | salary_average | salary_total |
+------------+----------------+--------------+
| Accounting |         234567 |      2000001 |
| Creative   |          12000 |       100000 |
| Tech       |           2000 |        50002 |
+------------+----------------+--------------+
->SELECT avg(salary) AS salary_average, sum(salary) AS salary_total
->FROM employees
+----------------+--------------+
| salary_average | salary_total |
+----------------+--------------+
|         123456 |      2150003 |
+----------------+--------------+

Using WITH ROLLUP you could perform the preceding operation with just one query:

->SELECT department, avg(salary) AS salary_average, sum(salary) AS salary_total
->FROM employees
->GROUP BY department WITH ROLLUP
+------------+----------------+--------------+
| department | salary_average | salary_total |
+------------+----------------+--------------+
| Accounting |         234567 |      2000001 |
| Creative   |          12000 |       100000 |
| Tech       |           2000 |        50002 |
| NULL       |         123456 |      2150003 |
+------------+----------------+--------------+

The difference is in the last line where for department a NULL value is given along with a super-aggregate value for both the average and sum. The WITH ROLLUP modifier basically adds another row to the query results with the values obtained from the original query but without the GROUP BY clause. This method is more efficient than using 2 queries since the data need only be scanned once.

PHP parse_url

PHP's parse_url is a handy little function that will parse a URL and return the various components in an associative array. For instance, consider the following code:

$url = "http://www.google.com/search?hl=en&q=php+rules&btnG=Search";
$parts = parse_url($url);
print_r($parts);

This would return:

Array
(
    [scheme] => http
    [host] => www.google.com
    [path] => /search
    [query] => hl=en&q=php+rules&btnG=Search
)

MySQL GROUP_CONCAT

The GROUP_CONCAT function is used in MySQL to concatenate column data from a particular grouping into a string, rather than performing this action inside a client side script. For instance, if you have a table of large cities along with their state, you could do this:

SELECT state, city
FROM bigcities

which would return

+-------+--------------+
| state | city         |
+-------+--------------+
| OH    | Cleveland    |
| OH    | Columbus     |
| OH    | Cincinnati   |
| PA    | Pittsburgh   |
| PA    | Philadelphia |
...

and then process each row and group them by state within your PHP script. Or you could use GROUP_CONCAT as follows:

SELECT state, GROUP_CONCAT(city) AS cities
FROM bigcities
GROUP BY state

which would return:

+-------+--------------------------------+
| state | cities                         |
+-------+--------------------------------+
| OH    | Cleveland,Columbus,Cincinnati  |
| PA    | Pittsburgh,Philadelphia        |
...

The default separator is a comma, but you can define an alternative separator as follows:

SELECT state, GROUP_CONCAT(city SEPARATOR ' -- ') AS cities
FROM bigcities
GROUP BY state

which would return

+-------+--------------------------------------+
| state | cities                               |
+-------+--------------------------------------+
| OH    | Cleveland -- Columbus -- Cincinnati  |
| PA    | Pittsburgh -- Philadelphia           |
...

PHP: Singleton design pattern

The Singleton design pattern is useful for limiting the number of instances of an object. Here's a short and easy-to-understand tutorial on how to implement this pattern using PHP.

How to use the Singleton design pattern

MySQL: BETWEEN

It's often necessary when constructing queries to limit a result set based on a particular field falling within a specified range. For example, if you want to select records from a table that fall within a certain date range you may write a query as follows:

SELECT * FROM sometable
WHERE date <= '2009-04-15'
AND   date >= '2009-04-01'

An alternative way to construct the above query is by using the BETWEEN statement:

SELECT * FROM sometable
WHERE date BETWEEN '2009-04-01' AND '2009-04-15'

PHP: avoid short tags

PHP code blocks are most commonly opened and closed as follows:

<?php //code here ?>

You can also use:

<script language="php"> //code here </script>

Both of the preceding methods are always available for php scripts to use. You can also optionally use php short tags or ASP-style short tags although both of these methods can be turned on and off using the php directives short_open_tag (on by default) and asp_tags (off by default) respectively:

//short tags
<? //stuff here ?>
<?= $somevar; ?> // will emulate <?php echo $somevar; ?>

//asp tags
<% //stuff here %>

Since short tags are not always available depending upon a server's configuration, they are less portable and therefore it is generally considered best practice to avoid their usage. PHP short tags can also potentially conflict with XML open tags.

MySQL table and index metadata

MySQL has many ways to obtain table metadata. Use SHOW TABLES FROM database to obtain a list of the tables in a database (omitting the FROM will use the default database). SHOW TABLES can also take a LIKE 'pattern' clause as follows:

mysql> SHOW TABLES FROM mydatabase LIKE '%users%';
+---------------------------------+
| Tables_in_mydatabase (%users%)  |
+---------------------------------+
| admin_users                     | 
| registered_users                | 
| registered_users_groups         | 
+---------------------------------+
3 rows in set (0.00 sec)

Another statement that comes in handy is SHOW CREATE TABLE, which will display the CREATE TABLE statement associated with a given table:

mysql> SHOW CREATE TABLE mydatabase.admin_users\G
*************************** 1. row ***************************
       Table: admin_users
Create Table: CREATE TABLE `admin_users` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `email` varchar(64) NOT NULL default '',
  `password` varchar(40) NOT NULL default '',
  `first_name` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `AU_email_idx` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

SHOW INDEX will display index information:

mysql> SHOW INDEX FROM  mydatabase.admin_users\G
*************************** 1. row ***************************
       Table: admin_users
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 18
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: admin_users
  Non_unique: 0
    Key_name: AU_email_idx
Seq_in_index: 1
 Column_name: email
   Collation: A
 Cardinality: 18
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
2 rows in set (0.00 sec)

To see column information from a table use one of the following:

DESCRIBE mydatabase.admin_users
SHOW COLUMNS FROM mydatabase.admin_users 
SHOW FIELDS FROM mydatabase.admin_users

Any of these statements would yield output similar to this:

+--------------+----------------------+------+-----+--------+----------------+
| Field        | Type                 | Null | Key | Default| Extra          |
+--------------+----------------------+------+-----+--------+----------------+
| id           | smallint(5) unsigned | NO   | PRI | NULL   | auto_increment | 
| email        | varchar(64)          | NO   | UNI |        |                | 
| password     | varchar(40)          | NO   |     |        |                |
| first_name   | varchar(32)          | YES  |     | NULL   |                | 
| last_name    | varchar(64)          | YES  |     | NULL   |                |
+--------------+----------------------+------+-----+--------+----------------+

The information_shema database also includes a TABLES table with table metadata, a STATISTICS table for table index metadata and other metadata tables.

PHP for loop optimization

The for loop is a staple of just about every programming language including php. Here is the basic structure:

for (expr1; expr2; expr3)
    statement(s)

expr1 is evaluated at the beginning of the loop. expr2 is evaluated at the beginning of each iteration and if true the loop continues and statement(s) is executed. expr3 is executed at the end of each iteration. The following is a common practice when using a for loop to iterate through an array:

for ($i = 0 ; $i < count($array) ; $i++) {
    //statements here
}

This works but is not optimized because the count($array) function must be executed on every iteration. It would be better to evaluate this and store it in a variable prior to executing the loop as follows:

$count = count($array);
for ($i = 0 ; $i < $count ; $i++) {
    //statements here
}

What is often overlooked regarding for loops is that each of the expressions can be empty or contain multiple expressions separated by commas. So we could also write the previous example as:

for ($i = 0, $count = count($array) ; $i < $count ; $i++) {
    //statements here
}

MySQL DELETE verus TRUNCATE

There are two ways to remove all the records from a table in MySQL; DELETE and TRUNCATE as follows:

DELETE FROM sometable
TRUNCATE TABLE sometable

These 2 statements are functionally equivalent except for the following:

1. DELETE usually executes more slowly than TRUNCATE
2. DELETE returns a row count indicating the number of records deleted while TRUNCATE always returns a row count of zero.
3. TRUNCATE will reset any AUTO_INCREMENT fields to 1 while DELETE will maintain the current AUTO_INCREMENT value (note that there are exceptions to this depending on the type of database engine and version of MySQL).

PHP: using isset to test string length

When working with strings it is often necessary to check that the string is of a certain minimum or maximum length. The standard practice is to use the strlen function to accomplish this as follows:

if (strlen($password) < 6) { echo "Password too short"; }

This is fine except there's another way to accomplish this using isset and php's ability to access the characters of a string as if it were an array. Since isset is a language construct rather than a function, it is faster then using strlen and might increase performance a bit if your code includes a large number of these calculations (in a loop for instance). Here's the above code using the isset trick:

if (!isset($password{5})) { echo "Password too short"; }

Basically this tests whether the 6th character in the string is set. If it is not, then the string must be less than 6 characters in length.

MySQL random sample

If you need to select a random sample of rows from a MySQL result set use the RAND function to randomly order your results, and LIMIT to limit the number of records. For instance, if you need to select 3 winners from a pool of entries stored in a table for a certain time period you could use the following:

SELECT * FROM entries 
WHERE entry_date <  '2009-04-01' 
AND   entry_date >= '2009-01-01'
ORDER BY RAND()
LIMIT 3

This would return 3 random entries from the result set.

PHP and dynamic class autoloading

Object-oriented PHP applications often are structured such that each class is contained in a separate source file. The trouble with this is that as an application gets larger and larger, it becomes increasing difficult to keep track of which class files you need for a given script. Rather than requiring every single class file in your PHP scripts, the developers of PHP came up with a neat solution in the __autoload magic function. If defined, every time an undefined class is instantiated, the __autoload function gets executed with the name of the instantiated class passed as a parameter. Suppose I store all my class files in the directory php/classes/ and name them CLASS_NAME.class.php. I could then write an __autoload function as follows:

function __autoload($class_name) {
   require_once $_SERVER['DOCUMENT_ROOT']."/php/classes/".$class_name . ".class.php";
}

I then simply include the __autoload function on each script rather than requiring every single class file. When an undefined class is instantiated, it gets automatically loaded in by the autoloader. The above example is fairly rudimentary; you can do a lot more with autoloading as demonstrated in this article at Generation Five. The article talks about several different options for integrating autoloading into your applications as well as all the pitfalls you might run into.

MySQL temporary tables

Creating temporary tables in MySQL is an easy process; simply use CREATE TEMPORARY TABLE rather than CREATE TABLE. Temporary tables differ from standard tables in that they are only visible/usable by the client who creates them and only exist for the duration of the connection in which they were created. The server will automatically drop a temporary table when the client connection ends. Temporary tables may have the same name as an existing standard table, although the standard table will be hidden from the client until the temporary table with the same name is dropped.