CODEWITHSUNDEEP
phparrays
Veeza
Veeza

Reputation: 63

PHP sort data from mysql to array by date

I want to display the stats for a user of the last 30 days including today. But in my database are only stats for a specific day, if the user did any action. If not the date is just not there, and the value for this day just needs to be 0. This is my current approach, the data is received from the database but it doesn't get inserted correct.

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt2 = $conn->prepare("SELECT * FROM all_stats WHERE user_id = :user_id ORDER BY date DESC LIMIT 30"); 
$stmt2->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$stmt2->execute();

$rows2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
// Create array with dates of the last 29 days and today.
for($i=29;$i>=0;$i--){
    $dates[$i] = date("Y-m-d", strtotime("-".$i." day" ));
}
// Check what rows are available
$y=0;
foreach ($rows2 as $row) {
   $daterow[$y] = $row['date'];
   $us[$y] = $row['US'];
   $ca[$y] = $row['CA'];
   $au[$y] = $row['AU'];
   $gb[$y] = $row['GB'];
   $de[$y] = $row['DE'];
   $y++;                       
}
$size = count($us);
for ($i = 0; $i<=29;$i++){
    if ( strtotime( $daterow[$i]) != strtotime($dates[$i]) ){
        $daily[$i] = 0;
    } else {
        $daily[$i] = $us[$i];
    }
}

The test data is: Today data available, yesterday empty, day before yesterday data available.

Output, only the data for today is inserted (at [0]) Wrong

Array ( [0] => 333 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 [6] => 0 [7] => 0 [8] => 0 [9] => 0 [10] => 0 [11] => 0 [12] => 0 [13] => 0 [14] => 0 [15] => 0 [16] => 0 [17] => 0 [18] => 0 [19] => 0 [20] => 0 [21] => 0 [22] => 0 [23] => 0 [24] => 0 [25] => 0 [26] => 0 [27] => 0 [28] => 0 [29] => 0 )

Upvotes: 0

Views: 108

Answers (1)

trincot
trincot

Reputation: 350137

The problem is in that you use the same index in both the $daterow and $dates array while going through your last loop. That is not what you want, as the matching values will not be at the same index.

I would suggest to do this, using array_search:

// Create array with dates of the last 29 days and today.
for($i=29;$i>=0;$i--){
    $dates[$i] = date("Y-m-d", strtotime("-".$i." day" ));
    // at the same time initialise $daily
    $daily[$i] = 0;
}
// Check what rows are available
$y=0;
foreach ($rows2 as $row) {
    $daterow[$y] = $row['date'];
    //... etc..
    // Now search the $dates for the date we just read from the DB:
    $i = array_search($row['date'], $dates);
    if ($i !== false) {
        // There was a match, so add up what we have for US:
        $daily[$i] += $row['US'];
    }
    $y++;
}

The above assumes that the $row['date'] has the date formatted just like the elements of $dates, i.e. YYYY-MM-DD. If this is not the case, you might need to do some adjustments, but the idea should work.

Upvotes: 2

Related Questions