This code works fine if I replace the $s variable in the value being assigned to the $query variable with a valid site. The echo at the beginning of the foreach statement works correctly.
Looking at the apache error log I see the following:
[Mon Oct 26 16:16:36 2009] [error] [client 10.1.13.253] PHP Warning: oci_execute() [<a href='function.oci-execute'>function.oci-execute</a>]: ORA-00911: invalid character in C:\\htmlroot\\reports\\dailyoperationssummary.php on line 75, referer: http://site.com/reports/paradailyoperationssummary.php
[Mon Oct 26 16:16:36 2009] [error] [client 10.1.13.253] PHP Warning: oci_fetch_all() [<a href='function.oci-fetch-all'>function.oci-fetch-all</a>]: ORA-24374: define not done before fetch or execute and fetch in C:\\htmlroot\\reports\\dailyoperationssummary.php on line 77, referer: http://site.com/reports/paradailyoperationssummary.php
Any assistance would be greatly appreciated. I am hoping this is just a stupid coding error on my part.
<?php
$sites=$_POST["sites"];
$sdate=$_POST["sdate"];
$edate=$_POST["edate"];
$c = oci_connect('user', 'pass', 'server');
foreach($sites as $s){
echo $s;
$query = '
With
sales as(
Select site_no,tran_dt,gross_sales_amt,Return_amt,tran_markdown_amt as discount,sum(gross_sales_amt-return_amt-tran_markdown_amt)as net_sales,sum(tax_amt1+tax_amt2+tax_amt3+tax_amt4) as Total_Tax,sum((tax_amt1+tax_amt2+tax_amt3+tax_amt4)+(gross_sales_amt-return_amt-tran_markdown_amt)) as Total
from dlysite
group by site_no,tran_dt,gross_sales_amt,Return_amt,tran_markdown_amt
),
cash as
(select site_no,tran_dt,name,amount
from dlysite_tender
where name=\'CASH_TNDR\'
),
visa as
(select site_no,tran_dt,name,amount
from dlysite_tender
where name=\'CREDIT1_TNDR\'
),
master as
(select site_no,tran_dt,name,amount
from dlysite_tender
where name=\'CREDIT2_TNDR\'
),
amex as
(select site_no,tran_dt,name,amount
from dlysite_tender
where name=\'CREDIT3_TNDR\'
),
disc as
(select site_no,tran_dt,name,amount
from dlysite_tender
where name=\'CREDIT4_TNDR\'
),
jcb as
(select site_no,tran_dt,name,amount
from dlysite_tender
where name=\'CREDIT5_TNDR\'
),
debit as
(select site_no,tran_dt,name,amount
from dlysite_tender
where name=\'MISCT10_TNDR\'
)
select t1.site_no,t1.tran_dt,t1.gross_sales_amt,t1.Return_amt,t1.discount,t1.net_sales,t1.Total_Tax,t1.Total,t2.amount as cash,t3.amount as visa,t4.amount as master,t6.amount as disc,sum(coalesce(t3.amount,0)+coalesce(t4.amount,0)+coalesce(t6.amount,0)) as visa_mc_disc,t5.amount as amex,t7.amount as jcb,t8.amount as debit
from sales t1
left Join cash t2
ON (t1.site_no=t2.site_no and t1.tran_dt=t2.tran_dt)
left Join visa t3
ON (t1.site_no=t3.site_no and t1.tran_dt=t3.tran_dt)
left Join master t4
ON (t1.site_no=t4.site_no and t1.tran_dt=t4.tran_dt)
left Join amex t5
ON (t1.site_no=t5.site_no and t1.tran_dt=t5.tran_dt)
left Join disc t6
ON (t1.site_no=t6.site_no and t1.tran_dt=t6.tran_dt)
left Join jcb t7
ON (t1.site_no=t7.site_no and t1.tran_dt=t7.tran_dt)
left Join debit t8
ON (t1.site_no=t8.site_no and t1.tran_dt=t8.tran_dt)
Where t1.tran_DT between TO_DATE (\'12/01/2008\',\'mm-dd-yyyy\') and TO_DATE (\'12/03/2008\',\'mm-dd-yyyy\')
and t1.site_no=$s
group by t1.site_no,t1.tran_dt,t1.gross_sales_amt,t1.Return_amt,t1.discount,t1.net_sales,t1.Total_Tax,t1.Total,t2.amount,t3.amount,t4.amount,t6.amount,t5.amount,t7.amount,t8.amount
order by site_no,tran_dt
';
$stmt = oci_parse($c,$query);
oci_execute($stmt);
$nrows = oci_fetch_all($stmt,$results);
if($nrows>0){
echo "<table border><tr><th>Site No</th><th>Tran DT</th><th>Gross Sales</th><th>Returns</th><th>Discounts</th><th>Net Sales</th><th>Tax</th><th>Total</th><th>Cash</th><th>Visa</th><th>Master</th><th>Disc</th><th>Visa Master Discover</th><th>Amex</th><th>JCB</th><th>Debit</th>";
for ($i=0;$i<$nrows;$i++){
echo"</tr>\n";
foreach($results as $data){
echo "<td>$data[$i]</td>\n";
}
echo "</tr>\n";
}
echo "</table>";
}
}
?>