Top 10 MySQL Mistakes Made by PHP Developers
A database is a fundamental component for most web applications. If you're using PHP, you're probably using MySQL - an integral part of the LAMP stack.
PHP is relatively easy and most new developers can write functional code within a few hours. However, building a solid, dependable database takes time and expertise. Here are 10 of the worst MySQL mistakes I've made (some apply to any language/database)a¦
1. Using MyISAM rather than InnoDB
MyISAM is used by default. However, unless you're creating a very simple or experimental database, it's almost certainly the wrong choice! MyISAM doesn't support foreign key constraints or transactions which are essential for data integrity. In addition, the whole table is locked whenever a record is inserted or updated: it causes a detrimental effect on performance as usage grows.
The solution is simple: use InnoDB.
2. Using PHP's mysql functions
PHP has provided MySQL library functions since day 1 (or near as makes no difference). Many applications rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc. but the PHP manual states:
If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.
mysqli, or the MySQL improved extension, has several advantages:
- an (optional) object-orientated interface
- prepared statements (which help prevent SQL-injection attacks and increase performance)
- multiple statements and transaction support.
Alternatively, you should consider PDO if you want to support multiple databases.
3. Not sanitizing user input
This can be cracked by entering aoadmin'; --a in the username field. The SQL string will equate to:SELECT userid FROM usertable WHERE username='admin';
The devious cracker can log in as aadmin' - they need not know the password because it's commented out of the SQL.
4. Not using UTF-8
Those of us in the US, UK and Australia rarely consider languages other than English. We happily complete our masterpiece only to find it cannot be used elsewhere.
UTF-8 solves many internationalization issues. Although it won't be properly supported in PHP until version 6.0, there's little to prevent you setting MySQL character sets to UTF-8.
5. Favoring PHP over SQL
When you're new to MySQL, it's tempting to solve problems in the language you know. That can lead to unnecessary and slower code. For example, rather than using MySQL's native AVG() function, you use a PHP loop to calculate an average by summing all values in a record-set.
Also, watch out for SQL queries within PHP loops. Normally, it's more effective to run a query then loop through the results.
In general, utilize the strengths of your database when analyzing data. A little SQL knowledge goes a long way.
6. Not optimizing your queries
99% of PHP performance problems will be caused by the database and a single bad SQL query can play havoc with your web application. MySQL's EXPLAIN statement, the Query Profiler, and many other tools can help you find that rogue SELECT.
7. Using the wrong data types
MySQL offers a range of numeric, string and time data types. If you're storing a date, use a DATE or DATETIME field. Using an INTEGER or STRING can
Truncated by Planet PHP, read more at the original (another 2893 bytes)