Looping through arrays in PHP to update records in MySQL but not getting all results

Question

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.

Answer 1

Presuming the records for each $updateDate exist in the database already, otherwise you would need to execute an INSERT query instead of an UPDATE.

The main issue I see with your code is in the for loop on $getSites.

for ($x=0; $x < $getSites; $x++) {
    $getSites[$x]['site_id'];
}

Which implies that $getSites is an array and not an integer. Example: https://3v4l.org/AK8ka

You would want to change it to Example: https://3v4l.org/0qbWf

$l = count($getSites);
for ($x=0; $x < $l; $x++) {
    $getSites[$x]['site_id'];
}

or better still use foreach, Example: https://3v4l.org/FTOCI.

foreach ($getSites as $site) {
    $site['site_id'];
}

---

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 at 0, this would create a date like 2019-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 as 1970-01-01

//consolidated conditional logic
$importTimestamp = strtotime(isset($_GET['importDate']) ? $_GET['importDate'] : 'yesterday');
$importDate = date('Y-m-d', $importTimestamp);

//declare dexpl and mthDays
$dexpl = explode('-', $importDate);
$mthDays = date('t', $importTimestamp);

//start at day 1, since we know mthDays will always be 28 to 31
$y = 1;    
while ($y <= $mthDays) {
    //null would be sent to the database initially otherwise
    $dateImport = date('Y-m-d', strtotime($dexpl[0]."-".$dexpl[1]."-".$y));
    $l = count($getSites);
    for ($x=0; $x < $l; $x++) {
        $updateManualStatsDaily = $statsDao->updateStats($getSites[$x]['site_id'],$total_sessions,$count_guests,$count_unique_guests,$total_minutes,$dateImport);
    }
    $y++;
}

---

Second you only need to call prepare and bindParam once on a given query operation. I suggest declaring the prepared statement to an object property and switch to using PDOStatement::bindValue() instead. This is because PDOStatement::bindParam() will use the bound variable reference to retrieve the value, which you are not utilizing in the given context of updateStats().

For example, notice the order of operations applied that bindParam is called prior to changing the $v value.

$v = 1;
$stmt->bindParam(':v', $v);
$v = 2;
$stmt->execute(); //results in sending 2 to the DB as the `:v` placeholder value

---

Then you just need to separate the concerns in your updateStats method to only prepare the statement if it has not been already.

protected function prepareStats()
{
    if (!isset($this-statsStmt)) {
        $this-statsStmt = $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');
    }

    return $this->statsStmt;
}

public function updateStats($hz_site_id,$total_sessions,$count_guests,$count_unique_guests,$total_minutes,$updateDate)
{
    try {
        $stm = $this->prepareStats();
        $stm->bindValue(':sessions', $total_sessions);
        $stm->bindValue(':wifiUsers', $count_guests);
        $stm->bindValue(':wifiUniqueUsers', $count_unique_guests);
        $stm->bindValue(':wifiTime', $total_minutes);
        $stm->bindValue(':siteId', $hz_site_id);
        $stm->bindValue(':statsDate', $updateDate);    
        if ($stm->execute()) {
            return true;
        }
        //no need for else, since we return on success already
        echo $stm->errorInfo();
    }catch (PDOException $e) {
        echo $e->getMessage();
    }

    return false;
}

---

Lastly I strongly suggest instead of using date('t') to use DatePeriod(). 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

//use !empty to prevent conflict of from today to today
if (!$start = date_create(!empty($_GET['importDate']) ? $_GET['importDate'] : 'yesterday')) {
    die('Invalid Date Supplied');
}
$today = date_create();
if ($start > $today) {
    die('Dates set in the future are not permitted');
}
//start at the first day of the month
$start->modify('first day of this month')->setTime(0,0,0);
//validate the current year and month to prevent exceeding current date
if ($start->format('Y-m') === $today->format('Y-m')) {
    $end = date_create('yesterday')->setTime(23,59,59);
    if ($end < $start) {
        //yesterday was previous month - use current start date
        $end = clone $start;
    }
} else {
    $end = clone $start;
    $end->modify('last day of this month');
}
//always end at end of day
$end->setTime(23,59,59);
$importDates = new \DatePeriod($start, new \DateInterval('P1D'), $end);

//we can use foreach on $getSites which is faster
//switched order of operations, since if there are no sites, we don't need to continue.
foreach ($getSites as $site) {
    foreach ($importDates as $importDate) {
        //logic to calculate site data to UPDATE...
        //...
        $statsDao->updateStats( 
            $site['site_id'],
            $total_sessions,
            $count_guests,
            $count_unique_guests,
            $total_minutes,
            $importDate->format('Y-m-d')
        );
    }
}

I would like to mention I have no idea where $total_sessions,
$count_guests, $count_unique_guests, $total_minutes are coming from, so this could represent additional issues that may need to be addressed.

Results from Current Date of "2019-01-01": https://3v4l.org/ddQG0

Import Dates: "2019-01-18"
Dates set in the future are not permitted


Import Dates: "2019-01-01" (current day)
2019-01-01


Import Dates: "2018-12-01", "2018-12-20", ""
2018-12-01
2018-12-02
2018-12-03
2018-12-04
2018-12-05
2018-12-06
2018-12-07
2018-12-08
2018-12-09
2018-12-10
2018-12-11
2018-12-12
2018-12-13
2018-12-14
2018-12-15
2018-12-16
2018-12-17
2018-12-18
2018-12-19
2018-12-20
2018-12-21
2018-12-22
2018-12-23
2018-12-24
2018-12-25
2018-12-26
2018-12-27
2018-12-28
2018-12-29
2018-12-30
2018-12-31