PHP/MySQL tip of the day: March 2009

Tuesday, March 31, 2009

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>
BEFORE

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

AFTER
</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.

Monday, March 30, 2009

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.

Friday, March 27, 2009

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.

Thursday, March 26, 2009

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.

Wednesday, March 25, 2009

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) { ?>

some html stuff here
blah

<?php } else { ?>

some other stuff
fubar

<?php } ?>

And using the alternative syntax this becomes:

<?php if ($showthis): ?>

some html stuff here
blah

<?php else: ?>

some other stuff
fubar

<?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.

Tuesday, March 24, 2009

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.

Monday, March 23, 2009

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

Friday, March 20, 2009

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.

Thursday, March 19, 2009

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.

Wednesday, March 18, 2009

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.

Tuesday, March 17, 2009

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.

Monday, March 16, 2009

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.

Friday, March 13, 2009

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! "; 
// Concatenation slows down the process 
// because PHP must add strings together

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

echo "Hello" , "World!" , " "; 
// 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. :)

Thursday, March 12, 2009

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.

Wednesday, March 11, 2009

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');

Tuesday, March 10, 2009

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.

Monday, March 9, 2009

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.

Friday, March 6, 2009

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

PHP/MySQL tip of the day