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>";
}
}
?>

Hey.

Line #68. You try to add the variable $s directly into the string, but because it is a single-quoted string, it isn't parsed and reads as an invalid character in the query.

Replace it with this and you should be fine.

and t1.site_no='. $s .'

Alternatively, you could use heredoc syntax, which would allow you to use single and double quotes without escaping them, and to add variables directly:

$string = <<<SQL
SELECT randomThings
FROM someTable
WHERE
  someField = '$someData';
SQL;

Note: The <<<SQL and SQL; delimiters must appear EXACTLY like they are there, with no spaces, tabs, or any white-space on either side of them!
You can change the keyword tho. The SQL part.

Perfect for long SQL queries or HTML markup ;-)

Thank you very much for your help I am new to this and I appreciate the assistance. I figured it was a stupid mistake.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.