I've got several SQL statements that run in succession. If I paste
them into a phpMyAdmin SQL window, they run quickly and correctly.
However, when I use them in a .PHP script, they run without error but
one fails to do it's job.

My PHP code is:

$sql = "TRUNCATE TABLE TransactionRptTrans;";
$dummy = $this->db->r_query($sql);

$sql = "TRUNCATE TABLE TransactionRptDetail;";
$dummy = $this->db->r_query($sql);

$sql = "INSERT INTO TransactionRptTrans(TransactionID, DomainID,
DomainName, Amount, ShipAmount, ShipSurcharge) SELECT t.TransactionID,
t.DomainID, d.DomainName, t.Amount, t.ShipAmount, d.ShipSurcharge FROM
Transaction t LEFT OUTER JOIN Domain d ON t.DomainID=d.DomainID WHERE
DATE_FORMAT(TransactionDate, '%Y-%m-%d') BETWEEN '".$from_date."' AND
'".$to_date."';";
$dummy = $this->db->r_query($sql);

$ssql = "INSERT INTO TransactionRptDetail(TransactionID, DomainID,
TransactionDetailID, ProductCode, Price, Qty, charge_fee,
merch_amount, fc_amount) SELECT t.TransactionID, t.DomainID,
td.TransactionID, td.ProductCode, td.Price, td.Qty, if(p.ReqShipping=1
AND p.FreeShipping=0, 1, 0) AS charge_fee, 0 AS merch_amount, 0 AS
fc_amount FROM TransactionRptTrans t INNER JOIN TransactionDetail td
ON t.TransactionID = td.TransactionID LEFT JOIN Product p ON
td.ProductID=p.ProductID;";
$dummy = $this->db->r_query($sql);

There's more after this but the last INSERT statement doesn't insert
anything, and no records in that table, it doesn't matter what happens
afterward.

I tried putting BEGIN TRANSACTION's and COMMIT's around each statement
and that made no difference. I also checked for errors and there
weren't any.

Can anyone see why the last INSERT doesn't insert?

Thanks,
Bill

billbois at gmail dot com wrote:
> I've got several SQL statements that run in succession. If I paste
> them into a phpMyAdmin SQL window, they run quickly and correctly.
> However, when I use them in a .PHP script, they run without error but
> one fails to do it's job.
>
> My PHP code is:
>
> $sql = "TRUNCATE TABLE TransactionRptTrans;";
> $dummy = $this->db->r_query($sql);
>
> $sql = "TRUNCATE TABLE TransactionRptDetail;";
> $dummy = $this->db->r_query($sql);
>
> $sql = "INSERT INTO TransactionRptTrans(TransactionID, DomainID,
> DomainName, Amount, ShipAmount, ShipSurcharge) SELECT t.TransactionID,
> t.DomainID, d.DomainName, t.Amount, t.ShipAmount, d.ShipSurcharge FROM
> Transaction t LEFT OUTER JOIN Domain d ON t.DomainID=d.DomainID WHERE
> DATE_FORMAT(TransactionDate, '%Y-%m-%d') BETWEEN '".$from_date."' AND
> '".$to_date."';";
> $dummy = $this->db->r_query($sql);
>
> $ssql = "INSERT INTO TransactionRptDetail(TransactionID, DomainID,
> TransactionDetailID, ProductCode, Price, Qty, charge_fee,
> merch_amount, fc_amount) SELECT t.TransactionID, t.DomainID,
> td.TransactionID, td.ProductCode, td.Price, td.Qty, if(p.ReqShipping=1
> AND p.FreeShipping=0, 1, 0) AS charge_fee, 0 AS merch_amount, 0 AS
> fc_amount FROM TransactionRptTrans t INNER JOIN TransactionDetail td
> ON t.TransactionID = td.TransactionID LEFT JOIN Product p ON
> td.ProductID=p.ProductID;";
> $dummy = $this->db->r_query($sql);
>
> There's more after this but the last INSERT statement doesn't insert
> anything, and no records in that table, it doesn't matter what happens
> afterward.
>
> I tried putting BEGIN TRANSACTION's and COMMIT's around each statement
> and that made no difference. I also checked for errors and there
> weren't any.
>
> Can anyone see why the last INSERT doesn't insert?
>
> Thanks,
> Bill
>

Check the result of your query for errors.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>
> Check the result of your query for errors.
>

Have done, and there aren't any. It's almost like the last query runs
before the previous one.

Thanks,
Bill

On Wed, 26 Sep 2007 21:31:12 +0200, billbois at gmail dot com
<bbois@hotmail.com> wrote:

> I've got several SQL statements that run in succession. If I paste
> them into a phpMyAdmin SQL window, they run quickly and correctly.
> However, when I use them in a .PHP script, they run without error but
> one fails to do it's job.
>
> My PHP code is:
>
> $sql = "TRUNCATE TABLE TransactionRptTrans;";
> $dummy = $this->db->r_query($sql);
>
> $sql = "TRUNCATE TABLE TransactionRptDetail;";
> $dummy = $this->db->r_query($sql);
>
> $sql = "INSERT INTO TransactionRptTrans(TransactionID, DomainID,
> DomainName, Amount, ShipAmount, ShipSurcharge) SELECT t.TransactionID,
> t.DomainID, d.DomainName, t.Amount, t.ShipAmount, d.ShipSurcharge FROM
> Transaction t LEFT OUTER JOIN Domain d ON t.DomainID=d.DomainID WHERE
> DATE_FORMAT(TransactionDate, '%Y-%m-%d') BETWEEN '".$from_date."' AND
> '".$to_date."';";
> $dummy = $this->db->r_query($sql);
>
> $ssql = "INSERT INTO TransactionRptDetail(TransactionID, DomainID,
> TransactionDetailID, ProductCode, Price, Qty, charge_fee,
> merch_amount, fc_amount) SELECT t.TransactionID, t.DomainID,
> td.TransactionID, td.ProductCode, td.Price, td.Qty, if(p.ReqShipping=1
> AND p.FreeShipping=0, 1, 0) AS charge_fee, 0 AS merch_amount, 0 AS
> fc_amount FROM TransactionRptTrans t INNER JOIN TransactionDetail td
> ON t.TransactionID = td.TransactionID LEFT JOIN Product p ON
> td.ProductID=p.ProductID;";
> $dummy = $this->db->r_query($sql);

Hmmm, shouldn't that be:
$dummy = $this->db->r_query($ssql);

$ssql != $sql
--
Rik Wasmus

On Sep 26, 6:37 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Wed, 26 Sep 2007 21:31:12 +0200, billbois at gmail dot com
>
> > $dummy = $this->db->r_query($sql);
>
> Hmmm, shouldn't that be:
> $dummy = $this->db->r_query($ssql);
>

D'oh! OK, where's my dunce cap?

Thanks very much!
Bill