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.

PHP require versus include

PHP has 2 commands available for including files within scripts; namely require and include. The 2 are identical except for one important distinction; error handling. If include cannot locate the file to include it emits a warning while require will emit an error. Consider the following code which attempts to include a non-existent file:

<html>
<body>
<p>BEFORE</p>

<?php include("file_no_exist.txt");?>

<p>AFTER</p>
</body>
</html>

The output will be:

BEFORE

AFTER

But if we replace include with require the output would just be:

BEFORE

When the require statement cannot find the file it throws a fatal error which halts execution of the script; nothing after the require statement will be executed. For this reason it is generally considered best practice to use require over include because scripts should not be executing if necessary files are missing or misnamed.

MySQL: create table based on existing table

MySQL offers a simple way to create a new table with the same column and index definitions as an existing table. If you have a table named table1 defined as follows:

CREATE TABLE table1 (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30),
email VARCHAR(40),
created DATETIME,
index name_idx (name)
)

Then executing the following will create an identical table with the name table2:

CREATE TABLE table2 LIKE table1

This copy will retain all the column definitions and indexes as table1 and will be identical in all respects (with a few exceptions). You can also create a copy of a table from an existing table using

CREATE TABLE table2 SELECT * FROM table1

In this case table2 will have the same column type definitions and values as table1, but index information is not duplicated. The table would be defined as follows:

CREATE TABLE table2 (
id int(11) NOT NULL default '0',
name varchar(30) default NULL,
email varchar(40) default NULL,
created datetime default NULL
)

You can also restrict the values copied into table2 based on the results of the SELECT statement. For instance,

CREATE TABLE table2 SELECT id, name FROM table1 WHERE id > 1000

will only copy values from table1 where the id is greater than 1000, and only recreate the id and name columns from table1.

See the MySQL manual for all the details on this functionality.

PHP ctype extension for data validation

PHP's ctype extension includes a set of functions which are wrappers around C's isDATATYPE functions (such as isalpha) that check whether a particular character is within a certain range. But unlike the C functions that only work on one character at a time, the PHP functions operate on the entire string. These functions are much faster than PHP's regular expression functions which are often used to validate input. As an example of where you might use these functions, suppose you have a string which is required to consist of all digits. Using regular expressions you might use

preg_match("/[0-9]+/", $somevar)

But using the ctype functions you could write it as

ctype_digit($somevar)

which is much faster.

MySQL slow query log

Probably the best way to locate and diagnose troublesome MySQL queries is through utilization of MySQL's slow query log. If enabled, MySQL will log any query that takes longer than long_query_time seconds (default is 10 seconds) to run to a file. The log file by default is in the MySQL data directory and named SERVER-slow.log. For the details on how to configure and utilize the slow query log, see the MySQL documentation.

PHP alternative control structure syntax

PHP offers an alternative syntax for most of its control structures that is handy for enhancing the readability of PHP code embedded within html. For the control structures if, while, for, foreach, and switch simply replace the opening bracket ({) with a colon (:) and end the statement with endif, endwhile, endfor, endforeach, and endswitch respectively. For instance, here's some embedded PHP code using the standard control structure syntax:

<?php if ($showthis) { ?>

<p>some html stuff here</p>
<i>blah</i>

<?php } else { ?>

<p>some other stuff</p>
<i>fubar</i>

<?php } ?>

And using the alternative syntax this becomes:

<?php if ($showthis): ?>

<p>some html stuff here</p>
<i>blah</i>

<?php else: ?>

<p>some other stuff</p>
<i>fubar</i>

<?php endif; ?>

With the alternative syntax it is easier to see where one block ends and another begins, and separates out the code from the content in a way that is more readable. This is especially true for situations where you have multiple nested loops and control statements.

MySQL: WHERE versus HAVING

Using the WHERE clause to restrict a result set is familiar to anyone who regularly uses SQL, but the use of the HAVING clause may not be. So what's the difference between the two? The WHERE clause is used to restrict records, and is also used by the query optimizer to determine which indexes and tables to use; only actual fields represented in the query table(s) can be utilized. The HAVING clause is a filter on the final result set, and is applied after ORDER BY and GROUP BY. So MySQL cannot use it to optimize the query, but it can filter the results based on a calculated or aggregate value obtained with a GROUP BY clause or similar means. As a rule, use WHERE when you can in order to utilize a table's indexes and use HAVING to restrict a result set otherwise. For instance, take the following query which demonstrates a common mistake in MySQL:

SELECT sum(salary) AS amount 
FROM employees 
GROUP BY department 
WHERE amount > 500000;

The idea with this query is to find departments at a given company where the total salary expenditure is greater than $500,000. However, it won't work because the 'amount' field is a calculated value and cannot be used by the WHERE clause. You'll get an error like this: Unknown column 'amount' in 'where clause'. The correct way to write this query is by using HAVING as follows:

SELECT sum(salary) AS amount 
FROM employees 
GROUP BY department 
HAVING amount > 500000;

This time, MySQL will calculate all the 'amount' values then use the HAVING clause to filter out any records where the amount is <= 500000.

Here we use WHERE and HAVING in combination:

SELECT sum(salary) AS amount 
FROM employees 
WHERE salary < 50000 
GROUP BY department 
HAVING amount > 500000;

In this query we find departments at a given company where the total salary expenditure is greater than $500,000 but only including employees who make less than $50,000 a year.

PHP and XML

PHP has extensive built-in support for simplifying the parsing, altering and creation of XML documents. Here's a great tutorial at PHP Freaks that walks you through all the basics of using the SimpleXML and DOMDocument built-in classes.

PHP Freaks - Tutorial: Handling XML data

MySQL: viewing current processes

When debugging/troubleshooting issues with your MySQL queries, it's often beneficial to be able to see which processes are running on your MySQL server along with their current state. MySQL has an easy way to do this by using the SHOW processlist command. Typing this at the MySQL prompt will allow you to see all of the current processes running on your server for all the databases for which you have access. You'll see the process id, the user and host running the process, the query itself (a truncated version), the time the query has been running and its current state. To see the full, non-truncated query use SHOW FULL processlist. And if you need to kill off a certain process just type KILL with the id of the process passed as an argument.

PHP dynamic image generation

One of the coolest things about PHP is its ability to create and manipulate images, and even output image streams directly to a browser using the GD library. You can take advantage of all the PHP image functions assuming your install of PHP was compiled with the GD library. For all the details and requirements just check out the GD section of PHP.net. I found this simple and entertaining little tutorial at PHPFreaks detailing how to dynamically create an image with text on it using a PHP script and outputting it to a browser - essentially what captcha does. The image functions are also useful for adding watermarks to images, resizing/cropping images, etc.

MySQL: avoid functions in WHERE clauses

One of the primary rules of MySQL Performance Optimization is to avoid using functions in a WHERE clause comparison when comparing an indexed column to a constant value. In other words, using indexed_column_name = SOME_CONSTANT is good but function(indexed_column_name) = SOME_CONSTANT is bad. MySQL will be unable to utilize the index on the column even if the function is very simple. The same applies to ORDER BY if you want to utilize an indexed column for sorting. Here's a real-world example. I have a table of user data with over 750K records in it. Take the following query where email is an indexed column.

SELECT * FROM users WHERE email = 'estenger@digiknow.com'

The query takes less than 0.1 seconds to run. Now let's examine the same query where I apply the LOWER function to the email column in the WHERE clause.

SELECT * FROM users WHERE LOWER(email) = 'estenger@digiknow.com'

This query takes a whopping 15+ seconds - over 150 times slower! I should also note that MySQL string comparisons are case-insensitive by default so applying the LOWER function is completely unnecessary here. Only use functions in a WHERE clause when absolutely necessary and if no other alternative is available.

Use PHP for shell scripting

Not everyone is aware that PHP can be used as a shell scripting engine. While not as robust as BASH or Perl, it is a much easier language to use - especially if you're a web developer already used to using it for web applications. Here's a great article on the subject over at PHPBuilder.com: Using PHP As A Shell Scripting Language.

PHP/MySQL cheat sheets

Here are a couple of nice little one-page cheat sheets for both MySQL and PHP that you can hang by your desk. The MySQL one lists out all the most common MySQL built-in functions, data types, php mysql functions and a few simple sample queries. I also find it useful to hang a cheat sheet for the MySQL DATE_FORMAT format specifier strings detailed here. The PHP sheet lists out common functions, regular expression syntax and date format specifiers. Whereas these sheets don't provide much detail, they are good for reminding you what functions and language constructs are available and what syntax to use without having to look something up online or in a book.

PHP: echo vs. print

I found this interesting article at LearnPHPOnline which performs an analysis of using echo versus print to output data. Both print and echo are considered language constructs rather than functions, but print can behave like a function if passed the right parameter. So which should you use? For all intents and purposes, it doesn't really matter that much although echo is slightly faster than print as is demonstrated in the article. I found this bit the most interesting conclusion the authors gleaned from their analysis:

As a last note on speed, it's recommended that developers add strings together via parameters- not through concatenation or multiple Echo calls. Instead of using new Echo commands to help organize code, separate them with commas (Make certain you aren't using concatenation- this actually slows the process)! Calling the Echo or Print command multiple times will also degrade the performance of the script, although marginally, as seen below:

echo "Hello" . "World! <br />"; 
// Concatenation slows down the process 
// because PHP must add strings together

echo "Hello" , "<br />";
echo "World" , "<br />";
// Calling Echo multiple times still isn't 
// as good as using Echo parameters solely

echo "Hello" , "World!" , "<br />"; 
// Correct! In a large loop, this could 
// save a couple of seconds in the long run!
?>

The author also makes the point that echo has one less character than print - which means less work for us lazy programmers. :)

MySQL: controlling output display with line terminators

When using the mysql command line interface, you can specify whether to display your results horizontally or vertically by using different line terminators. Using a ";" or "\g" causes the output to be displayed horizontally while a "\G" will cause the display to be vertical. As an example consider the following. Here we use the "\g" to display output horizontally. A ";" will yield the same results:

mysql> select id, name from sometable\g
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | Geddy Lee         |
|  2 | Neil Peart        |
|  3 | Alex Lifeson      |
+----+-------------------+
3 rows in set (0.00 sec)

Using the same query with the "\G" terminator:

mysql> select id, name from sometable\G
*************************** 1. row ***************************
  id: 1
name: Geddy Lee
*************************** 2. row ***************************
  id: 2
name: Neil Peart
*************************** 3. row ***************************
  id: 3
name: Alex Lifeson
3 rows in set (0.00 sec)

For output with many columns, the second option is often preferred as it is more readable.

PHP IE7 and SSL caching issue

I ran into a problem yesterday regarding a flash application running over an SSL connection using IE7. The flash application in question is passed a php script via the url which returns xml as the output; something like this: main.swf?script_file=script.php. I don't know all the complete details, but apparently over an SSL connection IE attempts to cache the xml which is passed to the swf. PHP by default does not cache php scripts. So since in this case the xml is created via a php script, IE cannot cache the xml and instead it returns an error (why??). So the swf itself loads, but will not get passed the xml file, which causes the application to not work properly. There's a lot of confusing/contradictory information regarding this out on the web, but it seems to be a bug in IE. Regardless of the exact cause, the fix is to explicitly tell your php script (the script.php file in the example above) to cache the output by setting these headers:

header('Cache-Control: cache, must-revalidate');
header('Pragma: public');

MySQL: resetting auto_increment

When using a table with an auto_increment column, it is sometimes useful to know how to reset the auto_increment value. For instance, if you needed to add some temporary test records to a table and removed them later, but didn't want a "gap" to appear in the values of the auto_increment field in question. As an example, suppose you had a table with a primary key auto_increment field named id with 7 records in it. You then add test records 8, 9, 10 and subsequently remove these records. This causes the auto_increment to be set to 11 (the last inserted id plus 1). Thus the next record inserted would have an id value of 11, causing a "gap" (... 4, 5, 6, 7, 11). To correct this, after deleting records 8, 9, 10 reset the auto_increment to 8 using

ALTER TABLE test_table AUTO_INCREMENT = 8

This would set the auto_increment to 8 thus removing the "gap". The auto_increment field cannot be reset to a value smaller than the current largest auto_increment field value plus 1 (so in the preceding example you couldn't set it to 7 or something smaller). You can also use this feature if for some reason you wanted to start the value of the auto_increment for a given table as something other than the default value of 1 . Suppose you had a table of user data which required the user ids to be numbers larger than 1000. You could then set the auto_increment for that table to 1000 immediately after creating it. For more on using auto_increment see the MySQL documentation page.

PHP search and replace

When performing a simple search/replace inside of a string use str_replace or strtr over ereg_replace and preg_replace whenever possible. Reserve use of the latter functions for complex search/replace operations where regular expressions are required. The str functions are much faster.

PHP/MySQL Tip of the Day

I'll be posting my PHP/MySQL tip of the day at this blog as an easy way to archive them and keep track of comments. In this first post I'll summarize my first week of tips:

Monday, March 2nd

Here’s an article with 40 simple tips for optimizing your PHP code.
http://reinholdweber.com/?p=3
I was aware of many of these –but there are a lot on here that were new to me. I don’t suggest you go tear apart all of your existing code to fix things based on these 40 things, but it might help you in your day-to-day coding.

Tuesday, March 3rd

I’ll shift to MySQL today. Here’s a short little article on using “LOAD DATA INFILE”, which is a great resource for loading client-supplied spreadsheets into a database without having to write a script. The example used demonstrates a technique where you can transform the file data to the different fields in your table using built-in mysql functions and also how to skip columns.

http://tech.forumone.com/archives/64-MySQL-Tips-1.html


Wednesday, March 4th

It’s important that any PHP programmer have a good understanding of security issues. Here are a couple of informative articles I located:

http://www.sitepoint.com/article/php-security-blunders/2/
http://webmaster-forums.code-head.com/showthread.php?t=939
http://net.tutsplus.com/tutorials/php/5-helpful-tips-for-creating-secure-php-applications/

That’s just a small sample. There are a lot of good ones out there- just google it.

Probably the most important security tip that consistently pops up in all of these articles is to NEVER TRUST USER INPUT. If you follow that rule you’re already half way there with regards to writing secure code. Sanitize form data, check file upload mime types, use PHP SESSIONS over cookies, etc.

Security issues are also a big part of the Zend exam, so that’s yet another reason to be aware of good security practices.

Thursday, March 5th

Use php.net for referencing function details. This should be the first place you go when you need specifics about how a given function operates. They explain all the parameters, the return values and give examples along with often very useful user-supplied comments.

If you know the name of the function you can access it quickly by just typing in php.net/function_name . For example php.net/str_replace will take you right to the page for the ‘str_replace’ function.

Friday, March 6th

In the process of studying for the MySQL exams I’ve come across several aspects of MySQL that I wasn’t previously aware of. One such thing is the fact that you can designate a numeric column in a table as ZEROFILL. What this does is cause a field to be left-padded with zeroes (up to the max length of the column) upon select. So if you declare:

CREATE table TEST (
id INT(7) ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(64)
)

So if the value of id is ‘123’, when you do a select it would return ‘0000123’ rather than just 123. I usually do this kind of thing in the PHP code using str_pad or something similar, so it’s nice to have this available if needed.

The mysql manual mentions ZEROFILL but doesn’t really explain it well: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html