 Welcome back to the channel, so we are working on our trending post report widgets and this is going to be the second part that we'll be working on. Now we want to query for the most commented on post and by default WordPress actually makes this very easy for us in our database. So I'm going to go to my local PHP, my admin and I'm going to try a couple of things that actually existed there that maybe we've never really given thought to. So when we open our PHP, my admin, we actually see our databases. So in this particular case, my database for this site is actually called CC. Now in CC, we have all these different tables and you can see some are for newcomers and others are just for default WordPress tables and they're about 12 in number. So we'll look at the WP underscore post. Now, of course it has a prefix of WP underscore. You'll see all through the tables and that comes from our particular file in our WordPress. That as we're installing, you remember we requested for a table prefix. Now when we're going the WP post, for example here in the table, we're able to see the different rows of information that are available. For example, here we have a post with an post author who is author one was an ID of one was posted on this date and it has this content. This is the title. It has no except it has a post status of publish. It has comments that are open allowing us to comment on it. However, even with all this information coming in at the very end of our table, we actually see that of course it has a link to that point, but we'll see that it has a comment count. Now this comment count is related to of course the comment status which is open allowing us to see to add more comments to the post. But this comment count here we can actually use it as you can see all the others are zero others are three. We can use this to actually throw and show the most commented on post and now what we're going to do is we're going to use a little bit of SQL and actually WordPress does this for us very, very well. So we'll come back to our code here and we're going to create a new function which we shall call a get most commented posts. I'll call it get db most commented posts and right here we shall just add our kali brackets so that we can start adding. Now WordPress of course comes with a global that is called WPDB which is for WordPress database in there. Now this particular global if we ever damp what it has on it, you'll actually see a couple of functions that are added onto it. I'll echo this with the pre tags so that we can format this and then I'll duplicate this, bring it here and close off the pre tag so that we can see it a little bit better. Now I just need to call this function here but what I'm going to do is that I'm going to create a new widget here that we shall call a trending posts comment widget. And that is the one that we're going to use to show our comments and we shall just use this same function we have here and we shall drop it in here. However because we have introduced the aspect of namespaces I'll just copy this and add it to this point in this function and also this function so that we don't have our plugin breaking. So what I'll do is I'll come back to the dashboard of my website and I'm going to go and we shall see that in our trending posts we expected to have one parameter. This is trending posts and we should be having another widget here. So I'll just make some amends here and call this most commented. I think I deleted my function. I'll just put it back here that I deleted. And then when we that is for this function now it's available for working. It's the one that was bringing us our trending posts. Now when we look at the get db most commented posts now we're able to see it in our dashboard and here of course we have our trending posts showing up. Now in this wpdb we actually say that we have an object and if we scroll down we see the information that is actually available for us to use. Now of course we have wp options coming in there but we have a function that we can append to our wpdb and now this function is actually what we call the get results. Now of course get results expect some information coming to it. Now inside this get results is actually where we're going to add our SQL. Of course our get results expects the SQL commands to come in. So first of all we wrap them with our double quotations and we'll say we are going to look for the ID, the post title and the comment count. And we're going to select this information and this information of course will be selected from a table. So we add the from table and the table is going to be called wp posts which we already saw in our database. Now the challenge comes with the prefixes. You're not always sure that the prefix will be wp in any case or in any database. So we need to make this a little dynamic and we use our Kali brackets here. So what we do is we access the wpdb variable and then we append to it and say we want to get the prefix of this. This will allow us to get the prefix that was stored in the WordPress on installations. Next of course we have to say we are going to get the posts that we actually do want to get are where the post type is actually post. Because we're looking for the most commented posts and we want to be published. So we don't want drafts or any other post status. So that's why we have the post status of publish. And we want where the comment count is actually greater than zero. Now the reason why we wrap our zero into strings here is because the database stores that information as a string. When you look at the database here and you go to the very end. This information of comment count is actually a string and I'll show you. I'll add up that for you to see that we actually get a string back. Now we echo and we say we want to get one where it's greater than zero. What we shall do next is actually we need to order those comments. Already we want our query to come ready to give us that information so that we don't do any extra coding to order this. So we'll just say order that information by the comment count and make it from the highest going all the way to the lowest. And then for now we shall limit we don't want our whole database to come back. We shall limit this and say we want ten posts let's say for now. So what I'm going to do is actually I am going to wrap this into into a variable so that at the end of the day we can always add an option to either make this more or less depending on the user that we do have. And what we shall do in this case is that we shall take away this ten and then close off close off our SQL and then we're going to add the PHP and call this limit. Now just below the global I am going to add a variable called limit and it will equal to ten which is a number. After adding a limit let's go and see what is actually returned on our front end. So in our dashboard we'll actually see that this returns an array of zero which shouldn't be the case. Let's go back because we have a post here that has a limit of two. So let's see where we are in our code. I see I have no spaces here. Let me just make sure I have the right spaces in my query. So I'll space that actually it's now starting to change color for my coding. Make sure I have the spaces in between my SQL link and we have the space on the limit that is working out well. This space doesn't matter for the PHP but I'll just wrap this then remove that space, save, come back to our front end, reload. And now we have a proper object that has our information. So remember I told you the comments count will actually come back as a string and it has a value of two. So we have a post called hello world and it's actually our most commented post at this time. So what we are going to do is that we're going to save this particular information we're getting here in what in a variable called our commented posts and we'll equal to that and what we're going to do is just I'll just clean this out and what we're going to do now is that we're going to loop through the array of information using a for each statement and we shall save for each as commented post as a post we're going to virtually just come back here and then copy this code that we have here so that we can use the same styling and have a similar table of information so what we're going to do here is we'll have a post title and the number of comments. So number of comments is there now in here what we are looping is remember we had our post title and comment title. So what we're going to do here is come back in here we're going to just bring the post because we are looping post we shall add the post title which is being written in our object and then write down here and then we shall append the comment count and save this. So I have an error here because I have an extra an extra bracket that was coming from the vadamp so I'll just tidy this up a little bit so I need to remove this remove the while loop tidy this up bring this a little back save this come back reload unexpected of course now we no longer need that bracket and then come back and reload here and we'll see that we have a hello world and we have a number of posts which are two so that's how we get back our commented posts and I'm going to wrap this up by just styling up our project that we do have right now now of course everything is namespaced it's clean we can move this to another let's say file if we wanted to just to keep this a little bit clean but what I'm going to do now is actually I'm just going to now add some style to our plugin now to add our style in our plugin I'm going to come here and then comment this and say style add styles add styles and we'll use the default WordPress way of adding styles so in order to just add only to the admin and not send this to the front and we're going to use the admin print styles hook in our WordPress and what we shall do is we shall use an anonymous function in this case and then we shall use a wpnq style and we shall prefix this with a tpw styles scripts and styles and we shall use the function that is a plugins URL here and that accepts only two arguments so it accepts the name of the file which will be css folder called css and a style sheet called style.css and of course we'll add the global file so that we're able to track this back just from this file so in here I need to create a new folder which I'll call css and in that folder I'll create a file called styles.css now the first thing we shall do is we shall add a table but I'm going to make this a little bit more restricted only to this widget for example and what I'm going to do is that to the different tables I'm actually going to add a class and so to this table I'll look for table again table table I'm just going to add a class and the class will be tpw tables so I'll just copy this come back to our style and then put the drop tpw tables of course that will be the table and what I'm going to do is I'm giving it a width of 100% and the next thing that I'll do is that I will need to add a table head with a background so table head give it a background and then in the background what I'm going to do is add a pound sign go for ffee I'll just use that for now and save that so if I come back here and reload we'll actually see nothing happen because I called my file styles here this should also be styles so I'll save this come back reload and we'll see that our table actually has a pinkish color right there might not be what we were looking for so let's go for a 333 which is black and then I'll give this a color over white which is fff so save this come back and reload we have our titles there I'm just going to make sure everything is right aligned left aligned sorry so save that left aligned of course we'll need some padding and then we'll make the different table rows the tds that come second to just have the content right so we'll have alternating we'll have alternating table rows and we shall add the nth child and save for every second table row what we're going to do is that we're going to give it a lighter shade of black which is that CC we'll have the text in black so black and it will still be left aligned just copy this and then I'll add this for every table row the second table data what we need to do is just add a text align of center and so I'll save this table head here th save need to add a comma to just make this so to every second table head that we do have will want our information actually being in the center so reload we have that coming out very well if the posts are going to be longer than this then we'll need to shift this but automatically the table will readjust to have that but visually let's just make that table head a little restricted so I'll add this here over just 30% of the table so come back here reload and voila looks a little better looks a little nicer so the only thing that we'll be missing is adding some padding into the table bodies so dump this here move it down do table data and then add a padding of about 10 pixels no no no no we'll do 5 pixels top down and then 7 pixels left right of course I'll just duplicate this to add the table head here add a comma and then of course reload and I think we are almost there if we did this right we restricted the the height of this so we'll make this 35% so reload and that looks a little bit better we could always reduce the size of the table data that comes outside the table head and make it a little smaller but I think overall this looks worthwhile and it's working as we had planned out to be so thank you for watching the video if you really enjoyed it please give it a thumbs up subscribe to the channel if you haven't yet and click the notification bell if you want to be informed about videos that will be coming up next so thank you for watching and happy coding