Basics of databases - Tables, fields and records

With the databases we will ask the interpreter program of PHP to act as an "intermediary" between the database and our pages that show or provide this data (depending on which way we circulate them).

In the same way as with text files, we have many predefined PHP functions, capable of opening a connection with a database, writing or reading data stored there, transferring them to a matrix, and other very practical functions that they facilitate us the interaction with the information that we store in the database

Difference between text files and databases: the SQL language

The main difference between storing the information in a text file and doing it in a database is the structuring and subsequent manipulation that we can do with the data.

It is much easier to work with rows that have several columns in a database, than in a text file; it is also easier to search and find certain ordered rows, whose value in some of their fields complies with a certain condition within a database; since access is faster and safer, and you can manage a large (huge) volume of data more easily.

On the other hand, in a text file, the most we can do is use a character as a separator between data and data (a rudimentary application of the concept of fields or columns of the databases), and while our only possible operations are : "add" a few characters or a line at the beginning or end of the file; "replace" the complete content of the file; "read" sequentially a number of characters or the complete file; all operations based on the physical location of the data (we need to know in which line is the data we are looking for); in a database, we have many more tools provided by the base management software (in our case, the program called MySQL)

The main differentiating tool offered by a database manager is its ability to interpret a declarative language that allows the execution of various operations on a database. In the case of MySQL, the declarative language is called SQL (Structured Query Languege or Structured Query Language).

Thanks to this language, we express that exact data we need in a declarative form, close to the natural language we use to talk among people (although we must use specific words of the English language), and it will be the manager software (MySQL) that will take care of those "physical" operations on the file that stores the data, freeing us from the tedious task of having to program at that low level of file manipulation, line by line, inside the hard disk.

We express a phrase similar to the following: "Bring the list of products that belong to the ordered household category of lowest price at the highest price", and the MySQL software will do all the rest of the work for us.

Why do we need to deal with database on our website?

Because practically all medium or large websites, all portals, news sites that are updated instantly and social networks use them to store the contents of their pages, content produced by people located anywhere in the world, which they add to the database by means of a form, and that, without any designer needing to format them, they are published instantaneously.

This "magic" is possible thanks to the databases (and the SQL language).

Other very common uses are: search within the contents of those pages, store the data of registered users in a site, collect their options through surveys, save messages from a forum, comments left in a guestbook or a blog, show products of a catalog of electronic commerce, the daily activities of an agenda, the contacts of an address book, the elements of a portal, a virtual campus, and an almost infinite etc.

These uses apply the functionalities that we can include in our websites, opening new markets, allowing us to offer services that are impossible without a database.

Programs that we use

The new programs involved will be, in our case, two:

1. 1. The program manager of databases called MySQL (a program that although originally works through command lines, we never use it in this way); Y,
2. 2. Precisely, to facilitate the interaction with the previous program, we use an interface or series of screens where we can interact with the database from visual tools; we will use phpMyAdmin, although we can also investigate MySQL front; MySQL Administrator, HeidiSQL, or any other visual interface for MySQL.

Both programs (MySQL and phpMyAdmin) are already installed if we have used an installer such as XAMPP, EASYPHP, APPSERV or similar.

Through these two programs, what we are actually executing are queries written in SQL language.

Fundamental concepts: base, table, register and field

Let's agree with the vocabulary: a base is not the same as a table, nor is a record a field. Let's see the differences.

Database

Our MySQL database manager will allow us to create as many databases as we need projects (and as a space we have in our hard drive, or in the hosting that we use).

Conceptually, a database is a "package" that contains all the information necessary for the operation of a complete system or project.

For example, a database called "store" can store all the data of an electronic commerce system (including data on products, sales, inventory, billing, forms of payment, shipping forms, etc.).), each database is a warehouse where information about a complete set of related information is stored, necessary for a complete system to work.

Physically, each new database in MySQL creates a directory or folder that will contain the data files of that database. As it happens in the relationship between folders and files, by itself, the database does not mean anything, it is similar to a database. folder, a simple container.

What keeps the information are the objects that it has stored inside. Well: the information within the database is stored in tables (the databases are simply sets of tables).

Tables

Returning to the example of a "store" database, this database could contain the following tables:

• Product
• Categories
• Users
• Orders
• Shipments
• Payments

That is, each of the concepts on which we need to save data will correspond to a table. For example, we will create a table for the "products", since the information we have about each product shares the same structure: we will have a name for each product, a description, a price, an available quantity, etc.

All the contents of the same table must share the same structure.

Fields

The structure of a table is defined by the number of fields in which we divide the information stored.

The possible fields (we can imagine them as "columns" of a spreadsheet) for a table of -for example- "products", could be the product code, the name of the product, its description, the category to which they belong, the price, etc.

Each field will have a defined data type that will limit what can be stored in it (numeric data, alphanumeric data, date, etc.) and we will also define a maximum length for each field (the "width" of the column, following the example of a return); that is, the maximum number of characters that we prevent storing in that field.

Records

Each item in this table (each "product", in the previous example) will be stored in a record (a horizontal row, a row)

We change the example, and see the elements of a table dedicated to store "messages" that users sent using a format:

id	name	email	message
1	Juan Pérez	juan@perez.com	¡Hello friends!
2	Carlos García	carlosgarcia@hotmail.com	Greetings from America
3	María González	mgonzalez@gmail.com	I like PHP

What we see in the first row (the titles in bold) represent what would be the structure of the table: in this case, what information will be stored relative to each "message" (since we are before a table called "messages").

In the example, we have decided to structure the information in four columns: "id", "name", "email" and "message".

These columns are called fields (it says: the "id" field, the "name" field, the "email" field and the "message" field):

id

name

email

message

Then, each row (horizontal) represents a complete data or a record, that is, the sum of all the fields (the complete information that is available) on one of the received "messages" and on one of the objects about which we store information.

Row 1 (the first record) contains the data of the first message (Perez's):

1

Juan Pérez

juan@perez.com

¡Hello friends!

Row 2 (the second record) has the data of the Garcia message:

2

Carlos García

carlosgarcia@hotmail.com

Greetings from America

And so on.

So, insummary:

• A database contains one or more tables
• A table is structured in fields (columns)
• Each row or line is called a record

We will fix these concepts as we continue to exercise them.