php oracle OCIFetchInto while loop

[bobo]

hello

In a nut shell I need to know if there is a way to read the next value of the OCIFetchInto array before it is printed. If this is not clear enough read on.

I have an intranet page for a school board that fetches all the photocopiers in the schools and keeps a score of the copies they used so that its easy to keep track of the number of copies we need to be charged for.

It an oracle/php application and I have your usual OCIFetchInto listing all the info correctly.


select statement here.... ordered by school code
statement execution here...
while (OCIFetchInto($resultsA, &$resultsA_row, OCI_ASSOC+OCI_RETURN_NULLS)) {
$SCH = $resultsA_row['SCH'];
$Counter = $resultsA_row['COUNTER'];
$machine = $resultsA_row['COPIER'];
Echo a table here
}


The code above was abriviated because there really no use to post all of it because what I need to do is to read the next field that is coming up before the while loop prints it.

My real code prints out a table, something like this:
------------------------------------------------------
photocopier_name | School Code | counter
-------------------------------------------------------

XeroxA | SCH1 | 123
XeroxB | SCH1 | 123
XeroxC | SCH1 | 123
XeroxD | SCH2 | 456
XeroxE | SCH2 | 456
XeroxF | SCH2 | 456

so here there's 6 machines at 2 different schools

my problem is I want to get the total of the all the counters (3 copiers in this example per school) . so SCH1 = 369. I know how to do that but I want to insert a table row at the end of each school code with the total per school.
like this:

XeroxA | SCH1 | 123
XeroxB | SCH1 | 123
XeroxC | SCH1 | 123
--------------------------
369
-------------------------
XeroxD | SCH2 | 456
XeroxE | SCH2 | 456
XeroxF | SCH2 | 456
--------------------------
1368
--------------------------

and so on...(38 schools)

What I need here is a logic or maybe a function name if it exists because I have no clue how to say to the loop... "if your changing school code apply a new table row" Is there a way I can read the next value in the array and say if the next school code is same do nothing... if the next school code is different apply my total count function.

[sorry about the long post i just to be as clear as possible]

[PeejAvery]

On a side note...The function ocifetchinto is a deprecated function. Why don't you use the fetching functions instead?

Anyway, there is no way to get ahead of the database row pointer. You can write all the information into a temporary array, then read from that temporary array to output the table. But, I don't see how that is going to change much.

Why exactly do you need to read ahead?

[bobo]

yes I know about the old code... it an older application

re: "Why exactly do you need to read ahead?"

I mean pretty much what you think I mean, "get ahead of the database row pointer" and I say this because in my brain storming I pretty much came with the same approach you just suggested.... put everything in an secondary array. That will be my last resort. But I though I saw somewhere someone doing it using a pre-define php function , and I've been searching google on it to no avail. It's always at this time I go to forums.

Are sure theres no way to read ahead of the database pointer? Cause thats exacly what I mean.

[bobo]

oops mis-read your question...

your asking why I want to read ahead...

(my brain is mush right now I though you had ask What do I mean by reading ahead. )

anyways...
pretty much like my litttle table design shows... I want to interup the array to insert a table row whenever the school code changes.

[mmetzger]

Assuming the data is always in order (sorted per the SchoolID,) you wouldn't have to read ahead. If you're after the format you've printed, try this:


select statement here.... ordered by school code
statement execution here...

$total = 0;
$previousSCH = "";

while (OCIFetchInto($resultsA, &$resultsA_row, OCI_ASSOC+OCI_RETURN_NULLS))
{
$SCH = $resultsA_row['SCH'];
$Counter = $resultsA_row['COUNTER'];
$machine = $resultsA_row['COPIER'];
if ($previousSCH != $SCH)
{
// print total, then reset to 0;
$total = 0
$previousSCH = $SCH;
}
else
{
$total += $Counter;
}
Echo a table here
}


You may need to add an extra check in there for the first instance (ie, don't print a total row if it's the first time through the loop) but that should give you what you're after...

[PeejAvery]

Are sure theres no way to read ahead of the database pointer?
Yes, I am sure. The function of a pointer is to get a specific row. If you could get ahead of it, then there wouldn't be a pointer.

Like I said, you can use a temporary array and then read from that array. There isn't any pointer to a specific row in an array.

[bobo]

Assuming the data is always in order (sorted per the SchoolID,) you wouldn't have to read ahead. If you're after the format you've printed, try this:


select statement here.... ordered by school code
statement execution here...

$total = 0;
$previousSCH = "";

while (OCIFetchInto($resultsA, &$resultsA_row, OCI_ASSOC+OCI_RETURN_NULLS))
{
$SCH = $resultsA_row['SCH'];
$Counter = $resultsA_row['COUNTER'];
$machine = $resultsA_row['COPIER'];
if ($previousSCH != $SCH)
{
// print total, then reset to 0;
$total = 0
$previousSCH = $SCH;
}
else
{
$total += $Counter;
}
Echo a table here
}


You may need to add an extra check in there for the first instance (ie, don't print a total row if it's the first time through the loop) but that should give you what you're after...

I see, your inserting he previous school code in a variable then comparing it to the present one, this should work...

thanks guys :thumb: