WEBVTT 00:00.590 --> 00:09.440 Okay, so after successfully inserting the data into this messages table, it's time that we fetch this 00:09.440 --> 00:13.760 data to display it on the guest book page. 00:15.950 --> 00:18.410 So that's a page we don't have right now. 00:18.410 --> 00:19.610 We need to create it. 00:19.610 --> 00:28.700 And we also need to add to the db php file a function that will be able to fetch this from the database. 00:28.700 --> 00:31.280 So let's begin with this. 00:31.370 --> 00:35.090 So we start with a select keyword. 00:35.120 --> 00:42.590 Then we need to either list the columns or use asterisk which means just select all the columns. 00:43.010 --> 00:49.460 Then we go to the from keyword saying from which table we want the data. 00:49.490 --> 00:52.010 This is called messages. 00:52.460 --> 00:55.490 And then we can add some conditions. 00:55.490 --> 01:03.570 We don't have any conditions because we want to select everything though we would like to order by so 01:03.570 --> 01:05.520 we can skip everything else. 01:05.520 --> 01:08.370 And now we get to order by. 01:08.400 --> 01:11.580 So we type order plus by. 01:11.580 --> 01:16.770 And here the column we would like to order the records by. 01:16.800 --> 01:19.890 In our case this is Createdat. 01:20.550 --> 01:28.170 And we want it in a descending order so that we get the most recent messages first. 01:28.170 --> 01:31.800 And this can be achieved by typing test. 01:31.800 --> 01:37.200 So the order comes right after the column name by which we want to order. 01:37.680 --> 01:38.010 Okay. 01:38.040 --> 01:40.500 So that's the SQL query. 01:40.530 --> 01:50.190 Now we create a statement by calling PDO query with the SQL. 01:50.610 --> 01:52.650 We don't have any arguments in here. 01:52.680 --> 01:57.570 You can also add the prepared arguments for select queries. 01:57.570 --> 02:02.040 But we don't have any here because we just want everything. 02:02.190 --> 02:06.720 And now we can just run this statement by calling. 02:06.750 --> 02:07.710 Fetch all. 02:07.740 --> 02:10.560 That's a method that will get everything. 02:10.560 --> 02:18.330 And we just pass one argument, which is how you would like to receive those results using mode. 02:18.390 --> 02:27.960 And we select fetch as SoC, which is a shortcut for fetch into an associative array, which means that 02:27.960 --> 02:35.040 we're going to get an array of associative arrays where every key is the column name and the value the 02:35.040 --> 02:36.690 value of the column. 02:38.460 --> 02:43.800 Okay, so let's think how we should call the page where we're going to display this messages. 02:43.800 --> 02:50.130 Let's go back to the page so it can be guestbook or messages. 02:51.360 --> 02:51.990 Not sure. 02:51.990 --> 02:53.070 What do you think. 02:53.610 --> 02:57.150 Maybe we can just go with guestbook. 02:57.180 --> 03:07.480 The first idea and this is a Get method because that's just a normal page Okay. 03:07.930 --> 03:12.880 And here we need some special handling of this. 03:12.910 --> 03:22.180 It's not just a template, so we need to well we need to connect to the database. 03:22.180 --> 03:23.770 So we use connect DB. 03:24.610 --> 03:37.330 But we can just fetch the entries by using get messages and just pass the connect db function to it. 03:38.380 --> 03:42.460 And then we're going to use our render view function. 03:43.270 --> 03:46.570 Our template name is the same as the route name. 03:46.570 --> 03:48.340 That's our convention. 03:48.430 --> 03:57.520 And remember that we have this second parameter called data can also pass it just this way. 03:57.520 --> 03:58.690 It's an array. 03:58.690 --> 04:00.820 By default it is an empty array. 04:00.820 --> 04:10.210 But we have it so that we can pass some additional data like Messages equals 04:11.920 --> 04:13.240 entries. 04:13.510 --> 04:19.840 Now some people also like to use the compact function. 04:19.870 --> 04:22.270 Now the compact function. 04:23.020 --> 04:26.200 Um, maybe let's also use the name named. 04:26.590 --> 04:27.070 Okay. 04:27.100 --> 04:29.230 So it's called template not name. 04:29.260 --> 04:30.160 Okay. 04:30.400 --> 04:31.990 That's a template. 04:35.380 --> 04:42.400 So if this variable would be called not entries but instead messages. 04:43.780 --> 04:46.900 And I would like to do something like this. 04:46.900 --> 04:52.930 I'm passing the key messages with the variable messages. 04:52.930 --> 04:59.740 Instead I can use a shortcut and call a compact function. 04:59.860 --> 05:04.060 So what this would produce is essentially what you've seen before. 05:04.240 --> 05:12.350 This would be an array where key would be the name of the variable and the value the value of that variable. 05:12.590 --> 05:13.700 That's optional. 05:13.700 --> 05:14.900 It's up to you. 05:14.930 --> 05:18.710 Personally, I don't like this function. 05:18.710 --> 05:24.410 I don't like using it, but it's just an interesting thing to know. 05:24.710 --> 05:25.940 Some people prefer that. 05:25.940 --> 05:26.690 For me. 05:26.780 --> 05:29.810 I prefer the absolute clarity. 05:29.810 --> 05:32.720 That's why I just want to be explicit. 05:32.780 --> 05:37.640 Anyway, it's time that we also add a template. 05:37.640 --> 05:41.150 So this is a guest book. 05:41.180 --> 05:43.250 Get PHP. 05:43.910 --> 05:49.340 So we just make it easier on ourselves by calling the template same as the root. 05:49.370 --> 05:58.310 Now we won't have a huge logic section of this template right now, because later on I'd like to obfuscate 05:58.310 --> 05:59.210 some data. 05:59.210 --> 06:04.100 So just hide the actual real email by replacing it with asterisks. 06:04.100 --> 06:07.490 But that's an optional thing for later. 06:07.520 --> 06:10.850 For now, let me just create a helper variable. 06:10.850 --> 06:15.960 Entries From data messages. 06:15.990 --> 06:18.600 Or maybe I'm just going to call it messages. 06:18.600 --> 06:22.530 So a quick reminder this data is an array. 06:22.530 --> 06:29.340 It's a variable available in this template because the render view function is receiving this as an 06:29.340 --> 06:32.760 argument and only then including the template. 06:32.760 --> 06:37.020 That's why it is just available just like that. 06:37.530 --> 06:43.980 Okay, now outside PHP tag, let's add the actual HTML. 06:45.360 --> 06:50.670 So we are using the HTML section element for every page. 06:50.700 --> 06:54.870 And we are adding a subheader using h2 element. 06:55.380 --> 07:00.750 So I can call it guest messages. 07:00.990 --> 07:07.890 And I think it would be nice if I could add a link to this page inside the header. 07:08.070 --> 07:10.560 So it's called Guest book. 07:12.630 --> 07:16.680 And Let me just call the link the same way. 07:16.710 --> 07:17.820 Guest book. 07:18.030 --> 07:26.490 We can now visit our new page, which doesn't really do anything at this point, but it seems that we 07:26.490 --> 07:27.900 should have the messages. 07:27.900 --> 07:29.910 Let me vardump the messages. 07:29.910 --> 07:36.060 So this would be a confirmation that we have successfully read them from the database. 07:36.060 --> 07:38.940 And indeed that's the case. 07:38.940 --> 07:41.790 We have our one and only message. 07:41.790 --> 07:51.360 Unless you have added more, I think that we should begin by checking if there are any messages at all. 07:52.050 --> 07:59.370 So let's use this if statement and the empty function would return true if the array is empty. 08:00.600 --> 08:09.000 So if that's true, we're going to display a paragraph saying no messages yet. 08:09.870 --> 08:14.400 Be the first to leave a message. 08:14.400 --> 08:19.000 And this can also be a link to the form. 08:19.300 --> 08:22.480 But let's ignore this for now. 08:22.570 --> 08:25.330 And then we just add an else. 08:25.330 --> 08:31.750 So as a quick reminder, I'm using the PHP alternative syntax for control structures here. 08:32.860 --> 08:36.580 Why don't we just add an endif at the end. 08:36.970 --> 08:45.220 And now we would have to use for each to iterate over every message. 08:46.390 --> 08:51.970 So this is messages as message. 08:54.430 --> 08:56.140 And I need to close. 08:56.140 --> 08:58.870 And for each at the very end. 08:58.900 --> 08:59.500 Okay. 08:59.500 --> 09:05.050 So nothing is displayed because we indeed have one message inside the database. 09:05.770 --> 09:06.220 Okay. 09:06.250 --> 09:13.540 So I might use an H3 element and just output something. 09:13.930 --> 09:26.320 So I'm going to use the HTML special chars Us for the message name to escape any unsafe content, like 09:26.320 --> 09:29.350 if anyone would try to use HTML. 09:29.350 --> 09:31.210 So we've seen that before. 09:33.580 --> 09:36.400 And someone did try to use HTML. 09:36.400 --> 09:40.510 That's why we are using HTML special chars. 09:40.510 --> 09:45.190 And then let's just use two paragraphs. 09:47.770 --> 09:50.080 So let me copy this function. 09:50.080 --> 09:55.600 And now there is a message email. 09:57.790 --> 10:01.510 So next up I should display the message. 10:01.990 --> 10:05.320 So I'm copy pasting this for it to be quicker. 10:06.220 --> 10:07.210 The message. 10:07.210 --> 10:13.150 And also I'm going to wrap the HTML special chars with another function. 10:13.150 --> 10:17.170 This is called an L to BR. 10:17.200 --> 10:24.420 It's just a handy PHP function that will replace all newline characters in the original input as. 10:24.420 --> 10:33.930 Remember that the message is a multi-line text input text area, so it will just convert this to BR 10:33.960 --> 10:39.300 elements, which would actually let us render those new lines. 10:39.300 --> 10:44.670 So if this was a multi-line message, it would just be formatted correctly. 10:45.090 --> 10:53.850 And then finally let's use a small element and say it was posted on. 10:55.830 --> 11:03.660 And again we can use HTML special chars with the message created at. 11:06.690 --> 11:07.230 Okay. 11:07.230 --> 11:11.340 So it's almost fine except that I've used the wrong variables here. 11:11.340 --> 11:15.240 So it's not messages, it is a message. 11:15.240 --> 11:19.500 So after refreshing everything is fine. 11:19.950 --> 11:22.430 Also let's get rid of this vardump. 11:22.490 --> 11:27.650 Okay, so let's summarize maybe what we have learned in this video. 11:27.830 --> 11:37.340 So we have learned how can we fetch data and how can we sort the data in ascending or descending order. 11:37.340 --> 11:39.560 So the default is ascending. 11:40.640 --> 11:43.730 Then we've created this endpoint. 11:43.730 --> 11:47.840 And we have also passed the data for the first time. 11:49.970 --> 12:01.730 And then finally we have actually reminded ourselves that we need to escape the data because users might 12:01.730 --> 12:06.560 just supply malicious data, intentionally or not intentionally. 12:06.560 --> 12:16.610 So it is in our best interest to make sure that any unsafe code, HTML or JavaScript is escaped, so 12:16.610 --> 12:23.330 it won't be interpreted as HTML by the browser, but displayed as text instead.