How to Filter Dates from PHP MYSQL Database to Javascript to Chart JS

in this video we're going to focus on how we can filter the data and this data is all coming from directly from our database here and what we want to do is we want to change this for example i'm going to select here five and then you can see here now the data will start from showing 5 april and then here this one 19 so we have a starting and ending point and now it's from 5 to 19 and we can of course play around that 22 and then go back to maybe 30 and then this one again uh let's say 12. here we are so let's start exploring how we can filter our data here on a daily basis so let's start look how to filter dates from phpmysql database to javascript to chart.js so the first thing what we need to do here is to note that i'm working on a local host this is very important because if you don't have a local host it's not possible or you can do it on your web server one or the other so you cannot have a standard file on your desktop next i have a database ready and this is just a basic database here it says tesla or imagine it would be a stock from tesla and we have heated dates all for one single month and then we have here the price and then here as well the volume of it although i want to focus on volume most important for us will be the date and the price so let's start work on this so the first thing what i want to do is i want to get the default code which is chargeys2.com getting started this specific link here which you can also find in the description box scroll down and then just copy this entire chunk of code here we're going to grab this code here so once we have that i'm going to paste it all in here and then i'll just cut out this and put it in here the title so next i have some additional code prepared because what we have through here is to make a connection with the database so in this case i have something already here and i will put this in the comment section below so you have this code as well and this is just a basic pdo connection with the database so i will not explain how this works you just can copy and paste this one let's copy and paste this put in here and of course by the way you should have this matching with whatever your database who's your name password and database name is so i'm going to put it here all up just paste it all in there this is just a php so if i save this now nothing happens of course we're not yet connected but we are now able to access our database so the next thing what we need to do here is to start working on updating or at least the data we have here should reflect whatever we would have in our database so to make this i'll clean up the database a little bit or sorry not database or keynote the background color here we just have only a single item that's it that the colors here this here needs to be adjusted so we're going to work on that but to do this we need to have first the data coming in from somewhere so what i'm going to do here is but let's see we can do that somewhere here above is basically or maybe here and it's going to say here it doesn't matter exactly where you put it as long as you put it before the javascript area this is very important so then what i'm going to do here is the php and it's a closing tag i'm going to just go fast in here so this would be uh try this is the attempt to connect with the query and then uh executed and then what i'm going to say here sql equals here now quotation and i'm going to say here select asterix from meaning basically we're going to select from a database but with the aspects meaning we're going to select everything although we could be very specific saying only the date and the price that would be even more better because it reduces the workload on it but in this case i'll just say select everything and we can always adjust this one but the focus here is not to get the data display so what i'm going to say here from and what will be here the item name here so in this case it is tesla but what i'm going to do is i'm going to say charges.tesla to be very specific so let's say chart js dot tesla as the table so this is the database name the database and the table name within this database is tested all right so now we have this uh sorry just this should be tesla my mistake so once we have this here semicolon then what i'm going to say your dollar sign they're going to say the result so what you want to do now is to get the result as a pdo and this po is a query you get the query and the query is of course the sql here dollar sign sql so once we did this we're going to create an if statement to check if we have any values in here so we say if dollar sign result so if the result is let me say row count we're going to count the rows and then here basically we're going to say here should be bigger than zero the reason why is if there's no row in that case there's no value or no very matching with our condition in this case we should have here because our row starts at id six and here there should be a total of 30 items as you can see here total of 30. so it should be 30 rows in here so once we have this we're going to do now here a while statement and this while statement is so if there would be anything loop through every one of them so for every row it's considered a result we're going to fetch the data problem so what we're going to do here is to specify what we want to get here so what do we want well first of all i indicated we wanted the date and the price so i want to grab those i want to display them in an array so i'm going to say here dollar sign i'm just going to make a date array as a variable or a i guess a variable in php and then we say here equal dollar sign then we're going to say here for every row which is this the row name would be whatever that column specifically is in this case it's column date so for every date and of course we're going to do exactly the same with the copy of this doing the same for the price i want to grab you the price of that stock so we say here as well price array very straightforward so once we have this what we want to do next is uh let's see here we're going to say here unset the result so yeah undo this eventually and then what i want to do here is out state so once we're done here you don't need any more for this then else statement we could say here for example echo because we mean this if we have a row count of larger than zero do this else sorry no or no results no results in database so once we have that one we have this finally here we have our try the try is connected here so we have a catch if ever we would have an error we will catch that error and we can say your uh pdoe exception or video exception you can show here the error letter e or as the error or the that's the variable and then here we die or we shut down the connection immediately and just indicate here error whatever you want do here uh i'll just say error for now we could even echo out the error message in here so it doesn't matter for now i will just skip that one that's not important for us most important one is here we have this connection i do expect this to work so we have this one here so if i save this now of course nothing happens yet all right so unexpected here all right i guess i already have a mistake here so let's see here we have the unexpected here or die this one so what we have to do here is seven columns say refresh all right so make sure you have seven column because php is not like javascript php is very strict so if i have this here now right now nothing fully shows here so let's start to work on drawing the item because i was expecting that this should show at least something all right so ironically i got an error here and after just checking i was thinking what's going on here and i'm apparently i was putting a tesla and i didn't even know that apparently my table name is not tesla all right that's very bad so anyway so let's correct that if i save that i should expect here now if popular response all right so this works everything is fine no more error response below so now what i want to do here is to start getting the values from our array here however when we have these values you must understand the following the values here are in php so that would mean that we need to convert them into a javascript readable version so that's what we're going to do right now so what we're going to say here is first of all let's look concept and then what we can say here is constant uh date or what we could do even here let me just do this first i'm going to hit onset dollar sign video to undo the connection all right now errors then what i would do here is for example i can do here a print so say print underscore r all right and then here the date array if i save this now you should get here a lot of values in total of 30 items which is correct because you can see here from april 1 all the way to april 30. so this works this is a confirmation that now this part works but of course this is php readable text so we need to parse it into javascript and parsing basically means make something readable for so in this case from php we parse it to javascript meaning from php we make it readable for javascript so that's what we're going to do right now so i'm going to put in here and let's start to work on that so the first thing here constant and i'm going to say here the date array let me just make it javascript equal and then we're going to say here php echo and then we're going to do here json underscore and quote and then what we're going to do here the json underscore encode we're going to copy this and put it in here so it's now becoming a json variable that will be readable for our javascript so here semicolon so if i would do here now something console walk and just display this then you will see it becomes now a json text and a refresh here uh console off we have your noun array with the items but this is very important json is a universal language but from json javascript cannot read that yet so what we need to do now is we need to make sure that this item is still readable in the right form or at least what it can read is that this is a date object this is not considered a string so we need to make sure it is now properly because this is just a string now properly a date object so what we're going to do now is just very straightforward contain constant we're going to say date let's say chart yes very simple equal and then what i want to say here is the following i want to look through every one of these items and then convert them into a date object so to do this what we need to do here is basically i'm going to say here i'm going to grab this the date array js because that's the convert it into json encoded code or text string then we say your dot map so you want to loop through every individual value of that and then to rename that we're going to say this is the day and then we have so every individual item is called the day we rename it to a day and then we're going to say yes we want the index so that might be sometimes useful so we're going to put in here the arrow function expression because we're going to loop through them as a callback they're going to say elect there's a day ks so every day this will be equal to new date and then what we want here is the day here if you have that there you are so the next thing what i want to do here is if we can do a console wall we're going to grab here now the djs and just see what we get let's save this refresh as you can see here now we get the date and we're getting here including or basically the entire date object including the i guess the day the month uh the number of it the year hours gmt or ground which mean time uh time zone basically so you can see here we have everything so what i want to do here now is i want to make sure because you can see here it's always plus eight because of the gmt plus eight in my case because of my local time zone here so what i want to do is i want to reset this or this this here i want to reset that on uh 12 midnight that would be 12 am and the reason why i want to do that is if not later on charges will not read this properly as well in the scale so we have some work to do here so what i'm going to do here is i'm going to convert that now into a proper time of instead of 8 a.m in the morning because it's plus 8 compared to g t we're going to set it on 0 0 to basically 12 midnight so to do that i'm going to say hit return and just grab here the day js and then i'm going to say dot set hours so then we're going to set the hours back on zero comma zero comma zero comma zero so you might wonder why do we have four zeros instead of here there's only like three items here the hour the minute the seconds well in this case it is the hour minute seconds and milliseconds which is also a part of javascript how to calculate the time so we have this now and we're just going to put semicolon here and if i now cut this out here and maybe we can just convert this properly i'm going to say this copy this dks or date chart ks save that and just remove this console law here to avoid all the redundant data refresh now we get the full time here this is extremely useful for us because this is the most universal way of calculating time it just get us the time in many seconds based on the date so it's a whole formula but anyway doesn't matter so much we have now the most important information now we can start to work with it so we have this and then what i want to do here is because i realized we forgot that is we need to start converting this into a date uh object as well basically recognize that this here the x square will be based on the date however what i'm going to do here is i'm going to grab this djs that is set hours you're raising this here you're going to grab all of this or even more specifically this one here that will be the one that we eventually need and then we're going to put it on our labels so this will be the one so what we're going to do here we're going to just delete all this i'm going to say here will be this all right and then what i need as well is of course same one here however for this one this will be lucky a bit more easier because we can probably just grab this copy that put it in here and this is an array so we can just do it without the brackets if i'm not mistaken they're going to put in here price why why why this doesn't need a whole method compared to like that or that can be done immediately the reason is here we need to convert an item into a date object and this one it doesn't need it so we're going to save this and let's see what happens all right we get something here but as you can see here you can see here the prices of all the stocks which is correct it's somewhere in the 11 and all up to 12. if i look here scroll down here this is 12 dollars so if i go at the very top starting is 10 55. so that's correct 1055 here dollars there however the time here is still not converted so i'm going to convert this now i'm going to use here the fnstate object so what we're going to do now is we need to get the specific file or the date fns adapter so go to charges.org then click here on ecosystem and in ecosystem we're going to scroll down here search for the adapters and i have three options here i'm going to recommend you to use a date fns if you don't need to use any modification on it if you like to use with a modification on it for example you need to adjust the date in a way you can use the luxon library more which is a bit more easier compared to that one or at least for me so if and if ever you heard of moments here this one has been deprecated meaning it's not being updated since end of 2020 so don't touch this anymore so one or the other but if we're only going to add up one specific file or we're only going to add up the support for the date go for date fns because we won't be doing anything else and only require one javascript to load while naloxone requires two javascript files so that's why i'm going to select this one here scroll down here and then you're going to look here for the specific file it's already in here you can see here the chart js adapter date fns bundle so copy that one and then what we're going to do is we're going to go down here or back here paste this in here and once we did this if we save this nothing still happens because we didn't activate it the date fns item so that's what we're going to do here now we're going to say comma and then we're going to say here plugins and i'm going to um put in here oh sorry we don't need the plugins i'm sorry that's not necessary but what we need is here next we're going to say here the type and this type will be now time so this is basically a new object this object is called time is supported because of our javascript file for the date fns so it recognizes this so within the time we again have here we're going to say unit and the unit will be in this case the day so i'm going to save this here refresh and there we are so now we're getting the dates in here nicely from april 1 all the way to april 30. of course because we have too many items here it will auto skip values so we can remove that out of skip false so let's say here how to skip and let's do this false comma say refresh or apparently it doesn't work it just doesn't fit at all so or maybe i'm missing something that's all right i will i will look later on in the documentation about this let's save the refresh there we are what is more important for us is now to have a selection of starting date ending date and that's what we're going to do now so we're going to go uh oh up here and let's see in here let's see just above the chart box here or just below the chart box but above the canvas we're going to put in a specific input here and this input type will be equal date and the date here will be also triggering a functionality on change so when we change the value it should be triggered and then let's go and say let's say here we will have a start date filter because we want to filter off filter our values i'm just going to put it here this that's the argument and once we have this we have that one all right then i want to copy this i'm going to say another one here date and then we say here and filter and maybe for now and probably we should move this maybe up and then get the specific value but it doesn't matter for now i will not spend too much time on it i'll just say your value and then we just say this um value will be 2022 um april 1 and this will be also our min value so we will set it as a default value but also a min value which is the minimum value that we can have and i'm going to copy this and i'm going to put in here exactly the same except here will be 30 april max value will be 30 here well maybe we can do min and max doesn't matter here so we can do both so this should be max same here max and min will be one all right so refresh there we are so you can see it really restricts us now in clicking it but now if i would for example select something here nothing happens yet why we didn't activate these functionalities here to do that i'm going to do the trick here because basically you might say well if we're going to use this item in our database shouldn't we use like ajax and everything no need if we can load all of the data immediately however only do this if you have let's say 200 300 data points but if you have like 50 000 data points different story of course so scroll down here and then what we're going to do here is we're going to play here with min value i'm going to say here and the min value is also set on 2022 april 1 and what is our max value comma here comma again will be 30. so this is already set on next and main if i refresh here nothing really changes yet so now we need to work on the function because the function here should be triggered the moment we select for example here a specific starting date so i'm going to say here this or the start date and we have here the uh argument of this above so here will be the parameter and i'll just say here the date and then what i'm going to say here for the date here we're going to say uh let's see you can say here constant we're going to say start date equal so what we're really going to do here now is because the date here must still be converted in a proper uh structure of a object date object because it's still a string remember and that's we also have to set immediately the time so what we're going to do here we can just copy this or this new date you say this equals new date and then one new day will be date and then what i'm going to say here is well let's do a console log and we're going to grab here the starting date save that refresh open up developer tab so you can see here the console off let's select something all right interesting invalid date so what we have here started will be new date this and then we get here invalid date all right so let's double check here what am i missing the minimum here all right uh i guess i realize that the invalidate is because the structure in html is different compared to uh in javascript so here of the year so we need to do here the month first so all right so we're going to or sorry not even a month i think it's day month year so let's convert that back so we say here one there we are i'm going to just copy this that's why it's an invalid date so make sure you pay attention on this because later on it's php and javascript the structure is exactly the opposite of what it is in html so we say here this will be put it back on 30 the minimum and maximum is 30 here as well there we are save refresh all right so it does not conform to the format year date all right let's there we are invalidate all right and filter date is not defined all right so let me double check you what's going on all right so sorry about this i realize i make it very very um small mistake here so let's convert them all back to its original state so apparently html just has the same structure as well so what we did was correct so this was all correct so what is the issue here it's because of this here this value here refers to the entire input and not on this dot value so if i scroll down here this here the date is of course nothing more than the entire input so i need to say this that value and that was our big problem of course there we are so now we get everything we need however here we have the same issue again with the hours gmt 8 meaning here now this is 8 o'clock here so i want to reset that back into its original state of 0 0 0. so how do we do that well basically they're going to say here in the start date we can just copy this chunk of code here copy that set hours dot set hours let's save this now refresh select something now we get the official numbers all right so now we have this and this is very important because now we can do something else i want to now adjust this here so how do we adjust that so when we have selected a new date that date will be eventually impacted and updates this minimum here to whatever the date would be meaning that what we're going to say here is we're going to update something going to the my chart dot then i'm going to config because basically it's from my chart config and upon config we go to options from options skills x and then let's see an option they say dot options.skills.x dot and then we're going to say a min and the mean here will be whatever this date is including the set hours we want to put it back into its original shape over us hours which is midnight then what i want to do is i want to make sure that we update this what you're going to say here is my chart dot update save this refresh now if i should select something it should work nicely as you can see here 30 in april matching 30 april go back here 27 april matching 11 april beautiful now this one is still not working because we don't have an end date filter so what i'm going to do is while already guessed it we're just going to duplicate in time right put in there and enter this this will be our end date and proper put it on end date and here we have the min we'll be converting to maximum value and that will be of course our end date here so here what was the start date filter if i was taking was the end date filter let's go up here to search this is the end date filter copy that and put it in here save that refresh and now we should have with our database connected nicely all the items we're now in five april and let's put this back on 26 april there we are let's do this here on 11 april and then we go back here we put a 22 all right and now we say we want to go back to 30. there we are so this is basically the way how you can play around with the dates without straining too much your database if you have a limited amount of data points if you have everything below 1 000 it should be more than enough to use it immediately like this so if you enjoyed this and maybe if you have a database this is a very common issue that you have a database where the date structure is completely different i'm going to recommend you a different video in that case to as an add-on to explore for example here this one is having how to use different date formats in charges if your database consists of different formats so that you have to convert them and make sure that they're readable for charges so it's a highly recommended item if you experience this kind of cases

Channel: Chart JS

Views: 12,806

Keywords: how to filter dates from php mysql database to javascript to chart js, filter dates from php mysql database to javascript to chart js, php mysql database to javascript to chart js, mysql database in chart js, chart.js, chart js, chart js tutorial, chartjs 3, chart js 3, chart.js 3, chartjs 3.7.1, chart js 3.7.1, chart.js 3.7.1

Id: bU1HiH7ndgI

Length: 29min 52sec (1792 seconds)

Published: Fri Apr 01 2022