PHP Tutorial - Databases in PHP, An Introduction
WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
There's little doubt that the tremendous popularity of PHP is due in large part to its ability to easily utilize a wide variety of powerful database systems. Adding a database to a website can provide the means for great dynamic content, all kinds of user interactivity and a range of flexibility and manageability that could not easily be accomplished without it. Sounds great, right? But what exactly is a database, you ask? And this is the perfect time to ask.... let's explore!
A database is a collection of data that is stored independently of the manner in which you collect it or may wish to retrieve it. It is organized for efficient storage and retrieval, based on the nature of the data, rather than the collection or retrieval methods. That makes it clear as mud, right? Let's break it down with a couple of examples.
Do you have a "little black book" -- or any good old fashioned name and address book? If not, I hope you have at least seen one! An address book like that is a collection of data doesn't really fit the definition of databases as we have come to know them. That is because it is organized according to the manner in which information will be retrieved. Not everybody organizes theirs in the same way. Some may choose to alphabetize by first name, some by last name and some even by nickname. Once chosen, however, the method of storage firmly dictates the method of retrieval. If you stored every Barnes under "B", you would have to already know my last name to find me. Of course you could always begin at the beginning and read sequentially through until you found me, but that's not very efficient -- especially in the case of looking for me as "Mr. Barnes" in a book that's organized by first name!
To compound the inefficiency, a computer would have to repeat the search from the beginning for each and every such retrieval. You might remember a few first and last name combinations as you scanned through the book, but the computer would not, unless a program was specifically written to do so.
Typically, databases store information in tables. Tables organize data into rows and columns. Each row represents a single record, such as one name and address. Each column holds a part of the record, such as a first name, or a phone number. With such an arrangement records could be rapidly resorted based on the values found in any column (or combination of columns) just like you can in a spreadsheet program. Thus you could efficiently retrieve any record based on any part of the record, regardless of how it was put into the table. Internally, database systems have technologies to make this kind of sort/retrieve extremely efficient - but that's a part of another story!
Imagine a table that holds lots of names and addresses. Now imagine another table that holds information about lots of televisions, including serial numbers, manufacturers and models. Now imagine that each television record is linked to the name and address record of the house in which the TV can be found. With these links in place it would be easy to use our sort/retrieve mechanism to provide a list of houses on Main street that have Sony TVs. Extracting information from a database in this way is known as querying the database.
The ability to query the database in this fashion is the root of its power. Implementing this power on a computer system involves, surprise, surprise -- a language! In our last example, we could create a question, or query, such as: list houses where the street name is "Main St." and the linked TV manufacturer is "Sony". Now we have a query that has some structure! That's right! Creating a set of structures for queries, and defining a simple language in which to specify those queries gives us a Structured Query Language, SQL. Using SQL is surprisingly easy, as you will soon find out.
There are plenty of database systems out there, most of which use SQL. If you are in a Microsoft Windows environment you will often find Access, the Microsoft SQL Desktop Engine (MSDE) or their granddaddy, Microsoft SQL server. When it comes to the web, however, we find MySQL all over the place. With a price tag of zero (in your currency of choice) its affordability might well explain this. MySql is available for most Unix/Linux flavors as well as for Windows. Price and availability have made it my choice for a database system to use as we continue with our PHP database examples. You will not have to change very much, however, for each axample to be applied to virtually any database system. Ah! - Another great advantage of database systems!