Advanced PHP Introduction to Databases

Hello again, and welcome back to Educator.com's Advanced PHP with MySQL course.0000

Today's lesson is a rather exciting lesson, because we are going to be writing an introduction to databases,0005

which is going to lead us down the path towards integrating a MySQL database with our PHP application,0010

which is one of the main goals of this course.0014

As part of our lesson today, we are going to be discussing a concept known as flat files,0018

which is a way of maintaining data for a particular web application.0022

We are going to introduce the concept of relational databases, which is what MySQL is.0027

As far as our relational databases go, we are going to talk about what a table is, and some of the properties of columns in relational database tables.0033

We are going to introduce something you may have heard about before, called SQL.0043

It is a structured query language; we are going to talk about what that is, and how it is used to work with databases.0047

And then, we are finally going to finish up with a rather important topic, called primary keys,0055

that describes some types of data that are going to be included in our tables,0060

and then provides some primary key examples to emphasize the point.0065

In general, a database is just an organized collection of data that you can access.0071

You can add information to it, and then you can search it, or in database parlance, you can 'query' it, to find out information.0076

In our web store, we have had a database that we have kind of referred to as a database,0084

but not so much specifically calling a database.0089

And what we have been doing is storing all of our data in text files, which are often referred to as flat files.0094

So, it is considered a flat file database.0101

And these text files that we have--items.txt and departments.txt--contain all of the data about the items and departments in our store.0104

And it is essentially our database.0111

If we look at items.txt, for example, what it does is, as we know, describes the item ID of a particular item;0114

it has its name, its cost, its description, image extension, and so forth.0123

And so, we have been storing all of this information in a database that is just composed of files.0128

Well, there are a couple of problems associated with using flat files to the database that relational databases are going to help to solve.0136

First of all, it is typically slow to access and add and search information from text files.0143

As mentioned, they are difficult to search; and to do things such as updating a file is difficult, as well.0151

For example, in our current application, when we update a department, what we are doing is copying all the file out;0158

we are searching for the row that we need to update.0166

We update that row, and then we rewrite all of those rows back to the file,0168

which is not a very efficient way to do things, and can take time, and is difficult.0171

Additionally, concurrent access to flat files can be a problem.0179

If you have maybe two web users that are logged in at the same time that are trying to add an item to the store, for example,0182

they both have to have access to that items.txt file.0189

And so, we haven't talked too much about it, because our relational database is going to take care of that;0192

but you have to worry about such things as file locking issues,0197

so that multiple people can access the file at the same time without overwriting each other's data.0200

Our relational database is going to help to solve some of these problems.0206

Our relational database is a different kind of database, and it stores data, and relations0212

between the different pieces of data in the database, in structures called tables.0220

It stores things in tabular format.0227

And what is known as a relational database system, or RDBMS, is a system that provides an interface for managing and using a relational database.0232

A relational database is comprised of all the tables that contain the information in the database.0243

The RDBMS provides additional features on top of that.0248

It provides an interface for accessing those tables, adding and deleting information in the database.0252

It also provides ways of providing security on them.0258

It provides things to allow concurrent access to the data.0263

And so, MySQL is a relational database management system, and so it provides these additional features.0267

So, not only are we using a relational database, but we are using this management system on the outside0274

that provides this nice interface that allows us to do different things.0278

RDBMS's solve many of the problems of flat files, one of the first being that they are faster than flat files for both storing and retrieving data.0284

We talked a little bit, just a minute ago, about searching for data in a flat file.0292

But actually, when we need to look up an item in the store, we need to go through line-by-line,0297

each line in the file, looking for a particular item.0302

Relational databases are made for doing that in a fast and efficient manner, so it is going to increase the speed with which we can look things up.0305

Additionally, part of the service that relational database management systems provide is: they handle concurrent access automatically.0312

So, they allow multiple users to connect to the database to read and write.0321

And they ensure that one person's changes don't overwrite another person's changes.0325

In our flat file database system, if we really wanted to ensure a system that allows multiple people to access it at the same time,0330

we would have to write our own code to do that.0338

And it is rather difficult to do, and we basically would have to manage it on our own.0341

So, using a relational database eliminates us from having to even worry about concurrent access, as the database handles that automatically.0345

The other thing that and RDBMS provides is typically a privilege system for controlling access to the data.0353

So, for right now, our flat files can be accessed by anybody that has operating system permissions on those files.0359

Well, what you can do with the RDBMS is have fine-grain control over your data.0367

You add users to the database system that restricts who can access what parts of the data--0372

For example, who can access certain tables of data, who can perform certain operations,0383

who can add things to the database, who can only query from the database, and so forth.0387

And the nice thing about that is: again, this is something that is built into the database management system.0393

So, it is not something that we have to implement.0399

If we wanted to implement that on our own, outside of the operating system privileges that provide basic access control for files,0401

we would have to implement that in PHP and validate a particular user and say,0410

"OK, this user can access that file; this user can update this file."0413

Well, the relational database management system does that for you automatically.0418

One thing to note is that you will hear me say RDBMS, or relational database; and I sort of use those terms interchangeably,0423

which is a common thing to do; so just so you know, any time I refer to an RDBMS or relational database, I am referring to the same thing.0431

I will refer to a MySQL database, and in this case I am referring to the whole database management system,0439

which provides these things--like the privilege system, facility for concurrent access, and so forth.0445

Also, for the remainder of this course, when I just use the term 'database,' I am referring to a relational database,0452

because that is what MySQL provides, and that is what we are going to be using for the rest of the course.0458

Relational databases store data in structures called tables, which organize data into rows and columns.0465

If you have ever used a spreadsheet before, it is pretty much the same exact thing.0473

What you have is defined columns that describe the types of data that rows in the table should provide.0478

And what each row is--it is known as a record.0487

And it is a set of values that provide values for all of the different columns.0492

It would make it easier to explain if I had a concrete example.0499

Let's say we have a table called Customers, which is used to store the names of all our customers for our fictitious web store.0501

We can define a couple of columns: one column is called firstName, and we are going to use that to store the first name of our customer;0509

we have a column lastName that stores the last name, and a column middleInitial that stores the middle initial of the customer.0516

So then, what we have is: that defines the structure of this table.0522

It defines the different columns, and the different data that is in this table.0527

And then, what we do is add rows to the table that represent these real-world objects, these customer objects.0531

Then, customers have a first name, a last name, and an initial, and so for each row, we provide values for the first name, the last name, and the middle initial.0537

For example, this first row of the database represents the customer Joe T. Smith.0545

We can add as many of these as we need to for each customer in the store.0550

And that is how the table organizes data: rows provide values to the data defined by the columns.0554

Now, columns in tables have at least two properties.0565

The first one is a name (which is what we saw in the last one: we had the firstName column,0571

the lastName column, and the middleInitial) that can identify the data that is stored there.0575

They also, like PHP (we have variables that have data types, integers, floats, and so forth)--columns have an associated data type with them.0582

For example, for our firstName column (we are going to learn more about the specifics later),0591

that would be a string data type, because it contains text.0600

So, that first column in our database has a name equal to firstName, and then it has a data type associated, which is string,0604

which means that every row in that table, to store a value in this firstName column, for example--you need to provide a string for it.0611

You provide Joe, for example, and so forth.0620

You can have other things, like numbers; maybe you would have a birthDate column.0624

And there is a special data type for dates and times, in which you could store Joe's birth date, for example.0631

Columns have both names and data types associated with them.0641

Now, I want to talk about SQL; this is something you may have heard before.0648

It is definitely something that is directly associated with relational databases.0651

What it stands for is Structured Query Language, which is not particularly important, as much as what it does.0657

And what it is: it is a language to work with relational databases.0664

It can be used in several different ways.0669

It can be used to define the structures in a database and the tables in a database.0671

For example, it might say, "OK, I have this database that has five tables."0675

"One table represents customers; it has these columns. One table represents orders and has these different columns," and so forth.0679

It provides a way to, in database terms, insert, update, and delete data in the database.0688

You can insert rows in the database; you can delete a row from the database.0695

For example, you could delete a customer from the database.0699

And you can update it; for example, maybe you have an item, and you want to update its price.0702

You could update that in the database, using what is known as a sequel update command.0705

And by the way, you can refer to it as 'sequel' or SQL; people call it different things.0710

The other thing that you can do with SQL is query (in database terms, that is another name for requesting or searching a database)0716

the database for specific pieces of information.0726

For example, you might say, "Give me the address of customer Joe Smith."0729

Maybe you have that stored in your database, and then you run this query--this SQL command--on this relational database.0733

It understands that command, and then it is able to provide that information back to you.0740

The other thing that SQL provides is several standard data types for data that can be stored in a database.0744

As mentioned, the columns in tables have data types associated with them.0753

For example, our firstName column contains string data, and our birthDate column would contain date/time data types.0758

Well, SQL provides a couple of different data types for all of Integers.0767

It provides a couple of types for floating-point numbers, a couple of different data types related to strings,0774

and a couple of data types related to storing dates and times.0781

And we are not going to talk about the specific data types now; but as we build up our web application0785

and start introducing SQL commands, we are going to talk about these data types specifically, as they come up.0791

I want to finish up this lesson with an important topic known as primary keys.0800

One of the main things that you do with a database is: after you add information to it, you query the database; you request information from it.0807

We have to have a way to look up data in the database: primary keys are a means of doing that.0813

For example, let's say we want to look up customer Joe Smith's address in the database.0820

Maybe he is a row in this Customers table.0826

Well, in order to do that, we have to have a way to uniquely identify Joe Smith.0830

And a piece of data that can be used to uniquely identify a table row is known as a primary key.0835

For example, we couldn't necessarily use the name Joe as a primary key, because you might have other people named Joe in your database.0844

So, when you query the database and say, "Give me the address associated with Joe,"0850

it is not going to be able to uniquely identify it.0854

What a primary key is: it is something that has to be unique.0859

For example, something in the real world that is unique is a Social Security number.0861

Each person only has one Social Security number, so that could be a piece of data that you could add.0866

It can be a column that you could add to your database, socialSecurityNumber.0871

That is not something you would probably want to do, but it is just as an educational example.0874

And what that would do is: you could look up a database and say, "Give me the row associated with Social Security...."0878

And if we know that that is Joe's Social Security Number, the database can return the information associated with Joe.0885

It is a way to uniquely identify a particular row in a table of the database.0890

And what you can do is note columns of a table as primary key columns.0896

What that says: for example, if we had a Social Security number column, we would tell the database, "This is a primary key column."0902

And what that means is that, for every row that is in that table, a value can only exist one time.0908

For example, you can't have two rows that have the same Social Security number; that defeats the point of a primary key.0917

And so, what you do is define it as part of the database (and we are going to learn more about how to specifically do that0924

when we create tables) to say, "This column represents a primary key, so ensure that a row isn't added0928

that tries to add a value for this primaryKey column that is a repeat of another one in the table."0936

When we talk about a table's primary key (which is a term you will hear used),0945

we are referring to the column whose values serve as the primary keys for the rows.0950

For example, if we were using socialSecurityNumber, we would say, "The primary key of table Customers is socialSecurityNumber."0956

Another unique thing is that books have ISBN numbers, which uniquely identify a book.0965

If we had a table of books, we could say, "The primary key for that table is ISBN."0969

And we might have a column in the table called ISBN, along with maybe the name of the book, and so forth.0974

And so, the key thing to note is that there is a one-to-one correspondence between a primary key and a row in the database.0983

You can't have one primary key value that links to multiple rows in a database.0989

In that case, it wouldn't be a primary key.0994

For example, to explain a little further what we were just talking about, let's say we had our three customers in the database from before.0998

But I have changed some of the data here.1006

So, let's say we wanted to look up Joe Smith; let's assume this column wasn't here, for now--we just had our three columns, as before.1007

If you want to look up Joe Smith (maybe we had additional columns over here that had address information,1019

so we want to find Joe's address), we say, "OK, give me Joe Smith's address."1025

Well, how could we do that? We couldn't say, "Give me Joe's address," because there is another Joe in the table.1030

Well, let's try his last name; we could say, "Well, give me the person whose last name is Smith--their address."1036

Well, that doesn't work, because we have a Jane Smith also.1041

Likewise, we have a doubled-up middle initial, so we couldn't say, "Give me the address of the person with middle initial D."1044

However, if we add our primary key column, socialSecurityNumber, which is a one-to-one correspondence1053

between a person (or a customer) and that ID, we could say,1060

"Give me the address of the customer with Social Security number 123456789."1065

And then, our database is going to be able to go and look it up in this table, and find this exact row right here,1071

and pull up any of the information in that row, as needed.1077

One thing to note is that this is called a table schema--it is a way of describing a table,1082

and often you will see a table described by its column names.1089

For example, this table has four columns: firstName, lastName, middleInitial, and socialSecurityNumber.1093

Typically, when you see column names listed, if it is a primary key, it is underlined.1098

That is something that you might see out there in literature and documents on the Web1102

that you come upon that talk about the structures of relational databases.1108

If something is underlined, that typically means it is a primary key.1113

Another option (because let's say we don't want to store Social Security numbers, which you probably don't,1117

for privacy reasons)--another thing that you can do--is just to randomly generate a unique ID every time you add a user to the database.1122

For example, when we add Joe T. Smith to the database, we can assign him the number 1.1131

When we add Jane D. Smith to the database, we can assign her the number 2.1137

And we have defined a new column here; instead of Social Security number, we have defined a primary key column called customerID.1142

What that means is that each row in this particular table has to have a unique value for it.1149

And then maybe we add Joe D. Simpson, and we assign him number 3.1156

The way you can do that is: you can manually do it--you can check the database, and one way to do it is just continually incrementing a number.1159

So, we could say, "What was the last person added to the database--what is their customer ID?"1167

Well, Joe Simpson had a customer ID of 3; "OK, when I add a new user, I am going to create a new user with customer ID 4."1171

So now, that ensures that each row has a unique customer ID, and it has a primary key to associate with it.1184

When I want to look up Jane Smith, I will say, "Give me the customer information associated with customer ID 2."1190

And so, this customer ID doesn't have a real-world analog, whereas Social Security number does.1198

There are some things in the world that you can use to model real-world objects,1203

like Social Security numbers and ISBN numbers, that can be used to uniquely identify things.1207

But oftentimes (and what we are going to be using in all of our examples in this course),1212

we will just be using randomly generated numbers, because that is something that we can ensure will always be unique.1216

One thing that you want to note is that if, let's say, you don't use something like this,1223

where you are continually incrementing a number each time a person is added to the database.1228

You want to make sure that, if you don't do that, and you define a primary key column like Social Security number,1234

that that is something that will ensure that any future users added to the database won't have multiple variables.1239

We know that each person (at least in the United States) has an individual Social Security number.1246

So, we know that any time we add a new person in the database, that Social Security number is not going to come up.1251

So, that reason is why we couldn't use something like last name, because there might be another Smith we add to the database.1257

And so, when you pick your primary key column, you want think about the future, as well,1266

and make sure that this is something that is always going to be unique, even in the future.1271

That ends today's discussion on the introduction of databases.1277

Thank you for watching today's lesson; I look forward to seeing you next time.1281