Connecting to a SQLite Database

[00:00:00]
>> Maximiliano Firtman: So for now, maybe the simplest thing that we can do is we can have, for now, a method that will be called, say function execute(), that receives a query and we return something. We can add data types or not, it's up to you, doesn't matter for now, it's not the important part.

[00:00:23]
And the idea is that we will try to execute the query here. So let's see how that works. First, we need to create a new SQLite3, and be careful here because SQL is capital, okay? And then the path to the file. So the path, and this is interesting, should I do this?

[00:00:48]
'../data/data.db' or not, we'll see, we'll see. By the way I didn't mention this, there are two ways to work with databases in PHP. We can use custom providers, or we can use something that is, let's say, more common in enterprise applications, it's called PDO, PHP Data Objects. What's the difference?

[00:01:20]
With custom providers, you will use directly the provider of the database you wanna use. We are doing it right now, it's SQLite3, and the API of that object, it depends on how that provider was created. Make sense? So if I wanna change to MySQL later, I need to change my constructor and maybe change the code because maybe the API is different.

[00:01:45]
PDO, it's an abstraction. It's a level of abstraction on top of the providers. We use the same API for all the providers, you just change the connection string, the connection stream will say if it's in MySQL or if it's SQLite. We can use both if you want, if you wanna take an example of both.

[00:02:06]
Does it make sense? It's an abstraction layer, so then if you more or you have to change your database engine, you don't need to change your code. Well, at least you change your code in some pieces, a moment.
>> Student: So is that to help multiple different applications write to the same database or for the PDO, what are the use case there?

[00:02:27]
>> Maximiliano Firtman: The use case is to abstract your code, to separate your code from the current database implementation that you're using. So in the future, you wanna change your database to another provider, then you don't need to change your code.
>> Student: Sure.
>> Maximiliano Firtman: Because you are using, that has advantages and disadvantages.

[00:02:46]
There is advantages that maybe you cannot talk directly to the database in its own API, so sometimes some features might not be available, or it might be restricted. But you have an abstraction layer on top, so you are kind of standardizing how you access all the databases with one API.

[00:03:06]
>> Student: Sure.
>> Maximiliano Firtman: That's PDO.
>> Student: Okay.
>> Maximiliano Firtman: PDO is actually not part of the standard library, here, you can get to the manual of PDO, PHP Data Objects. It's another library that is typically, here, you say installation, do I need to install it? Well, actually it's enabled by default, okay, so you shouldn't install anything, but maybe a hosting provider may disable that.

[00:03:31]
There is no reason for that, but it's something that you can disable, okay, you don't need to enable, okay? It's an extension, so actually, anyone can go to a configuration file and remove it, it's not part of the standard library, okay? And the other difference is that PDO is object-oriented, and the other custom providers, it depends on the provider.

[00:03:50]
You may have object versions or functional versions, okay, that's the other difference. We're going use first the custom provider. So we are going directly to the SQLite 3, and actually what we are gonna do here is to first try to make a query. So I'm not sure how confident are you with SQL, but I'm going to use the simplest possible SQL that we can do here that is 'SELECT * FROM exhibits'.

[00:04:25]
SELECT * will just bring all the fields and all the records, there is no WHERE, so there is no filter, so give me all the tables. Okay, of course you can customize that later.
>> Maximiliano Firtman: Okay, I think it's fine. Once we get this, we can talk to our database and say we want a query using that query, and that will give me a result.

[00:04:57]
Okay, something like that.
>> Maximiliano Firtman: So if it's empty, it's going to be null, so actually, I will check if we have something or not. If not, we will see what you return or if I'm going to throw an exception or I don't know, whatever. If we have something then I need to do something with it.

[00:05:19]
Okay, so here comes the interesting part. There are many ways to browse that result, we can create the cursor, and then go live, so we keep a cursor, cursor is a pointer, to the database and then we render one data and we move to the next one, we render their data, we move to the next one.

[00:05:41]
So we keep a connection to the database while we are rendering or processing the data. Or we can convert everything we have there into an array, so then we can manipulate that offline, okay? Without the connection to the database, does it make sense? So the simplest way is to get the whole array and now you work it as an array.

[00:06:04]
So for that, typically, you say, okay, all the things are from that result, we ask for fetching an array, and then you kinda specify as an argument, how do you wanna do that? And there are three modes. Do you want to receive a associated array so the index will be the column name, or do you want to receive the columns as indexes, like numbers, or do you want both?

[00:06:32]
Well, typically we use yes, the associated array, it's the most common one. So it's three, equalize three, why is everything in upper case, roes anyone know? Think it's fine, yeah, I think it's fine. This is typically a constant, and it's using a pattern coming from C, C++, where everything is uppercase, it's not a PHP thing.

[00:07:01]
The person who developed this API was using that design pattern and that's okay. So this is fetching the array, right, and now where we're going to getting return. Actually something very similar to what we have here. It's an array rows, and then we have an associative array, meaning that the key is the name of the field.

[00:07:32]
Does it make sense? So actually it's the same structure. But now we are getting the same structure from the database.
>> Maximiliano Firtman: Does that make sense? So maybe it's the one we wanna return, I don't know, we'll see. So then we return that,
>> Maximiliano Firtman: If talking to MySQL more difficult, no, it just changed the provider, MySQL, yeah, you need a username and password host, then you make a query, and you can also convert into an array.

[00:08:12]
Or you can keep the pointer and go one by one so most of the data dtabases are just the same in terms of how you call them from PHP or how you use them from from PHP. Okay, does it make sense? Okay, and by the way, I'm receiving the query as an argument, so why I'm doing this now, we can just receive it as an argument.

[00:08:36]
I think that's better. So I'm removing the line 8, because I'm already getting it from here, okay, so someone will give me the query. Mean, we can change that, it's up to you, but I think that, that should work. So now my index php should actually say, well, where are the exhibits?

[00:08:56]
>> Student: Well, the exhibits would come from the database, okay? Does it make sense? So that means that I need first the database.
>> Maximiliano Firtman: new DB() and then db execute, and now I have the 'SELECT * FROM exhibits'. I know that some exhibits. Some of you may be thinking, well, having SQL queries inside the view inside the HML doesn't look fine.

[00:09:25]
We can add layer of abstraction using OOP, a controller, but that has to do with design patterns, not with PHP, you can do wherever you want.