Writing results of a SQl query in Excel sheet using PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nanditagokhale
Forum Newbie
Posts: 4
Joined: Tue May 12, 2009 10:17 am

Writing results of a SQl query in Excel sheet using PHP

Post by nanditagokhale »

Hello everybody,
In my PHP code for generating a report in excel sheet, I have the following two queries.

Code: Select all

 
$orders = func_query("SELECT $sql_tbl[order_details].productid, $sql_tbl[order_details].product_options, avail, $sql_tbl[orders].date, count($sql_tbl[order_details].productid ) 
FROM $sql_tbl[order_details],$sql_tbl[products],$sql_tbl[orders]
WHERE $sql_tbl[order_details].productid =$prod_code
AND $sql_tbl[order_details].productid = $sql_tbl[products].productid
AND $sql_tbl[order_details].orderid = $sql_tbl[orders].orderid
AND $sql_tbl[orders].date <$large_number
AND $sql_tbl[orders].date >$small_number
GROUP BY product_options");
 
$countsold=func_query("select count($sql_tbl[order_details].productid)
FROM $sql_tbl[order_details],$sql_tbl[products],$sql_tbl[orders]
WHERE $sql_tbl[order_details].productid =$prod_code
AND $sql_tbl[order_details].productid = $sql_tbl[products].productid
AND $sql_tbl[order_details].orderid = $sql_tbl[orders].orderid
AND $sql_tbl[orders].date <$large_number
AND $sql_tbl[orders].date >$small_number GROUP BY product_options");
 
For writing the results of the first query in excel, I am using the following code which works fine.
foreach ($orders as $value) {
$filename ="Report.xls";
$export_file = "/home/expressi/public_html/store/admin/".$filename;
$fp=fopen($export_file,"wb");
fwrite($fp,$value["productid"]);
fwrite($fp,$value["product_options"]);
}
However, for displaying the results of the second query, I am facing a problem with the following code
 
for ( $cnt1 = 1; $cnt1 <= count($countsold) ; $cnt1 += 1) {
 
fwrite($fp,"'$countsold[$cnt1][count($sql_tbl[order_details].productid)]'"); 
 }
 
This does not write the correct value of count. Can you suggest something?
Last edited by Benjamin on Tue May 12, 2009 11:38 am, edited 1 time in total.
Reason: Added [code=php] tags.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Writing results of a SQl query in Excel sheet using PHP

Post by Darhazer »

It seems that you are performing the PHP count() function instead of retrieving the count, returned by MySQL. (var_)dump the $countsold[0] variable
Probably the last line should be something like:

Code: Select all

fwrite($fp,"'$countsold[$cnt1]['count']);
And best, give the column an alias, for example:

Code: Select all

$countsold=func_query("select count($sql_tbl[order_details].productid) as totalCount
FROM $sql_tbl[order_details],$sql_tbl[products],$sql_tbl[orders] ...
Than you can use:

Code: Select all

fwrite($fp,"'$countsold[$cnt1]['totalCount']);
Last edited by Benjamin on Thu May 14, 2009 4:22 pm, edited 1 time in total.
Reason: Changed code type from text to php.
nanditagokhale
Forum Newbie
Posts: 4
Joined: Tue May 12, 2009 10:17 am

Re: Writing results of a SQl query in Excel sheet using PHP

Post by nanditagokhale »

Thanks for your help :)
nanditagokhale
Forum Newbie
Posts: 4
Joined: Tue May 12, 2009 10:17 am

Writing to an excel sheet using PHP

Post by nanditagokhale »

PHPExcel made things very easy...thanks!!!
Post Reply