Jump to content

Windows, PHP 5.2.14 and Oracle 10g


stockton

Recommended Posts

I get no results if I run the following program, but if I cut and paste the echoed SQL into Apex SQL Commands I do get the results that I am after.

Please tell me what I have done wrong?

<?php
global $conn;

/*
* Main body starts here
*
*/
    echo "Test for 20101207 <br />";

    $conn=oci_connect("s7s", "big", "localhost");
    if ( ! $conn )
        {
        $e = oci_error();  // For oci_connect errors pass no handle
        echo "Unable to OCILogon to database. ".$e["message"];
        die();
        }
    else
        {
        echo "Connected<br />";
        }

    $Event = "203";
    $MemberNum = "2020230335"; // "2020000669";
    $StartDate = "2010-04-01 00:00:00";
    $EndDate   = "2010-10-28 23:59:59";
    $TablesArray = array(1, 3);
    $TablesCount = 2;

    $Datum = date("Y-m-d H:i:s");    /* 2006-03-17 23:22:32 */
    $TableTickets = $SlotsTickets = $TableTurnover = $SlotsTurnover = $BonusTickets = $TicketsAlreadyIssued = 0;
    $TableTickets = $SlotsTickets = $TableTurnover = $SlotsTurnover = 0;

    $SQL  = "SELECT trans_code,trans_table_name,SUM(TRANS_TURNOVER) TablesTurnover ";
    $SQL .= "FROM TRANSACT ";
    $SQL .= "join tables on trans_table_name = tbl_code ";
    $SQL .= "where TRANS_CODE = '".$MemberNum."' AND ";
    $SQL .= "TRANS_DATE BETWEEN TO_DATE('".$StartDate."','YYYY/MM/DD HH24:MI:SS') AND ";
    $SQL .= "TO_DATE('$EndDate','YYYY/MM/DD HH24:MI:SS')";
    $i = 0;
    if ($TablesCount > 0)
        {
        $SQL .= " AND (";
        }
    else
        {
        $SQL .= ";";
        }
    while ($i < $TablesCount)
        {
        $SQL .= "tbl_pit = '";
        $SQL .= $TablesArray[$i];
        $i++;
        if ($i < $TablesCount) $SQL .= "' or ";
        else $SQL .= "') Group by trans_code, trans_table_name;";
        }

    echo "Tables<br />".$SQL."<br />";

    $stmt = oci_parse($conn, $SQL);
    oci_execute($stmt);
    while ($row = oci_fetch_row($stmt))
           echo htmlentities($row)." ";
?>

 

Result from program/script is:-

Test for 20101207
Connected
Tables
SELECT trans_code,trans_table_name,SUM(TRANS_TURNOVER) TablesTurnover FROM TRANSACT join tables on trans_table_name = tbl_code where TRANS_CODE = '2020230335' AND TRANS_DATE BETWEEN TO_DATE('2010-04-01 00:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2010-10-28 23:59:59','YYYY/MM/DD HH24:MI:SS') AND (tbl_pit = '1' or tbl_pit = '3') Group by trans_code, trans_table_name;

 

result from Apex is:-

2020230335	PB02	25093333.33
2020230335	AR25	6708333.34
2020230335	AR18	0
2020230335	PB08	21054167.21
2020230335	AR20	0
2020230335	AR16	0
2020230335	AR19	0
2020230335	AR15	1590458.33
2020230335	BJ20	0
2020230335	PB07	2672066.67

 

 

Link to comment
Share on other sites

  • 1 month later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.