Using MySQL Databases with PHP | Conceptualizing a Physical Model

| 1 Comment

[ Today's entry could be subtited » The Database Room. ]

Easy to confuse the term 'database' with the software that runs one .. which is actually a 'RDBMS' » Relational DataBase Management System. I've used the term 'database' myself (many times) when what I actually meant was » RDBMS.

MySQL Relational Database Management SystemOkay to use the term 'database' generically, I suppose (.. to refer to its parent software), long as you're aware you're doing it. (Which I wasn't.)

Heck, even MySQL refers to the software it produces as a 'database.' (But it's actually a RDBMS.)

Interesting how sharply the (conceptual) line cuts .. where none existed only a short while ago. Cool to watch programming concepts take form.

A 'database' is a simply collection of tables .. similar to what you find in a spreadsheet. Whereas an RDBMS is the software that powers/runs the actual database.

Of course, I am FAR from being a database guru. (N00b is more like it.) But from what I can see, DATABASES look pretty cool (*).

[ I don't know much about RDBMS's, other than the software seems to function similar to the way a web server works, such as » Apache or LiteSpeed.

The 'relational' part seems to refer to TABLES, which can be 'related' to one another, in any way you like, but only when you explicitly specify the relation.

The database for the Drupal CMS, for example, contains 68 'related' tables. Bigger and more complex applications tend to have more tables. ]

(*) My background contains some mechanical engineering, so that's what I tend to use as a conceptual point of reference. It happens on its own. Here's what I see (» a physical analogy):

»» The Database Room

A good-sized ROOM. I call this » The Database Room. It's empty except for BOOKCASES .. on 3 of the 4 walls .. BOOKCASES that run both wall-to-wall & floor-to-ceiling .. all divided into many perfectly square little cubes.

The only other thing in the room sits mounted to a circular pedestal in the center » a big, shiny, mechanical ARM (made of Inconel stainless-steel) .. which both deposits-items-into & retrieves-items-from the bookcase .. which it does both quickly & efficiently.

Running into the room from the outside is a conveyor belt. (Black, of course.) The conveyor runs both to & from the big mechanical arm. Deftly the arm grabs packages from the conveyor and places them into their designated boxes/cubes (.. in the bookcase). It also retrieves requested packages from other cubes and places them on the outgoing conveyor.

••• today's entry continues here below •••

The tops & left-sides of all bookcases (tables) are labeled as part of a grid (.. like a spreadsheet). Columns go up & down. Horizontal rows are called 'records'.

Each wall (bookcase) is a 'table'. A table can contain as many columns & rows (records) as you like. Each table has a different name (.. located at the very top, centered), as does the label for each column heading.

The walls spin around (like in a scary movie) such that you can have as many different tables as you want. Magical gnomes on the other side of the walls/bookcases swap out tables often as necessary.

[ This is admittedly the hokiest part of my analogy .. point being » there are no limits to the number of tables a database can contain. At least, none you'll bang up against in this lifetime. ] 

Each database gets its own room. Different room = different database. And there's a sign on the door, which is the NAME of the database. The password is the key that opens the door to the database room.

Every database has a KEY (password), and programmers take steps to ensure bad people can't get 'hold' of it (.. such as protecting with .htaccess files).

The language we use to instruct this mechanical arm .. is called ( drum-roll ) » SQL ("ess-kyu-ell" or "sequel") aka » Structured Query Language, which is simple, but can be used to compose some very sophsticated queries.

Learning how to get jiggy with these SQL queries seems to be a major part of learning to program with PHP. [ SQL commands are usually written in ALL_CAPS by convention (syntax), even tho SQL itself is NOT case sensitive. ]

PHPCool thing about the PHP scripting language is that it will pass your SQL commands to your MySQL database (.. using the mysql_query command), and retrieve the results (.. using the mysql_fetch_array command).

Very cool, indeed. This PHP does remarkably well. Almost as if it were created for this very purpose (.. of working with databases).

If my analogy is valid, the actuator arm = the RDBMS, and the conveyor belt = PHP, or the part of PHP that shuttles SQL code into and query RESULTS out-of the RDBMS. The bookcase-covered rotating walls manned by gnarly gnomes = TABLES, which, when taken together = your DATABASE.

[ If I've made any GCE's, pls lemme know. (gce = gross conceptual error.) ]

This idea, of how a database works, just popped into my head a few days ago .. much in the way most anything you study becomes increasingly clear and begins to take form. It's like you can almost reach out and touch it some times.

Used to be (for me) a DATABASE was a tiny point, a speck .. way 'over there.' A mysterious little speck in the distance. Now it's a room I can walk into. =)

Using both PHP and MySQL together is definitely more complicated than using either program separately. In nuclear terms, putting two things together is called .. fusion. It's what the sun uses to create heat (.. energy we can feel some 93 million miles away).

I'm starting to get a glimpse of the power available and it seems unlimited, for all intents & purposes.

[ Reactors btw, use fission, which is the process of breaking a single atom into 2 smaller parts. Fusion (the sun) delivers far more energy than fission, yet requires an enormous energy input to get the process going. ]

Interesting how websites that employ a combination of both PHP & MySQL .. are usually termed 'database-driven' .. suggesting the 'database' part (MySQL) is more important than the PHP part.

I've previously had the impression that a major part of PHP's job is to shuttle SQL code (queries) to a database and retrieve (fetch) its results. All else seems secondary.

»» Nectar Day

Thursday was one of those picture perfect days of summer .. for which southern California is famous. Back when I lived down in San Clemente (not far from here), the city's motto was » Worlds Best Climate. Yeah.

Even by SoCal standards, Thursday was a delicious day. Felt like God was smiling. Everybody was in a good mood. Mighta been the cherriest day ever (.. I had the Bug.) .. and that includes the years I lived in Hawaii.

Fancy Pants»» Little Hackers

The kids have almost defeated Fancy Pants 2. They simply have to kill the 'bad bunny' at the very end, and they win.

But the bad bunny (who stole their ice cream cone at the beginning) does not die easily. No.

Have to kick the snail shell into the bunny .. to kill him. But if the bunny jumps on you, it's very bad.

Funny how a big group of kids will all want to play the same game, so they can work together .. to help each other.

I'm talking about what I see at the library (.. where they have a dozen PCs dedicated solely for kids' use).

The kids help each other. When one gets stuck, others comes to his aid. Older kids help younger ones. Remarkable to see in action. The hacker-like, goal-oriented cooperation .. at such a budding young age. =)

1 Comment

You're right that MySQL is an RDBMS, it's also worth bearing in mind that there are other kinds of database organization - databases have a rich history.

Early on there were hierarchical database systems like IBM's IMS and generalizations of the idea to what were called "navigational" databases; in these, data is grouped in units called "records" that could have complex internal structure (repeated sub-records and such) but the most important thing was how there were links (parent/child, or before/after, which were how programs "navigated") between records. This was the style of things that the COBOL programming language was built for handling; however, you needed to meticulously plan things because record structures couldn't be changed later on, and to do anything with them required writing code.

It was later on that Edgar Codd created the Relational Model (changing from "records" into a more restricted structure with tables and rows) and with Chris Date developed the idea of SQL, based in no small part on ideas from mathematics (sets, and algebra for working with sets). SQL systems generally allowed tables to evolve (adding new columns) and the data could be inspected by writing a query that could be interpreted rather than having to write COBOL code, so this was a big step forward.

Now, a great deal of web-based software has moved on from that too, to more free-form stores for data that aren't organized in tables and don't have any kind of pre-planned structure (these are generally called key-value stores); Amazon's SimpleDB and Google's BigTable are examples of hugely powerful systems for this, which give up some of the elements of the 70's RDMBS model to achieve global scale and massive parallelism.

Each of these generations requires significantly more compute power than the last to really work, so we couldn't have skipped any of them, and only today do we have so much cheap bandwidth that it's practical to just throw your data at Google, Amazon or Yahoo! and let them handle it all for you.

> I've previously had the impression that a major part of PHP's job is to shuttle SQL code (queries) to a database and retrieve (fetch) its results.

Pretty much, and this basic need has been with us for over 50 years: that was pretty much 99% of what COBOL programs were doing in the 1960's, and most business software development for the last 50 years consisted of wrangling data through its lifecycle: creating data records, retrieving them, updating them, and deleting them, leading to the wonderful acronym CRUD for this style of thing.

Of course, you can do much *more* than just this with data, but it's the bread and butter of computing and a good thing to get familiar with these. So, the stuff you're getting your head around here will serve you in good stead for a long time; understanding RDBMS's is still very useful foundation for knowing how to effectively employ things like Google App Engine, even though they are of a later generation.


About this Entry

This page contains a single entry by Rad published on August 1, 2010 8:01 AM.

Red Eye Removal & The Bug's Return was the previous entry in this blog.

Court today | "You're free to go." is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.