PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

The Ghost of Christmas Past

Note: This article was originally published at Planet PHP on 19 December 2010.
Planet PHP

PHP has been around nearly as long as there has been a Web to develop for, but it evolves constantly and is a modern programming language by almost any definition. Sure, it might not suit hipsters who only enjoy bands when they can say aoyou probably have not heard of them; they only formed tomorrow,a but it is not your granddad's programming language.

Sadly, web development often also involves SQL, and that is your granddad's programming language. SQL, and the stored procedure languages you are most likely to encounter, have deliberately not evolved much since the 70s and 80s. When your programming ancestors were hunting saber-toothed tigers and scratching proposed SQL syntax onto cave walls, they had some fundamentally different ideas to those behind scripting languages.

If you want to write good, efficient SQL for complex problems, you are going to have to learn some of that mindset, but you can go a fair way with simple SQL by avoiding some traps.

Null

There is a null concept in PHP, but it is a lot more aospeciala in databases. After you spend a few hours chasing down a bug related to a difference between values that render identically, you'll consider joining the folks rushing to NoSQL database management systems just on the basis that you'd be able to avoid SQL.

PHPa-aas you are no doubt awarea-ais loosely-typed, which means that although null has some special properties, they can easily pass you by unless you're paying attention. In PHP, null == '', null == 0, null == false and null == null all evaluate to true. That's because null is a special PHP data type that can only ever have one value, but like other PHP data types, it gets converted into other types automatically, so comparisons can happen.

In strongly-typed languages like SQL, you might expect an empty string to not equal 0 nor to equal Boolean false, and you'd be right, but maybe not in the way you expect. In SQL, null is not a type; it is a value, or more precisely, the absence of a value. Any type can be set to null. It is intended to show that a piece of data is unknown or not applicable, so the reason those comparisons fail is not because of differing type.

When comparing two variables set to '' and null, it is not the fact that they are different types that will make the comparison fail, it is that the empty string is one specific value that a string variable can hold, and null is another. Even if that seems fair and logical, it might still bite you from time to time. If you are looking at the contents of your tables, it is not always obvious that a column contains some values that are null and some that are an empty string. They'll both look exactly the same from a command line test query. In SQL, null == '', null == 0, null == false and null == null all evaluate to null. That's right, even null = null is not true, but these values are not false, either. Because null is a special value with a special meaning, two nulls are not necessarily the same. Just because two values are unknown, does not mean they are the same, but at the same time if at least one value is unknown, I don't know that they are different either. I don't know next week's lotto numbers, or those of the following week, but that does not imply they will be the same set. It is unlikely that they will be the same both weeks, but I will not be able to say for sure until both are drawn. Because nulls don't match, you can't select rows that have nulls by writing SQL like SELECT * FROM my_table WHERE foo = null. You need to write SELECT * FROM my_table WHERE foo IS NULL.

Stored procedures

There are lots of things that are great about stored procedures, but the syntax is rarely one of them. It's great to be able to keep logic close to your data, so that manipulation, validation, and integrity checking can all be done with little risk of circumvention. It's great that your database backend can have business logic shared by different parts of your system without having to share it between different programs or port it to different languages. Doing work in the database where it might get usefully optimized or cached without effort on your part is great.

The syntaxes, on the other hand, have crawled out of another era, and just like the stars of Jurassic Park, they are mean. PostgreSQL's PL/pgSQL, MySQL's ANSI SQL:2003 syntax, and Oracle's PL/SQL

Truncated by Planet PHP, read more at the original (another 5735 bytes)