Storing Data with PHP — Flat File or Database?
Many applications require the long-term storage of information. In PHP scripts, you can make information available within sessions — periods of time that users spend at your Web site — by using methods such as PHP session functions and by submitting forms. However, eventually you need to store information for use tomorrow or next week. You can store it in a cookie that you set to last after the session is ended, but the information is vulnerable. It’s not under your control. The user can delete or change the information at any time or can refuse to accept the cookie. To be available and stable, the information needs to be stored somewhere secure, where no one can access or tamper with it. The information needs to be stored on the server.
Information can be stored on the server in flat files or in databases. Flat files are text files stored in the computer file system. Humans can read flat files by using the operating system commands that display files, such as cat in Linux and Unix. You can access and edit these files by using any text file editor, such as Notepad or vi. The information in the flat file is stored as strings, and the PHP script that retrieves the data needs to know how the data is stored. For example, to retrieve a customer name from a file, the PHP script needs to know that the customer name is stored in the first 20 characters of every line.
Using a database for data storage requires you to install and learn to use database software, such as MySQL or Oracle. The data is stored in files created by the database software and can only be accessed by the database software. Databases can store very complex information that you can retrieve easily. You don’t need to know how the data is stored, just how to interact with the database software. For example, to retrieve a customer name, the PHP script needs to know only how to tell the database software that it wants the customer name, using a standard communication language called SQL. The database software handles the storage and delivers the data, without the script needing to know exactly where or how the customer name is stored.
Flat files have some advantages over databases:
- Available and versatile: You can create and save data in any operating system’s file system. You don’t need to install any extra software. Additionally, text data stored in flat files can be read by a variety of software programs, such as word processors or spreadsheets.
- Easy to use: You don’t need to do any extra preparation, such as install database software, design a database, create a database, and so on. Just create the file and store the data with statements in your PHP script.
- Smaller: Flat files store data by using less disk space than databases.
A flat file is quick and easy and takes less space than a database. It is ideal for storing small amounts of information quickly, such as a simple list or small piece of information. Flat files are particularly useful for making information available to other software, such as an editing program or a spreadsheet. Flat files can be looked at by anyone with access to the computer directory where they are stored, so they are useful when information needs to be made available to other people.
Databases have some advantages as well:
- Security: A database provides a security layer of its own, in addition to the security provided by the operating system. A database protects the data from outside intrusion better than a flat file.
- Accessibility of data: You can store data in a database by using a very complex data structure, specifying data types and relationships among the data. The organization of the data makes it easy to search the data and retrieve what you need.
- Ability to handle multiple users: When many users store or access data in a single file, such as a file containing names and addresses, a database ensures that users take their turn with the file to avoid overwriting each other’s data.
Databases require more start-up effort and use more space than a flat file, but are much more suitable for handling complex information. The database handles the internal organization of the data, making data retrieval much simpler. A database provides more security, making it more suitable for sensitive, private information. Databases can more easily and efficiently handle high traffic when many users may try to access the data almost simultaneously.
In PHP 5, SQLite, an extension for data storage that combines the main advantages of flat files and databases, is included by default. SQLite stores the data in a flat file, so you don’t need to install database software, but you store data using SQL, the standard database communication language. SQLite is a quick option for storing and retrieving small amounts of data in a flat file using SQL. SQLite is not a good option for really huge, complicated databases.