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'