Page 1 of 1

OCIResult as Hyperlink to 2nd Report

Posted: Mon Jan 31, 2005 3:32 pm
by SCNetworks
Hello All,

Need some help. Am both not a programmer nor PHP wiz, but did set up some pages almost 2 years ago using Oracle, PHP4 and IIS.

Have a new boss and he would like to see some "drill down" pages instead of the many different reports I've done one off over time. Here's and example of what I mean:

Background: We're a telecom company who does wireline operations, our physical plant network is kept in an Oracle database. Each "wire center" has it's own schema - and every schema is identical - so unions are the order of the day - and there's a lot of them.

They now want totals of certain pieces of equiptment which is easy to do, but from the results page displayed - they want hyperlinks to more detailed reports I already have in another section. Am not sure how to
both pass a variable and display a hyperlink within the page.

Below is the first report which loops through each WireCenter and gives the total of what's queried, let's say "Multiplexers":

Code: Select all

<?php 
$db="blahblah"
$connection = OCILogon("1","2",$3);

$stmt = OCIParse($connection,

"select 'AHVLAL' LOCATION, $query TOTAL from ahvlal$table union 
select 'CMPHAL' LOCATION, $query TOTAL from cmphal$table union
select 'ECLCAL' LOCATION, $query TOTAL from eclcal$table");

OCIExecute($stmt);

echo( date("l, F dS Y.") );

// Start of table and column headings (ID and Name)

print "<br><br>\n";

if ($table == ".pole where rb_state <3 and frc=3")
echo ("<b>Total of Multiplexers</b><br><br>");

print "<TABLE WIDTH="50%" CELLSPACING="0" CELLPADDING="1" BORDER="1">\n";

print " <TR><TH BGCOLOR="#BBBBBB" WIDTH="50%">Company 68</TH><TH BGCOLOR="#BBBBBB" WIDTH="50%">Total</TH></TR>\n"; 

// Loop through results 

while(OCIFetch($stmt))

&#123;

print " <TD>" . OCIResult($stmt, "LOCATION") . "</TD>\n";

$amount = OCIResult($stmt, "TOTAL");
$number1 = number_format($amount);

print " <TD align="right">" . $number1 . "</TD>\n";

$tot_amount += $amount;
$number2 = number_format($tot_amount);

print " </TR>\n";

&#125; 

print "</TABLE>\n"; 

print "<br>\n";

echo "<b>Total = $number2</b>";

OCIFreeStatement($stmt); 

OCILogoff($connection); 

?>
So, this lists 2 columns - one wit the name of the WireCenter (actually the schema name) and the total of Multiplexers within each, formatted nicely on a web page.

They want the total number to actually be a hyperlink to the below report which gives much more detailed info on each Multiplexer:

Code: Select all

$connection = OCILogon("1","2",$3);

$stmt = OCIParse($connection, "select '$wc' LOCATION, ipid, lead_number, struct_number, type, area_no, clli, latitude, longitude, hs_type, feed_type, dsl_capable from $wc.multiplexer where item = 14 and rb_state <> 6 order by $srt");
			       
OCIExecute($stmt); 

echo( date("l, F dS Y.") ); 

// Start of table and column headings (ID and Name)

print "<br><br>\n";

print "<TABLE WIDTH="90%" CELLSPACING="0" CELLPADDING="1" BORDER="1">\n";

print " <TR><TH BGCOLOR="#BBBBBB">LOCATION</TH><TH BGCOLOR="#BBBBBB">IPID</TH><TH BGCOLOR="#BBBBBB">LEAD</TH><TH BGCOLOR="#BBBBBB">STRUCT</TH><TH BGCOLOR="#BBBBBB">TYPE</TH><TH BGCOLOR="#BBBBBB">CSA</TH><TH BGCOLOR="#BBBBBB">CILI</TH><TH BGCOLOR="#BBBBBB">LAT</TH><TH BGCOLOR="#BBBBBB">LONG</TH><TH BGCOLOR="#BBBBBB">MOUNT</TH><TH BGCOLOR="#BBBBBB">FEED</TH>
<TH BGCOLOR="#BBBBBB">DSL</TH></TR>\n"; 

// Loop through results 

while(OCIFetch($stmt)) 

&#123;

print " <TD align="center">" . OCIResult($stmt, "LOCATION") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "IPID") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "LEAD_NUMBER") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "STRUCT_NUMBER") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "TYPE") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "AREA_NO") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "CLLI") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "LATITUDE") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "LONGITUDE") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "HS_TYPE") . "</TD>\n";

print " <TD align="center">" . OCIResult($stmt, "FEED_TYPE") . "</TD>\n";
 
print " <TD align="center">" . OCIResult($stmt, "DSL_CAPABLE") . "</TD>\n";

print " </TR>\n";

&#125; 

print "</TABLE>\n"; 

OCIFreeStatement($stmt);

OCILogoff($connection); 

?>
I guess the variable that needs to be passed somehow is $wc which stands for wirecenter, which is also the schema name - so it all matches up. This is currently selected through an HTML form and passed to this report.

I hope this makes sense to you programmers out there - have spent two days looking around on how to do and am at the point where I need some advice. Any and all suggestions and help is appreciated ...

Steve.




feyd | please some a form of formatting the code posted