== Simple SQL == SQL is pretty easy. That’s one of the reasons that it powers virtually every online app and why competitors just can’t compete. However, advanced SQL is hard to master. Unfortunately (for the world) and fortunately (for us), “advanced” SQL is required for pretty much even simplistic apps to scale under load or do anything “really awesome”.

Where most people fall apart is that they either * only know simple SQL (which can do almost anything, just horrendously poorly), or * only know (or overly rely on) one (or two) proprietary ORMs (Object Relationship Mapper).

Simple SQL revolves around retrieving a collection of rows from a single table at a time. At its most basic, it would be the equivalent of SELECT * FROM Foo; This would retrieve all the columns from a table called Foo. Traditionally, simple SQL uses the “core” SQL language keywords of SELECT, FROM, WHERE, and LIMIT.

  • SELECT says “Retrieve rows”
  • “FROM” is the table to retrieve them from
  • “WHERE” puts conditions on what to be retrieved, and
  • “LIMIT” specifies the number of rows to fetch.

Here’s a real-world example:

SELECT * FROM Employees LIMIT 2; +——————+————+———-+ | EmployeeID | Name | State | +——————+————+———-+ | 1 | Monica | NULL | | 2 | Ted | NULL | +——————+————+———-+

Now, write a SQL statement to select the employees that have an ID greater than 1.

SELECT * FROM Employees WHERE EmployeeID > 1; +——————+—————+———-+ | EmployeeID | Name | State | +——————+—————+———-+ | 2 | Ted | NULL | | 3 | Xandra | NULL | | 4 | Sally | NULL | | 5 | Jonathan | NULL | +——————+—————+———-+

Now, select the employees ordered by Name limited to the first 3.

SELECT * FROM Employees ORDER BY Name LIMIT 3; +——————+—————+———-+ | EmployeeID | Name | State | +——————+—————+———-+ | 5 | Jonathan | NULL | | 1 | Monica | NULL | | 4 | Sally | NULL | +——————+—————+———-+

What’s important to realize is that this is where virtually all PHP coders stop. What’s more is that since 2005 or 2006 (with the rise of the ORMs), a great number of programmers do not even know simple SQL. To them, it is literally a scary monster, meant to be avoided. Just google the terms “no complex SQL” and “ORM”.

== A Bit More Advanced == SQL revolves around 4 distinct types of operations. They are referred to as a CRUD operations. * Create * Read * Update * Delete

There are two major domains for CRUD operations: * Schema-level: Databases, tables * Row-level: Inside tables.

=== Row-level CRUD ===

  1. To add data to a table, you use INSERT: INSERT INTO


table> () VALUES ();

  1. To read data from a table use SELECT: SELECT FROM



  1. To update data in a table, use UPDATE: UPDATE


table> SET = WHERE ;

  1. To delete data in a table, use DELETE: DELETE FROM


table> WHERE ;

=== Schema-level CRUD === 1. To create a table, use CREATE: CREATE TABLE


table> (col1 TYPE, col2 TYPE);

There’s substantially more to it than this, but that’s the jist.

  1. To read the schema of a table, use DESC: DESC



  1. To update the table’s schema, use ALTER: ALTER TABLE


table> ;

  1. To delete the table, use DROP: DROP TABLE



  • NOTE: There is no warning/confirmation for deleting tables or databases!! And no “do-over”, either.

== ORMs == The other PHP developers (and its even worse for other languages, like C#, Python, and Java (and with Ruby, it’s systemic!)) rely on a humongous crutch. Humongous in both code size, bloat, and degree to which they depend on it. It’s called the ORM: Object Relationship Mapper. And it is the disdain of coding gurus the world over.

You will, unfortunately, have to learn several ORMs in order to be pro. Fortunately, the only thing I want you to learn in each is how to grab the raw SQL driver from the ORM, so you can run your own SQL directly. ORMs are primarily “meant” to solve a problem called the “Object-relational impedance mismatch”. This is a fancy way of saying “Objects do not match up with tables.” Tables, they say, are 2D (rows and columns); objects, they claim, are 3D. The way I’ve had it explained to me — again, and again is that a database has a Book table that stores multiple books but your Book

An ORM at a very basic level is a way to “abstract out” database manipulation so that by editing a PHP object, you also edit the underlying table of a class

User extends ORM { public $username; } +——+—————+—————+ | id | username | password | +——+—————+—————+

$user = new User(1);

Now, the User table would look like this:

+——+—————+—————+ | id | username | password | +——+—————+—————+

The problem with ORMs is that they try to be one-size-fits-all and this basically means that it is * impossible to create optimized SQL (in fact, the lowest theoretically, on any database server the ORM supports (mysql, postgresql, etc.)]) * generally impossible to include only the data you want. Have a table with 50 columns? Need only 2? You’re screwed!

Furthermore, using an ORM in a project * Retards the skills of current coders: They won’t be using SQL, so they will necessarily become dependent on the ORM. * Forces new coders to learn a new language: ORMs are totally different and are many times more complex than even advanced SQL, often times with little to no documentation (and a complete dearth of tutorials). * Generally inhibits scalability: Twitter’s pain — relying on Ruby’s defacto ORM. Digg’s pain? Relying on an ORM. Reddit: Relying on Python’s ORM.

=== Why ORMs are Used === The reasons for adopting an ORM are said to be (in order of value):

”’Allows you to run SQL on “any” server (mysql, postgres, etc.):”’ While this may (or may not) be true, sticking to the SQL standard generally avoids 90+% of the headaches of porting anyway. And, realistically, very few applications ever have a need to work on multiple databases anyway.

”’Solves the supposed ‘object-relational impedance mismatch’:”’ In reality, there is no mismatch; it’s a myth borne from unenlightened coders. Tables are inherently collections of objects. So you would have a User object that stores the data from individual rows of the Users table, and a UserManager that handles the actual SQL manipulations.

”’Easier to learn / more rapid development, because there is no SQL:”’ This is so debatable! Most of the people using ORMs have no solid OOP experience, so it is debatable whether they find it easier to learn. And any decently-skilled SQL coder can outcode an ORMer any day, in my experience, both in terms of raw time and definitely in terms of their code’s efficiency.

ORMs are considered the “holy grail” of OOP, however, and there are tons of fanatics on the pro side, and very very few objectors on the other side. A large number of projects use ORMs (drupal, wordpress, to name just a few) and it has largely caught on even more than the MVC pattern.