 Welcome back to the channel. I was looking through Stack Overflow to see some questions and some answers and I landed on this wordpress challenge so I picked it up because it looked interesting and it's something that I've looked at before and I thought why not take on the challenge and really help out. The challenge is this person has a table that has a brand model color and mileage of the different cars in the different rows and they want to be able to view this information in a custom post type and probably also exhibit that information from the custom post type onto the front end. The big challenge they do have is that this information is coming from a firm straight into a database. So how do they do this? How would you do this? So we're going to tackle this in a number of steps and we're going to make sure that we actually make it work. So what do we do? We're going to come here and we're going to open a plugin as always and we're going to call this a Stack Over question that will be our plugin and then of course we're going to drag and drop that into our editor and then we'll start by creating a PHP file with the same name open PHP then we shall use a startup plugin to help us open that so we'll call it stackover question and then I'll change this to my URL and then we shall just have the name we'll just say take your place, move stuff from the DB to the custom table to CPTs, our version one that will be that will be all good. I'll just make it this GPL 2 and save this. So we've opened up our plugin. The next few things that we're going to do is let's plan this out and highlight it inside steps. What do I mean by that? First we're going at the custom post type that will allow us to have our information after the post type we're also going to add some meta boxes because if we look at these these are properties of a custom post type if the car is the custom post type we'll have brand, model, car and kilometer these are small pieces of information which I think belong inside meta boxes so we'll open meta boxes for them to be exact and after opening four of those we're going to also add another row onto this table so that we have a unique so we'll add a column or in databases that's called a field we'll add a field for auto IDs and these are the very IDs we shall query for we shall first of all get our custom post types, pick the auto field that has ID that has been saved as a meta box so we'll make these five so we'll get that meta box we'll filter out and say these particular custom post types don't have these IDs and we're going to check our database table to see if there any other fields that don't have similar IDs as the meta post that has been saved in the CPTs so once we find those then we shall use our SQL to only get back the rows that don't have similar IDs registered and then those will be auto inserted inside our CPTs so that's what we're going to do so we'll get those IDs filter the table of course contrasting with saved meta ID in CPT so that's what we're going to do about four steps relatively long but we'll be able to make it and we'll do it very well so our first step is we're going to make custom post type and the good thing is that the person here has added a custom post type so we're going to just copy that so that when we paste back the information we're giving them exactly what they do need so the next thing that we're going to do is that we're going to copy their meta boxes and we're going to add those meta boxes to our code so I'll paste this here let's see what we have inside our dashboard so we'll go to install plugins inside our dashboard and we're going to activate our stack of a question plugin after activating it we'll see we have a custom post type here of the portal so once we go to our post type we see that it's empty we don't have any post types when we click add new we have our custom post type but we don't see any meta boxes here meaning there is a problem already with our meta boxes so we look at this and we'll see what is needed so when we look at our meta box we'll see we have the id for the meta box we have the title for it we have a callback function there is no way we're going to be getting that field so we remove this pasting argument and then we see this is called car reviews which should be the post or the screen and this is wrong the post type registered here is called auto so we'll need to change this to auto as well this is no more so meaning the fields will be right here under the editor and then high for priority so let's reload that and we see we've made some corrections to make sure that we have our fields here so we have the karmic we have also the model so what we're going to do now is we're going to have five fields so i'll just duplicate this to make them two let me increase the real estate we have here so we have the make we have the model we're going to have i'll just look again at our database that we're going to have here so we have color and then we have the mileage so the mileage will be in kilometers we don't need to throw off that unique ID also in here but if we want we can as well just show it and say ID so we'll call this a UID so this step is done and we'll tick off two pieces to be done so i'll just cut this and move this along the way so that we tick off whatever we need and we're done so we've done the custom post type i'll remove this i'll also remove this here because we are still in php there's no need to escape it so we've done the meta boxes now what we need to do is we need to add a column and field inside our database so we're going to make a similar database inside our php myadmin so we'll go for localhost slash php myadmin and we'll go to our cc cc database and we're going to create a table here which we shall call garage of course it would be prudent for us to add the wp underscore which is the prefix at this time but we can just also leave it plainly but for now let me just add that prefix we're going to have four columns actually five columns at that so we'll click go to add the columns so the first thing that we need is going to be an id the id will be an integer we can give it a length and say maybe it will be 120 i would never know how many fields there will be it should be always not now it will be auto increment so it will become our primary id and i think that is good enough the next thing that we need to do is we need to have a brand so we'll have a brand which will be a text we can give it a 120 we need to have our model here which is also similarly going to be a text we'll give it 120 and that will be okay we need to have a color which will also be a text that runs through then finally we are going to have our mileage which should be kilometers so i'll keep it the same as we have here so we have km which is our kilometer it's an integer so once we are done with this we are going to hit our save to actually get our wp garage so when i go inside here you will see that we have our table that has all this information so we're going to insert some fields inside here so since this is auto incrementing we can ignore it but we need to add the different brand so we need to have a fiat fiat panda black so our kilometers will be that we'll have a fiat well it will be a panda model and it will be color black this is not the best way to work with your database but just populating it so that it's actually working well so i'll hit go to have that and then i'm going to do the next piece of information which is opel kosa red so we shall also go back to enter and we'll have an opel to be a kosa it will be red in color and it will have this mileage so we hit go and go again and this is inserted so we'll go to our wp garage you'll actually see that we have our fiat and this information has been put in twice so what i'm going to do is just get rid of these two rows i'll say yes so we have our id and we have this as three so i'll just change this to two but even if it was three it would work so we have a table here which is similar to this the only difference is that we have a primary key of id that is allowing us to work with our garage database table much better because we need a primary key in any table you need a primary key so what we are going to do next is we are going to start querying our custom post type to check if there are any post types that have a metadata that is saved and is similar to the id so let's get to that so what i'm going to do here is i'm going to open up a function and i'm going to call it check for similar meta IDs that will be our function now inside this function we are actually going to start querying so we'll need to use the wp query and to make this clean i'll just call it a techie press underscore check bla bla bla now this function is going to of course start off with a clean id array so we'll say id array in CPT is going to equal to an empty array so this is the new of writing PHP arrays if you're not familiar you could be used to having this array and that it works the same way it's just that the square brackets are the new we are doing it so we'll just leave it as that for backward compatibility and the next thing we're going to do is we're going to query for all autos so we'll say our arguments are going to equal to an array semicolon and we're going to look for the post type post not post t so we're looking for post type which is called auto as we have which registered here auto in the post type when registering it so we need auto the posts per page and so we're going to get all the ones that available so we'll get a minus one here i'll make this wpc friendly that is a wordpress coding standard friendly now once we have our arguments the next thing we're going to do is we're going to say let's get our wordpress loop and it's equal to a new wordpress query class so we bring the wordpress query class in here and we say we'll pass in our arguments from here so we have our loop and then we'll say while we have any loops so while loop have posts if our while loop has any posts here actually come here and say the loop will be the post so the next thing that we're going to do is we're going to get our id cpt here and we're going to chain onto it or we're going to say it is equal to now we need to add the square brackets here to show that it's the array we're adding onto we're going to say get meta get post meta and what we need here is the id so we'll say get the id which is available to us as a function inside the loop so we can get the id and we're going to get the key which we shall call id when we are saving it and we're going to say this should be true because we want it to be single we don't want it to be an array and then eventually we're going to return our id arrays in cpt so this function is just querying for all the post types posts of auto and then it's looking for post meta called id and then saving this inside inside an array so we have an array of ids here now the next thing that we're going to do is actually going to query our database and find out if there any ids that are similar to what is in this array and we're going to remove those ids and only get the ones that do not exist inside this particular array so we are going to open up a function and this function we're going to call a techie press underscore query garage table and we're going to pass in our information now what this function essentially is going to do is that we're going to pass in our global of wpdb and wpdb is a global variable that passes in the whole class for working with wordpress databases it allows us to have clean methods of instead of using like mysql prepare and all of that everything comes out clean in wordpress when we use this global for our database so we're going to say our database table name garage and we can add the table prefix to it here at the beginning and what we're going to do is we're going to use the wordpress the wpdb we need to get the prefix so that will give us the prefix and we shall chain it onto the garage the reason why we get this wp is because all these other tables were having wp and you never know what they use at the end of the day we'll use this table either changing the prefixes and so on so we need to have that uniformity that's why i gave it the wp underscore garage here and that's why we're using the prefix here so that in the future when the prefix is changed we still have this available for working and it can still do well so the next thing that we're going to do is actually use mysql at this point and we're going to say that we need to return some sort of results we are going to get these results by saying we're going to check our database and inside our database we're going to use a sql here so we need double brackets here so we need double quotations so we're going to use wpdb and we're going to chain onto it a method that is part of that class and that method is called get results and get results of course will need to be fed in with the parameters that are needed and the parameters that are needed here are actually sql so we're going to do the select so you need to have some knowledge of sql it's a select it's all from our table name so we'll bring in our function here our variable here so we're going to get all the results from here and we are going to actually return them as results so for now let's just test some of these things to see how they do work out so i'm going to use a wordpress hook which is called a wp and what this hook does is that every time you're working with wordpress it actually is hit so what we're going to do here is we're going to vadamp this so i'm going to first copy this place this here and we can vadamp it to see what's going to happen but we know of course we're going to have zero so there is no need for me to do this for now but we can also just query this can query our database table to see what results are actually returned so let me duplicate this and let me vadamp what we get here so go to the end and save this let's come back to our back end just hit the same piece and you'll see that here we get back an array of data and let me just show this better and we'll see that we have an id of one which is a string we have the brand fiat modopanda and so on and we have our secondary of information that is for the brand opel modokosa and so on so we're getting all our information back from the database safely inside wordpress so this is working out well as the function that we need the next step that we're going to do is that we need to juxtapose what we have here and what we get back here remember we said we're going to exclude the ids that are returned from here because that particular custom post type is already saved so we don't need to have the ids that are in here coming from of course this id we're going to save this id as a part of the custom post type so as we adjust with that metadata we'll need to remove that particular information so how do I tackle this we're now going to start working on our next function that actually is going to insert into the custom post type so I'll begin a function here and I'm going to call it a techie press insert into auto cpt and that will be a function and the first thing that we're going to do is that we're going to use the wp insert post function of wordpress that takes in an array of information and also takes in an error but for now we'll take this away because we are not going to error we're going to have definite data so we'll use that so what we need to do is have a post array here so I'll just copy this and start it from up here now of course this will be an array we close it off with a semicolon but we need values that are going to be passed in here so let me just check this array function here and we'll see what it looks like in our wordpress codex and we see that this helps us to insert our data post by adding new data and the parameters it needs is an id needs a post author now the id of course will be auto entered so it needs a post author these are optional fields it needs a date which will be added it needs a title so we'll use a title we need a post status our post status will be published because one then instantly published we don't want to leave them as drafts and then we'll use the post type that we're going to be pasting this information is auto not post but auto and then of course we'll need to add a meta input remember we said we're going to save all the other small information inside our meta input so those are the pieces of information that we need right now uh let me just add them here so what we're going to need is we're going to need a post title we're going to need meta input we're going to need a post type we're sending this information we're also going to need a post status for what our post will look like after that information is added so let me add these in single quotes and then i'm now going to chain on our post title so our post title for now we shall strip shall use the wp strip all tags and then essentially what we're going to call this particular field is when we get our information from the database we shall say whatever that result will be we are going to chain on and say let this be the model because if you look at this information coming back we have a string here called model so we're going to have the brand and the model coming fast as the name so we'll have result model and then i'm going to chain on the brand so brand and then model will be our title i'll just add a space in here to make that look a lot better so we'll have something like opel kosa will be the name of that we can also add on the mileage if we want to there is no problem with that so we can do the something here and then we'll just make this in our camp so that's what we'll need next thing that we're going to do is we're going to chain here let's not forget the comma we'll need to add the meta input and this is going to be in an array of data add a comma here now in our meta input we're actually going to have about four items so i'll just copy this here pass them here so what we're going to need first of all as metadata is we're going to need our id which will allow us to have something that is unique remember this is the id that we'll use to contrast against all the information coming in from this particular function where we query the CPTs and get the metadata stored as id so we need to save this id here and we shall equate that to our id so result id put a comma here and then let me duplicate this three times so we'll have a brand i'll make this a small b we'll have a model small m and then finally we'll have our kilometer this will be a small k brand that's fine add a comma we'll add a comma after our model and then we're going to add our kilometer here and also add a comma to keep it friendly with wordpress and then we just need to do the same thing here and add and chain the different pieces of information so we know that our post type will be auto so single quotes auto that's where we need to send that information then here we'll also add single quotes and say we need this to be published so save this here now we're getting that information and then we are sending it inside our post type but no post type will be created here even if we run this particular function so i'll run this here i'll change that so even if we run wp action we're not going to get anything because this will be broken there is no reference for this so what we need to do at this point is we need to go back and start querying our database and then compare the two pieces of information so we're going to have a variable here which we shall call available in CPT array and it's going to equal to this particular function take a press check for similar meta IDs probably this is is not the best name to be descriptive here but that's the function that we'll need and that is going to give us the information so we can actually vadam this to see what's coming in so i'll just vadam this information here copy press this and then i'm going to do a die that will allow us to kill our process from here and we shall not post anything inside our pieces here so if i come back the same place here you will see we have an array of zero so we need to actually check that and say if it is zero then abcd is going to happen or abcd is not going to happen so we already have an array of meta IDs already showing us here so what we need to do is actually we need to pass these meta ID arrays inside our query garage table so we're going to bring this here as a function and we're going to pass in our available car in the CPT because we want to receive it here so i'll pass this as an argument in here so that we can readily receive it here and the next thing that i'm going to do is that i'm going to call this as our database results so we want our database results to come here so i'll use this to i'll actually just vadam it here to see what we get back so inside our database here we are going to do a variation in terms of the queries that we do if the car available in CPT array is equal to zero or is null or is equal to a string of zero or is empty we are going to run this straight up we're going to get our results as this however if we have some values in our array of the CPT array then we're going to run a different kind of SQL query so let me do this first and foremost so we'll get this here and say if then run this so we will say our results are equal to that and we shall return results that's what we shall return but what are we querying for what's the if we're going to say if this is equal to null so we'll put null first and then we say it should be of the same type and it should be equal to that we are also going to say all if it's empty if it's equal to zero as a figure we can also add another condition to make sure that it is right all it's a string of zero as we see here we have an array of zero that came back let me wrap this so that we can read it we can also say if what we are getting here if this car available array is actually empty so we can also say if it is empty so I'll paste this here take this off if it is empty then we run this and get our results otherwise so we'll say else else we're going to have a different way of meaning if it's empty or zero that's it if it has some figures in it then it's going to be something different so otherwise we shall return results but we're first of all going to use a different SQL uh to query for our data so first things first is we shall write and say our IDs will equal to and we're going to use an implode method here to get our data out so we use that implode and we're going to say we want to strip over particular information so we want to get the commas out so single quotes here we'll get the commas we want to take the commas out and the space and we want to take those out from our car CPT variable that's the first thing that we want to do then what we're going to do next is we're going to write an SQL statement and say our SQL will actually equal to double quotes terminate that and say it's going equal to select all from and we shall bring in our table name and we are going we're going to get all the rows from our table name where the ID if you remember our ID where the ID here is actually not in so it's not in and then we shall pass in those arguments whereby we are talking of the IDs these IDs that we implode have to be in here so for example if we have two or three or four those will have to be taken out so that's why we add our IDs inside here so once we have our SQL ready here we're going to use this method and say our results will equal to this but at this point we're just going to pass in our SQL because we have set it out as a variable so we'll just pass in our SQL we have in both cases results returning and we save these as a database so let's see what happens we'll save this come back and reload and you see it brings back all to arrays from inside our database that is good so that is brilliant now we're going to just use i'll comment this out and say since our results come back as an array we're going to use a for each statement so we'll say for each so for each database results as result as a single result at that point we are going to get all of this here and then we're going to move it inside our for each statement so for each result that comes back we are going to get all the information that comes back as a result brand as a result model as a result kilometer as we are seeing it from here then we are looping through that and inserting that as a post so let's save this and see what happens so i'll reload this and reload it again and you'll see that we get our pale cosa coming back with the number and fiat panda coming back as that if i reload this over and over and over and over and over again you're going to see that we don't get any other results coming in because we are exclusively checking for whether they are similar ids with this metadata so let me just click inside one of this let me edit it this data is not showing here so let's show it so that we can be about actually confirm that it is true so inside our value here i'm going to open some php tags echo and say get post meta which is what we need now inside this we shall just be able to say get the id get the id the function and then we're going to pass in the metadata we want to get the id here and then we're going to say this is true and of course pass a semicolon and save this so we're just echoing out what has already been saved so when i save this let me reload this oh this is get post meta sorry get post meta and when we reload this you'll see that we have our two actually here i'll just copy this paste it in the different fields and we'll paste in their respective ids and you'll see that it actually works out so here we have run brand have model we have color and then finally we have km for kilometer or mileage reload this you'll see this information shows up here apart from color which we didn't save here so i reload this and will have color and this will be related to color i'll spell this right and save this so this is not working out well let me trash this and delete it from our trash this will also help us see whether our code is logically right so you will see that it automatically fetches it again and when we see fiat panda it doesn't have this so i'll also trash this automatically it has been fetched again so we have fiat panda and we have our color black here and we have our pale red here so we only have two post types in our databases here so even if we made a mistake it would automatically come back and we just need to clean our trash to make sure everything is okay so we have our two pieces here it means our code is working out well it's not duplicating any post types as adding them but rather it's giving us all our information coming out really neatly and well here so we couldn't decide to just make these fields read on this so that nobody is tempted to feel like you know what i can actually edit this content so we can add a read only field here which is a part of html and this is just for user experience so if i reload this you'll see that these are grayed out somebody knows they are not going to be able to edit them even if they type they cannot edit them so everything is actually working out well and nice now i can make this code a little more improved because right here when we were querying our database results we were checking to see what comes back so if we check in our database and there is no information coming back there is no need for us to run this code right here so i'm just going to use this a function that we have here paste it here because it's more or less similar to what's happening and i'm going to say the database results if the database results are either now or zero or a string of zero or it's empty then all we need to do is actually return we don't do anything otherwise if it's more then we get to add more information from here so let's test this out i'll leave this vandam so that we can see more so what we're going to do is we're going to manually add another field here to insert a row so we'll just say maybe Toyota and then we'll add Mitsubishi Toyota Mitsubishi is one company i'm not really good with cars so Toyota Mitsubishi and we'll say it's a pickup and we'll say maybe it is white then it has a mileage of whatever that is and hit go so we'll pass that in let's check our WP garage we'll see it's duplicated it we'll just leave only one here so we have it five here a reload just to check you see our database covered back with zero as a field but let me allow this to pass through so reload this so we can actually see that our test is broken the first time it returns all the data but the second time it's not returning it because something is broken so let me just troubleshoot this let's check for database vandam database that's returning zero but that should be impossible because the database is supposed to return some information let's look for the available in CPT array so reload we have our two variables we have one and two showing up here so what could be the problem so let me just vadam here and see what we get for ideas we have one and two and they look really weird it's a string let's see what happens let's try to check if we can get back any database results and reload here we'll actually see that we get back one item in our array of mitsubishi pickup and kilometers and all of that so this is working out well it has contrasted everything that needed to be contrasted and sure thing everything is working out well so i'll save this we can take out all of this that is vadamping to leave our code clean and let's see if it passes this test because the Libby results will not be negative let's see if it actually saves our auto our post so when we hit it you'll see that we have our Toyota Metsubishi pickup and when we edit that it has a UID it has everything it's just working out fine and it's fantastic it's actually working out the way that i expected it to be so that is one way you can save this information is already saved in the CPTs you can actually throw this on the front and and use it in any way that you want to so go ahead try it out see let me know how you've enjoyed this code let me know if it has been helpful if it has pointed out some things to you and that's how we can work with the database so i was adding flat data here but maybe in the next video i could show you how you can make your own form that sends the data directly from the front end inside your database what do i mean i want us to automate this whole process of you saying i want to insert a row and then say i want to add a Toyota and the Toyota will be a Corolla and it will be a saloon car saloon car it's a saloon car already so this could be blue and it could have a mileage of just a few kilometers i'll add this so one of the things that we've been realizing is when we go to her table we see we've added the information twice i don't want that to happen i just want it to be only once just to test our code again if we hit this here you'll see that it actually has four pieces and we have our Toyota Corolla with all our information so it's working out very well um it's relatively performant and this is all the code that we needed so let me know what you think about this if you'd like to see more of issues like this uh tackled and cleaned out let me know uh what you think you could share your own trouble that you're having so thank you for watching uh subscribe to the channel if you haven't yet subscribed to it because we have very many interesting things coming like the video and share it with your friends thank you for watching and enjoy yourself