I am trying to loop through some data stored in arrays, then update records in MySQL for an entire month. I can retrieve the correct data for a day, but not the entire month.
I have a form with a calendar where a user can select a date. Currently, I have it coded so that a user can select any date (month, day, year) and the days in that month are determined (I'm sure there is a more efficient way to do this). If January 18, 2019 is selected, for example, 31 days are returned (since there are 31 days in January).
What is happening though, is that the data for the date selected is getting inserted into the table for January 1 only (e.g. if I select January 18, 2019 from the calendar, the data for January 18, 2019 is inserted, but for January 1, 2019. no other data is inserted). What I need to happen is for the data for the entire month, whatever exists up until the current day, to be inserted into the correct day of the month. If I change the date selected from the calendar, the data for that date gets inserted, but again, it gets inserted for January 1.
I need to be able to loop through the days of the month, get all sites which have data (this works), then get the data for each site for each day of the month (this works for each day, but I need the entire month at once).
importDate is the date selected in the calendar. If I echo this, it returns the date I have selected.
This is how I determine if importDate is set and what to do if it is or isn't, plus get number of days for the month selected (e.g. selecting January 18, 2019 returns 31 days for January).
if(!isset($_GET['importDate'])) {
$importDate = date('Y-m-d', strtotime('yesterday'));
$mthDays = date('t',strtotime($importDate));
}else{
$importDate = $_GET['importDate'];
$dexpl = explode('-',$importDate);
$mthDays = date('t',strtotime($importDate));
}
I then loop through the days of the month (probably not doing this in the right place), then loop through my sites. All sites are returned. getSites is a function that gets all sites.
Inside the loop to get the sites, I connect to a separate database for software we are using, get the data from that db for each site, do some calculations, then move to the next site.
I then call a function that will update my table as mentioned, with all the data for the entire month. Currently, as described above, I am only getting data for one day to insert.
$y = 0;
while ($y <= $mthDays) {
// Loop to get all sites
for($x=0; $x < $getSites; $x++)
{
// Code here for connecting to database for software we are using, get the data from that db for each site, do some calculations...
// The variables being passed into the function in the code directly below are the site, the date, plus calculated items not shown here.
$updateManualStatsDaily = $statsDao->updateStats($getSites[$x]['site_id'],$total_sessions,$count_guests,$count_unique_guests,$total_minutes,$dateImport);
}
$y++;
$dateImport = date('Y-m-d',strtotime($dexpl[0]."-".$dexpl[1]."-".$y));
}
Here is my function:
public function updateStats($hz_site_id,$total_sessions,$count_guests,$count_unique_guests,$total_minutes,$updateDate){
try {
$stm = $this->dbSt->prepare("UPDATE table set sessions_wifi = :sessions, wifi_users = :wifiUsers, wifi_unique_users = :wifiUniqueUsers, wifi_time = :wifiTime
where site_id = :siteId and circ_date = :statsDate ");
$stm->bindParam(':siteId',$hz_site_id);
$stm->bindParam(':sessions',$total_sessions);
$stm->bindParam(':wifiUsers',$count_guests);
$stm->bindParam(':wifiUniqueUsers',$count_unique_guests);
$stm->bindParam(':wifiTime',$total_minutes);
$stm->bindParam(':statsDate',$updateDate);
if($stm->execute()){
return true;
}else{
echo $stm->errorInfo();
}
}catch (PDOException $e) {
echo $e->getMessage();
}
return false;
}
Note: I also have this coded to run for one day (selected from the calendar) which works. It's getting the days of the month and trying to add the data for each day that's giving me trouble.
If someone can help, I would greatly appreciate it! Thanks.
Presuming the records for each
$updateDate
exist in the database already, otherwise you would need to execute anINSERT
query instead of anUPDATE
.The main issue I see with your code is in the
for
loop on$getSites
.Which implies that
$getSites
is an array and not an integer. Example: https://3v4l.org/AK8kaYou would want to change it to Example: https://3v4l.org/0qbWf
or better still use
foreach
, Example: https://3v4l.org/FTOCI.There are also a few issues with order of operations, that should be improved.
First
$dateImport
is not defined until after the initial loop, this results in null being sent to the database on the first iteration.As well since
$y
starts at0
, this would create a date like2019-01-0
, which would be interpreted by PHP as the last day of the previous month;2018-12-31
and may produce unexpected results. Example: https://3v4l.org/TiHtu.In addition, it appears
$dexpl
may not be defined if$_GET['importDate']
is not set, which would create a date like--0
, which would be interpreted by PHP as1970-01-01
Second you only need to call
prepare
andbindParam
once on a given query operation. I suggest declaring the prepared statement to an object property and switch to usingPDOStatement::bindValue()
instead. This is becausePDOStatement::bindParam()
will use the bound variable reference to retrieve the value, which you are not utilizing in the given context ofupdateStats()
.For example, notice the order of operations applied that
bindParam
is called prior to changing the$v
value.Then you just need to separate the concerns in your
updateStats
method to only prepare the statement if it has not been already.Lastly I strongly suggest instead of using
date('t')
to useDatePeriod()
. Which will give you a full representation of the days that are being iterated over. As opposed to iterating by the number of days of the specified$importDate
, which would include days before the specified$importDate
and days in the future.Edit: After reading your question more thoroughly, I realized you wanted to import the entire given month. Not just from the specified date to the current date. But also stop at a maximum of current date. So I updated the logic to reflect the desired behavior.
Example: https://3v4l.org/S0H6s
Results from Current Date of "2019-01-01": https://3v4l.org/ddQG0