PHP Oracle Join

PHP Oracle Join

flashflash Posts: 2Questions: 0Answers: 0
edited January 2013 in Feature requests
Hi there,

I'm Guntur, i'm new here..
Please help me to solve my PHP Oracle Join problems,

I was look the server side code for PHP Oracel here
http://www.datatables.net/development/server-side/php_oracle

But, there is doesn't support JOIN statement yet?
Is there any body was did it?

Thanks..

Replies

  • ashishkulkarniashishkulkarni Posts: 9Questions: 0Answers: 0
    I would guess that you just need to try it Guntur. Should be straightforward.
  • flashflash Posts: 2Questions: 0Answers: 0
    Thanks for your reply ashishkulkarni..

    I was tried, and here is my code based on http://www.datatables.net/development/server-side/php_oracle with little modification..but, it's still doesn't work..

    If you're already doing this, would you teach me how? thank you

    [code]
    <?php

    $aColumns = array( "A.WHATS_NEW_ID",
    "A.WHATS_NEW_TITLE",
    "A.CONTENT_TITLE",
    "A.WHATS_NEW_CREATE_DATE",
    "A.WHATS_NEW_ACTIVE_STATUS");

    $aColumnsInner = array( "A1.WHATS_NEW_ID",
    "A1.WHATS_NEW_TITLE",
    "B1.CONTENT_ID as CONTENT_ID",
    "B1.CONTENT_TITLE",
    "A1.WHATS_NEW_CREATE_DATE",
    "A1.WHATS_NEW_ACTIVE_STATUS");

    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "A1.WHATS_NEW_ID";

    /* Join */
    $sJoin = "LEFT JOIN DRUPAL.TBL_CONTENT B ON A.CONTENT_ID = B.CONTENT_ID";
    $sJoinInner = "LEFT JOIN DRUPAL.TBL_CONTENT B1 ON A1.CONTENT_ID = B1.CONTENT_ID";

    /* DB table to use */
    $sTable = $gaSql['tablenamespace'].".TBL_WHATS_NEW A1";

    /*
    * SQL queries
    * Get data to display
    */
    //Inner sql - not being fetched by itself.
    $sQueryInner = "SELECT ".implode(', ', $aColumnsInner).", row_number() over (".$sOrder.") rowsNumerator FROM ".$sTable." ".$sJoinInner." ".$sWhere;
    $sQueryFinal = "SELECT ".implode(', ', $aColumns)." FROM (".$sQueryInner.") A ".$sJoin." ".$sLimit." ORDER BY rowsNumerator";


    /* Data set length after filtering */
    $sQueryFinalCount = "SELECT COUNT(*) as \"totalRowsCount\" FROM (".$sQueryFinal.") A";

    $iFilteredTotal = 0;

    /* Total data set length */
    $sQueryTotalCount = "SELECT COUNT(".$sIndexColumn.") as \"totalRowsCount\" FROM ".$sTable;

    //Create Statments
    $statmntFinal = oci_parse($conn, $sQueryFinal);
    $statmntFinalCount = oci_parse($conn, $sQueryFinalCount);
    $statmntTotalCount = oci_parse($conn, $sQueryTotalCount);

    // //Bind variables.

    if ( isset( $_GET['iDisplayStart'] ))
    {
    $dsplyStart = $_GET['iDisplayStart'];
    }
    else{
    $dsplyStart = 0;
    }

    if ( isset( $_GET['iDisplayLength'] ) && $_GET['iDisplayLength'] != '-1' )
    {
    $dsplyRange = $_GET['iDisplayLength'];
    if ($dsplyRange > (2147483645 - intval($dsplyStart)))
    {
    $dsplyRange = 2147483645;
    }
    else
    {
    $dsplyRange = intval($dsplyStart) + intval($dsplyRange);
    }
    }
    else
    {
    $dsplyRange = 2147483645;
    }

    //Bind variables of number of rows to fetch.
    oci_bind_by_name($statmntFinal, ':iDisplayStart', $dsplyStart);
    oci_bind_by_name($statmntFinal, ':iDisplayEnd', $dsplyRange);

    //Bind all variables of general search
    for ( $i = 0 ; $i < $nWhereGenearalCount ; $i++ )
    {
    oci_bind_by_name($statmntFinal, ':whereParam'.$i , $sWhereGenearal);
    oci_bind_by_name($statmntFinalCount, ':whereParam'.$i , $sWhereGenearal);
    }

    //Bind all variables of specific search
    for ( $i = 0 ; $i < count($sWhereSpecificArray) ; $i++ )
    {
    oci_bind_by_name($statmntFinal, ':whereSpecificParam '.$i , $sWhereSpecificArray[$i]);
    oci_bind_by_name($statmntFinalCount, ':whereSpecificParam '.$i , $sWhereSpecificArray[$i]);
    }


    //Execute selects
    oci_execute($statmntTotalCount);
    $iTotal = 0;
    while ($row = oci_fetch_array($statmntTotalCount, OCI_ASSOC))
    {
    $iTotal = $row['totalRowsCount'];
    }
    oci_free_statement($statmntTotalCount);

    oci_execute($statmntFinalCount);
    $iFilteredTotal = 0;
    while ($row = oci_fetch_array($statmntFinalCount, OCI_ASSOC))
    {
    $iFilteredTotal = $row['totalRowsCount'];
    }
    oci_free_statement($statmntFinalCount);



    /*
    * Output
    */
    $output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
    );

    print $sQueryFinal." ";
    print $sQueryFinalCount." ";
    print $sQueryTotalCount." ";
    oci_execute($statmntFinal);


    while ( $aRow = oci_fetch_array($statmntFinal, OCI_ASSOC) )
    {
    $row = array();
    for ( $i=0 ; $i
    [/code]
  • ashishkulkarniashishkulkarni Posts: 9Questions: 0Answers: 0
    Guntur,

    Can you try out the following to make sure it is not an issue with your queries first:

    1. For each query, try and get the final SQL statement either using echo (preferred) or by manually evaluating the PHP expressions.
    2. Run these queries using SQL*Plus or Toad.
    3. If the queries run properly and you see output then look at the remaining PHP code.

    Cheers,

    Ashish.
This discussion has been closed.