PHP Oracle Join

flash

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

ashishkulkarni

I would guess that you just need to try it Guntur. Should be straightforward.

flash

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]

ashishkulkarni

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.