<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dashboard";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT p.id, p.company, p.status
FROM product p
where p.startDate between '2019-02-01' and '2019-02-08'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
// output data of each row
echo "<table border='1'>
<tr>
<th>company</th>
<th>total product count company wise</th>
<th>total count with y status</th>
<th>total count with approved status - NO</th>
</tr>";

$totalProductCountCompanyWise = array();
$countAllY = 0;

while($row = mysqli_fetch_array($result)) {
$key = $row['company'];
if(!array_key_exists($key,$totalProductCountCompanyWise)){
$totalProductCountCompanyWise[$key] = 1;
} else{
$totalProductCountCompanyWise[$key] += 1;
}

if($row['status'] == "y"){
$countAllY++;
}

$sql2 = "SELECT p.id, p.company, p.status , ps.approved
FROM product p
join productstatus ps on p.id = ps.id
where p.company = '".$key."' and ps.id = '".$row['id']."' ";
$result2 = mysqli_query($conn, $sql2);
$countNO = 0;
while($row2 = mysqli_fetch_array($result2)) {
if($row2['approved'] == "no"){
$countNO++;
}
}

$companyData = array(
array("company" => $row['company'],
"totalProductCountCompanyWise" => $totalProductCountCompanyWise[$key],
"totalCountWithYStatus" => $row['company'],
"totalCountWithApprovedStatusNO" => $row['company']
)
);


echo "<tr>";
echo "<td>" . $row['company'] . "</td>";
echo "<td>" . $totalProductCountCompanyWise[$key] . "</td>";
echo "<td>" . $countAllY . "</td>";
echo "<td>" . $countNO . "</td>";
echo "</tr>";
}
echo "</table>";
}
else {
echo "0 results";
}
mysqli_close($conn);

//tables for reference -
/*
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`company` varchar(255) NOT NULL,
`productID` int(11) NOT NULL,
`status` varchar(255) NOT NULL,
`startDate` datetime NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `product` (`id`, `company`, `productID`, `status`, `startDate`)
VALUES
(1, 'ABC', 13245, 'y', '2019-02-01 00:00:00'),
(2, 'amazon', 13215, 'n', '2019-02-02 00:00:00'),
(3, 'google', 13345, 'y', '2019-02-03 00:00:00'),
(4, 'amazon', 13145, 'y', '2019-02-04 00:00:00'),
(5, 'amazon', 13145, 'y', '2019-02-04 00:00:00'),
(6, 'google', 13188, 'n', '2019-02-07 00:00:00'),
(7, 'IBM', 13177, 'n', '2019-02-08 00:00:00');

ALTER TABLE `product`
ADD PRIMARY KEY (`id`);
ALTER TABLE `product`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

CREATE TABLE `productstatus` (
`id` int(11) NOT NULL,
`approved` varchar(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `productstatus` (`id`, `approved`)

VALUES(1, 'yes'),(2, 'yes'),(3, 'no'),
(4, 'yes'),(5, 'no'),(6, 'yes'),(7, 'yes');

ALTER TABLE `productstatus`
ADD PRIMARY KEY (`id`);

ALTER TABLE `productstatus`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
*/

?>

<?php
echo "Output Should be like below - ";
echo "</br>";
echo "</br>";
echo "<table border='1'>
<tr>
<th>company</th>
<th>total product count company wise</th>
<th>total count with y status</th>
<th>total count with approved status - NO</th>
</tr>
<tr>
<th>ABC</th>
<th>1</th>
<th>1</th>
<th>0</th>
</tr>
<tr>
<th>amazon</th>
<th>3</th>
<th>2</th>
<th>1</th>
</tr>
<tr>
<th>Google</th>
<th>2</th>
<th>1</th>
<th>1</th>
</tr>
<tr>
<th>IBM</th>
<th>1</th>
<th>0</th>
<th>0</th>
</tr>
";
?>