Pivot Table Query in PHP - Please help

[chanw1]

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

 

[seanlim]

var_dump($sql) after building each query. use mysql_error to get the error message from mysql.

 

And I think you will have to use a period (.) instead of commas to join your SQL string.

 

Please also use the code tags for us to read your code more easily.