MySQL and SQL Database and Language PHP Help

Where is this supposed to go?
It’s a question you ask every day. Where do these shoes go? Where does this new box of books go? Where do these receipts go? Because that’s such a common question, it shouldn’t surprise you too much that when you’re building web applications, you need to ask the same thing:

Where does my information go?

For the kinds of web applications you’ve been building with web pages and PHP, the answer to this question is simple: in a database. But what do you get out of a database that makes it worth the effort of installing another tool and learning another language? And, why does everyone seem to agree that if you’re writing PHP code, you need a database, too? Sit tight, because this chapter is about to reveal all.

What Is a Database

A database is just a repository in which you can store information, add some layer of organization to the stored information, and grab that information when it’s needed. In a literal sense, a file cabinet is a database. You can throw things into it, pull those things back out, and even use files and labels to keep your files orgao.ized

Databases Are Persistent

You’ve already seen that PHP gives you arrays (page 83) that serve as a sort of programmer’s file cabinet. An array might function as a database in a simplistic sense, but it won’t serve your needs for long. For one thing, arrays and their contents in PHP are lost every time your program stops and starts again. That’s not very helpful

A good database provides long-term storage for your information. If your program stops running, or your entire web server has to be restarted, a database doesn’t lose your information. Imagine if every time your web server had to be shut down for an upgrade, your database lost every user’s first name, last name, and email address.
Do you think your users would come back to your site if they repeatedly had to type everything in again? Not a chance.

Therefore, a good database needs to store information more permanently. In programmer jargon, this is called p!:rsislinq your information. (At times, though, even permanent information can be lost; see the box that follows for advice on backing up your information in such cases.)

Permanent Data Is Really Semi-Permanent

Even though databases store your information, and that storage lasts beyond your computer or even a database starting up and shutting down, your information is still not really permanent. Think about it: even if you write in ink instead of pencil, you can still throwaway the piece of paper you wrote on. That’s how dataoases work: they store information in a form that’s harder to destroy, but that information still (ail be destroyed.

At some point, databases have to store information somewhere, usually on hard drives. If one of those hard drives crashes or becomes defective, your information is lost. no matter how good your database is. Additionally, threats to computers like overheating or natural disasters can wipe out your data by destroying the hard drives on which it exists

Replication is expensive because you basically need another server with another copy of your database software running. Still, if you use an application extensively and it earns money as long as it’s running, replication is a really important way to ensure that information isn’t lost in a disaster.

If you think about it, you’re constantly working with something like this on your computer: a system that stores your information over a long term. It’s your hard drive and file system. The files on your computer are basically pieces of information; such as addresses, emails, your finances, or maybe what level you’ve made it to in Angry Birds. You can shut down your computer and start it back up, er even upgrade to a new computer, and all those files with all your information will still load up

In ‘other words, a file system is really a sort of database. In fact, lots of databases actually use files much like your computer does to persist information. So, why doesn’t PHP just store information in files? It actually has an entire set of tools for working with files, including creating, writing, and reading files. Isn’t that enough?

Databases Are All about Structure

If you think about it. there’s something pretty clunky about your computer’s file system. Have you ever tried to remember the last time you sent an email to someone? Your email program might not know that person’s email address. And, if you go to that person’s card in your address book program, that address book program might not be connected to your email program.

Even if you actually find the email address, you might need to reference some documents related to the email message. Where are those documents? In another folder somewhere, probably in some highly-organized structure about which you’ve long forgotten.

That’s why your computer gives you one or more ways to search for information. In Mac as X, you can use Spotlight (see Figure 4-1) or a program like Quicksilver

Databases Are All about Structure

Databases Are All about Structure

In fact, these search programs attempt to do what databases do by nature: locate and organize information. If you’ve ever tried to make these sort of connections on your computer-whether you’re using Spotlight or Google Search or doing it by hand-you know it’s a hassle and inconsistent, at best. What you need is a better way to connect two, three, or ten pieces of information together.

Good Databases Are Relational

There is one task for which a file system and your hard drive are lacking, but a database excels: creating relationships between different pieces of information. For example, you might have a person, and that person has several email addresses. phone numbers, and mailing addresses. This isn’t anything new; your address book program already handles these sorts of relationships.

But a good database goes further. An email message is related to the email address of the sender, and that email is related back to the person’s name and phone numbers and mailing address. A map with streets connects those streets to the streets used in a person’s mailing address. The creator’s name.in a file description relates to that person, and their email, and their phone, and so on.

In a lot of ways, these relationships are really a giant web of connections. A good database both creates and manages all these relationships. In fact, relationships are so integral to MySQL, FileMaker, Oracle, and other big-time databases that they’re called retauonst c’iacaoast’s. (For a technical journey into how these databases operate, see the box on page 95.

This means that in addition to instructing a database what information you want it to store for you and your programs, you also define how that information is connected to other pieces of information. Not only do you get to use this web of connections, but you specify to the database exactly how the web should be constructed. That’s a lot of power, which is why you’ll have to learn an entirely new language to work with these relational databases

Objects and Relations in Databases, Oh My!

For decades, the relational database has been the de facto standard for high-end applications, whether those applications run on the Web or on an internal company network. These database
programs-often called an RDBMS,which stands for

An RDBMS stores information in tables, rows, and columns. For example, you might have a table of users that has columns for their first name, last name, and emall.Ihis means that anything that’s stored in the RDBMS involves some kind of mapping.

This means that the information in your PHPscript has to be mapped to particular tables and columns. You’d say, for example, the

Of course, with an OODBMS,this means you have to have lots of objects in your code. so you’re going to end up writing some code whether you’re working with an RDBMSor an OODBMS. Still, the RDBMSis far, far more common in web applications than the OODBMS,which is why it’s definitely the one you want to focus on learning.

Posted on January 12, 2016 in MySQL and SQL Database and Language

Share the Story

Back to Top
Share This