Hi All
I have been trying to solve my query problem for days with no luck. Please help
I'm trying to build a pivot table query from 2 part query in php.
Both query runs ok in mysql query browser but once I place the query into php it doesn't work.
Here is the my code
I have a table I want to turn into a pivot table
+------+---------+-------------+--------+
| id | product | salesperson | amount |
+------+---------+-------------+--------+
| 1 | radio | bob | 100.00 |
| 2 | radio | sam | 100.00 |
| 3 | radio | sam | 100.00 |
| 4 | tv | bob | 200.00 |
| 5 | tv | sam | 300.00 |
| 6 | radio | bob | 100.00 |
+------+---------+-------------+--------+
//Building First Part of the Query to get all the columns
$sql = "SELECT DISTINCT
CONCAT(',SUM(IF(salesperson = "',salesperson,'",1,0)) AS `',salesperson,'`')
AS countpivotarg
FROM sales
WHERE salesperson IS NOT NULL ";
$result = $mdb2->query($sql);
$count = $result->numRows();
if ($count>0){
while ($row = $result->fetchRow()){
$q .= $row['countpivotarg']
}
}
/*This should give the following results to plug into the next query statement*/,
,SUM(IF(salesperson = "bob",1,0)) AS `bob`
,SUM(IF(salesperson = "sam",1,0)) AS `sam`
//Plug first query results into a second query statement//
$sql = 'SELECT product ' . $q . ',COUNT(*) AS Total
FROM sales
GROUP BY product WITH ROLLUP;';
$result = $mdb2->query($sql);
$count = $result->numRows();
if ($count>0){
while ($row = $result->fetchRow()){
echo "rows here";
}
}
//Results
+---------+------+------+-------+
| product | bob | sam | Total |
+---------+------+------+-------+
| radio | 2 | 2 | 4 |
| tv | 1 | 1 | 2 |
| NULL | 3 | 3 | 6 |
+---------+------+------+-------+
Any help would be greatly appreciated.
Thanks