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
==================