PHP multiple date condition

[FooKelvin]

Hi All,

 

how to display php table like attachment?

 

 

it mean that, there is 2 members registered in November, but it have 0 member get approve. There is 3 members registered in December, and total have 5 members get approved (2 from November + 3 from December).

 

Each Register and Approve have their individual dates.

I get all my thing wrong as i can loop only by either Register date or Approved date.

 

example.

 

member1

->Registered date : 21/11/2016 (Fall under November)

->Approved date   : 2/12/2016 (Fall under December)

 

member2

->Register date  : 23/11/2016 (Fall under November)

->Approved date: 3/12/2016 (Fall under December)

 

Thanks in advance.

 

[Barand]

Read the data into an array, indexed by month. Store counts of registered and approved for each month. Output the table from the array.

[FooKelvin]

Read the data into an array, indexed by month. Store counts of registered and approved for each month. Output the table from the array.

Hi Barand,

Thanks. I am stuck with this code.

$newarray = array(11 => array(), 12 => array(), 1 => array(), 2 => array(), 3 => array(), 4 => array(), 5 => array(), 6 => array(), 7 => array(), 8 => array(), 9 => array(), 10 => array());
$data = array();
$sql = "SELECT id,registerdate,approvedate FROM  [A_Sys].[dbo].[member]";
$stmt = sqlsrv_query($conn, $sql);
if ($stmt === false) {
    die(print_r(sqlsrv_errors(), true));
}

while (list($id,$rd,$ad) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    
    if (!isset($data[$id])) {
        $data[$id]['id'] = $id;
        $data[$id]['arr'] = $newarray;
    }
    if ($rd) { //register date month
        $item = date_format(($rd), 'm'); //m/d/y
        $data[$id]['arr'][$m][] = $item;
        var_dump($item);
    }
    if ($ad) { //approve date month
        $item2 = date_format(($ad), 'm'); //m/d/y
        $data[$id]['arr'][$m][] = $item2;
        var_dump($item2);
    }
}

?>

I var_dump item & item2.

string(2) "11" string(2) "12" string(2) "11" string(2) "12" string(2) "12"

string(2) "12" string(2) "12" string(2) "12" string(2) "11" string(2) "11"

//here is my database

id	member	registerdate	approvedate
1	mem1	2016-11-22	2016-12-22
2	mem2	2016-11-22	2016-12-22
3	mem3	2016-12-22	2016-12-22
4	mem4	2016-12-22	2016-12-22
5	mem5	2016-11-22	2016-11-22

[Barand]

try

<?php

// get data date range
$date = new DateTime();  // now
$dateto = $date->format('Y-m-01');
$datefrom = $date->sub(new DateInterval('P1Y'))->format('Y-m-01'); // minus 1 year

// initialize array
$dp = new DatePeriod($date, new DateInterval('P1M'),12);
$data = [];
foreach ($dp as $d) {
    $data[$d->format('F')] = [ 'reg' => 0, 'app' => 0 ];
}

// get data and accumulate counts in array
$sql = "SELECT 
            registerdate
          , approvedate  
        FROM datetest
        WHERE registerdate >= ? AND registerdate < ?
        ORDER BY registerdate";
        
$stmt = $pdo->prepare($sql);
$stmt->execute( [$datefrom, $dateto] );

while ($row = $stmt->fetch()) {
    if ($row['registerdate']) {
        $rm = (new DateTime($row['registerdate']))->format('F');
        $data[$rm]['reg']++;
    }
    if ($row['approvedate']) {
        $am = (new DateTime($row['approvedate']))->format('F');
        $data[$am]['app']++;
    }
}

// assemble the output
$tabledata = '';
foreach ($data as $month=>$vals) {
    $tabledata .= "<tr><td>$month</td><td>{$vals['reg']}</td><td>{$vals['app']}</td></tr>\n";
}
?>

<table>
    <thead>
    <tr><th>Month</th><th>Registered</th><th>Approved</th></tr>
    </thead>
    <tbody>
        <?=$tabledata?>
    </tbody>
</table>

Note that this processes the previous year's data. Set your date range as required.

Edited November 22, 2016 by Barand

[FooKelvin]

$date = new DateTime();  // now
$dateto = $date->format('Y-m-01');
$datefrom = $date->sub(new DateInterval('P1Y'))->format('Y-m-01'); // minus 1 year

// initialize array
$dp = new DatePeriod($date, new DateInterval('P1M'),12);
$data = array();
foreach ($dp as $d) {
    $data[$d->format('F')] = array( 'reg' => 0, 'app' => 0 );
}
//var_dump($data);
$sql = "SELECT id,registerdate,approvedate FROM  [A_Sys].[dbo].[member]  WHERE registerdate >= '2016-11-22' AND registerdate < '2016-12-22'
                ORDER BY registerdate";
$stmt = sqlsrv_query($conn, $sql,$datefrom,$dateto);
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ){
    if ($row['registerdate']) {
        $rm = (new DateTime($row['registerdate']))->format('F');
        $data[$rm]['reg']++;
    }
    if ($row['approvedate']) {
        $am = (new DateTime($row['approvedate']))->format('F');
        $data[$am]['app']++;
    }
}
// assemble the output
$tabledata = '';
foreach ($data as $month=>$vals) {
    $tabledata .= "<tr><td>$month</td><td>{$vals['reg']}</td><td>{$vals['app']}</td></tr>\n";
}
?>

<table>
    <thead>
    <tr><th>Month</th><th>Registered</th><th>Approved</th></tr>
    </thead>
    <tbody>
        <?=$tabledata?>
    </tbody>
</table>

The table head wont display when i put in 

->format('F');

[Barand]

It was working when it left the shop. Do you have error reporting turned on?
 
Your call to sqlsrv_query() does not look right. You are not using query parameters so why try to provide them? RTFM