|
|
Introduction to SQL (and MySQL)A Hands-on How-toSMfrom Brass Cannon ConsultingA little vague handwaving can often save hours of tedious explanation. |
New Math came along when I was about halfway through grade school back in the 1960's. It introduced a novel idea, the idea of "sets." This turns out to be a very powerful concept, and a useful one.
Imagine you have a tall stack of paper, with line after line describing the items in a grocery store. Every item in the whole store has a line on that huge pile of paper. What kind of computer program would it take just to list all the different categories of items?
If you learned to program in BASIC you'd think about it in terms of counters and fields and arrays. Read a row, copy it to an array; perhaps try to figure out whether we had already seen it or not. If the array were big enough to hold all the rows, you might wait until the end and then sort the array, and throw away the duplicates. If you couldn't hold that many rows, you'd have to look for the duplicates before you saved them.
Or, you could use a database that works on the principle of sets, and Structured Query Language. Here's the SQL solution:
SELECT DISTINCT dept FROM store;
That's it. That's ALL of it. One line. Do I have your interest yet?
We pronounce SQL as "sequel," so it's "a SQL", not "an S.Q.L." A SQL database is a collection of named tables. Each table has rows and columns. A row is very much like an old computer punchcard. Any given table needs to have the same layout for all its rows -- they all have the same columns, in other words. If you need a different set of columns, that means a new table.
If a row has a column that has no content, we still need to have the same number of columns as all the other rows. To preserve the row layout, we say that the empty column has the special value NULL. The database can be asked to return rows where a column is or is not NULL, just as it can find rows with certain values. A database works much better when it does not have to scan all of its rows, of course. Part of the "magic" of a database comes about when the database architect creates an index on certain columns, so that "interesting" rows can be found more quickly.
We also need a way to identify each row. Two rows that have the same value in every column would be the same row. If all else fails, we would add an extra column and number the rows so that each row would be unique. In practice, that is seldom necessary, however. Usually we can specify a few columns, and the values in those columns are enough to uniquely identify each row. We call this set of columns the key. Given the key you can find a unique row in the table.
More "magic" happens when you ask SQL to relate two (or more) tables to each other. In fact, this is why a SQL database is called a relational database. The power of SQL comes from our ability to define a key for more than one table at the same time. Instead of typing the entire department into each record, we can type a short code, a key, and use that to point to another table, where we have codes and descriptions. This would be a modest savings in disk space, but it is also a major saving in programmer time, because the SQL code to bring the code and description together is trivial compared to the code necessary to look up descriptions in a "procedural" language such as BASIC or COBOL.
Here are two tables -- note that they both have a field called 'SKU' for Stock Keeping Unit. Each item that bears the same SKU is to be treated the same. Different SKU, different product. Same SKU, same product.
The SKU is an example of a key -- we can use it to identify a unique row in our "books" database. For instance, if we sell a copy of Atlas Shrugged, the SKU should tell us not only the title, but whether it was hardbound or paperback. Note, though, that we don't have to have a row in our database for each individual copy of the book. If we have five copies of the same title and format on our shelf, or a hundred, we don't care which of them we sell to a certain customer, any more than we care whether she pays by giving us two tens or a twenty. They are interchangeable with each other, and so they will have the same SKU. They can be represented by one row in the database, just by having a column to store the number of copies on hand. We can call that number "instock":
DESCRIBE books; sku varchar(32) title varchar(96) author varchar(64) format char(1) price float instock tinyint
Let's say we want to put a few books into a shopping cart -- just to be different, we will call our cart a "book bag." The book bags will be saved in a new table. Why? Because a shopping session is not like a book. The columns that describe a book do not describe a book bag. Each book bag is given a unique ID, something that the PHP programming language is happy to do for us automatically. This identifier is called a session, and for each book our customer purchases we simply add one row to the "bag" table containing her session id (sess), the SKU (sku) for that book, and the number or quantity she is buying (qty):
Our "book bag" table also refers to the sku, however its key is the session ID, which we'll call "sess":
DESCRIBE bag; sku varchar(32) sess varchar(32) qty tinyint
Customer: "Hi, anybody there? I'm looking for a book about frogs." PHP: Creating a new session, ID# 12345... SELECT * FROM books WHERE title LIKE 'frog'; sku title author format price instock ------ ------------------ ---------------- ----- ----- ------- abd123 My Life as a Frog Charming,Prince P 12.75 14 Customer: "Okay, I'll take one of that book." PHP: INSERT INTO bag VALUES sess='12345', sku='abd123', qty='1'; Customer: "And what about that tell-all book by Snow White?" PHP: SELECT * FROM books WHERE author = "White,Snow"; sku title author format price instock ------ ------------------ ---------------- ----- ----- ------- axb454 Short Help White,Snow H 9.99 2 Customer: "Ooh! Yes, I want that one too!" PHP: INSERT INTO bag VALUES sess='12345', sku='axb454', qty='1';
Now, how do we get back the contents of the book bag?
SELECT * FROM books, bag WHERE books.sku = bag.sku AND sess = '12345'; books.sku title author price qty --------- ------------------------- ---------------- ------ ---- abd123 My Life as a Frog Charming,Prince 12.75 1 axb454 Short Help White,Snow 9.99 1
Notice that the books and bag tables have a column that is the same. We added the table name to tell "books.sku" and "bag.sku" apart. As you can probably tell, "books.sku" simply means "the sku column of the books table." Having columns in common between tables allows us to join the tables -- we join the "bag" table with the "books" table on the column named sku, and the result is the intersection set, which contains the books in our customer's book bag.
The beauty of this approach is that we didn't have to count the number of rows, or "create an array" to allocate space to store them, or do any of the things that COBOL (or other programming languages) would demand of us. We just toss the orders into the book bag, and that single line of SQL is all we need to get them back out again when we want them.
This "non-procedural" approach comes much more naturally to some people than others. It's not easy for some programmers to shift gears between the relational model and the procedural "loop through the file" model. If you haven't been corrupted by the procedural way of thinking, you may find that SQL is the most natural way to work with data.
Of course, we still have to bridge the gap between our database and our webserver. Fortunately, as you've seen, there is a remarkably easy-to-use solution: PHP. Generating "sessions" is just one of the handy things PHP does. We happen to have a fairly complete working example over at SimpleShop.org, which gives more practical examples of using SQL statements from within PHP.
You are invited to discuss this article with the author on the Brass Cannon webboard.