Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:

I am trying to get the php mysql query to return the result dynamically group by field.

For some reasons it is not working here is the code that I am using.

<?php
    $servername = "localhost";
    $username = "user";
    $password = "pass";

    try {
        $objDatabase = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
        $objDatabase->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected successfully";
        }
    catch(PDOException $e)
        {
        echo "Connection failed: " . $e->getMessage();
        }
?>

<?php 

//Process the GET data received from the previous page.
$custo = $_GET["Customer"];
$startdate = $_GET["fromdate"];
$enddate = $_GET["enddate"];
$stdate = date("Y-m-d", strtotime($startdate));
$endate = date("Y-m-d", strtotime($enddate));


$basequery = "SELECT cust, manu, model, serial, capacity, firmware, method, date, stime, etime, wks, result, COUNT(*) AS total FROM hdds WHERE cust = '".$custo."' and `date` >= '".$stdate."' and `date` <= '".$endate."'";

$retval = mysql_query( $basequery, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}

?>


    <?php 

$type="capacity";
$typeQuery = $basequery." GROUP BY ".$type;
// Perform the Query
$objDbResultByType = $objDatabase->Query($typeQuery);
echo '<div id="1000" style="display: none;">';
echo "<h3>Quality Control Checked by<br></h3><strong>";
$capacity = array();
while ($row = $objDbResultByType()) {
    echo $row['capacity']. " = " .$row['total'];
    echo "<br><strong>";
$result = "{ label: \"".$row['capacity']."\", y: " .$row['total']." },";
array_push($capacity,$result);
}
//echo $result;
$lastIndex = count($capacity)-1;
$lastValue = $capacity[$lastIndex];
$testedby[$lastIndex] = rtrim($lastValue, ',');

//Echo the capacity from array to the monitor screen.
foreach ($capacity as $result){
echo $result, '<br>';
}


    ?>

The code above gives me blank screen.

However if I run this query on mysql it returns the data, there is no problem with the query.

mysql> SELECT cust, manu, model, serial, capacity, firmware, method, date, stime, etime, wks, result, COUNT(*) AS total from hdds where cust = 'Imran-ABC' and date >= '2015-08-01' and '2015-09-14' group by date;
+-----------+------------------------------------------+------------+----------+-----------------------------+----------+--------+------------+----------+----------+------+-----------+-------+
| cust      | manu                                     | model      | serial   | capacity                    | firmware | method | date       | stime    | etime    | wks  | result    | total |
+-----------+------------------------------------------+------------+----------+-----------------------------+----------+--------+------------+----------+----------+------+-----------+-------+
| ABC | Seagate Barracuda 7200.7 and 7200.7 Plus | ST340014AS | 5MQ3DJPM | 40000000000 bytes [40.0 GB] | 8.12     | zero   | 2015-08-26 | 18:56:29 | 18:56:29 | 89   | Succeeded |     1 |
| ABC | Seagate Barracuda 7200.7 and 7200.7 Plus | ST340014AS | 5MQ3DJPM | 40000000000 bytes [40.0 GB] | 8.12     | zero   | 2015-09-01 | 18:56:29 | 18:56:29 | 89   | Succeeded |    27 |
| ABC | Seagate Barracuda 7200.7 and 7200.7 Plus | ST340014AS | 5MQ3DJPM | 40000000000 bytes [40.0 GB] | 8.12     | zero   | 2015-09-02 | 20:04:19 | 20:04:19 | 36   | Succeeded |     2 |
+-----------+------------------------------------------+------------+----------+-----------------------------+----------+--------+------------+----------+----------+------+-----------+-------+
3 rows in set, 1 warning (0.00 sec)

mysql>

Can someone please help what I am doing wrong and why this is not working?

Thanks well in advance for your help.

share|improve this question
    
<?php not <? php. – Jon Stirling Sep 4 at 11:04
    
Thanks fixed <?php but still same problem – user2107349 Sep 4 at 11:06
1  
You are mixing the (deprecated) mysql_* calls with PDO. Also, check logs if you get a blank screen, your error message will be there. – Bart Friederichs Sep 4 at 11:07
1  
In your php you are grouping by capacity while in your query you use date. Is it intentional? – bumpy Sep 4 at 11:07
    
bumpy, this is intentional – user2107349 Sep 4 at 11:15
up vote 0 down vote accepted

Managed to fix it by using following.

<?php

$type="capacity";
$typeQuery = $basequery." GROUP BY ".$type;
// Perform the Query
$objDbResultByType = $objDatabase->Query($typeQuery);
foreach ($objDbResultByType as $row) {
preg_match('/\[(.*?)\]/', $row['capacity'], $matches);
if (isset($matches[1])) {
    $row['capacity'] = $matches[1];
}
    echo $row['capacity']. " = " .$row['total'];
    echo "<br><strong>";
$result = "{ label: \"".$row['capacity']."\", y: " .$row['total']." },";
array_push($capacity,$result);

}

$lastIndex = count($capacity)-1;
$lastValue = $capacity[$lastIndex];
$testedby[$lastIndex] = rtrim($lastValue, ',');

//Echo the capacity from array to the monitor screen.
foreach ($capacity as $result){
echo $result, '<br>';
}

?>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.