I have a table that I am pulling data out of. I want to know when the value for Supervisors.SupNum changes. Then, if the value does not change though each iteration, 'do this'. If it does change, do the other. How would I go about making this routine happen?
How to tell if a cell value changes?
If you mean you want to know, while retrieving successive rows from a result set, when the current row has a different value for a field from the corresponding value in the previous row: compare the current value with the previous value.
I could be totally off base here, but I'm imagining a situation where you would like to retrieve data from a table and know which values have changed. The big question that comes to mind is "changed since when ?" The second thing that comes to mind is that in order to know that something has changed, you would either need to a) store all the old, unchanged data or b) enter some extra information in each table record whenever that record gets changed.
If you use approach a, your script could loop through the new data and compare each record to the old data which you have stored somewhere.
If you use approach b, your script could loop through the new data and know which ones had changed if you had something like a Supervisors.LastModified field that you could check. If LastModified was in the last few minutes -- or since the last time you looked -- then you might be able to respond differently.
I just can't get my brain wrapped around what I'm trying to accomplish. Actually, I'm trying to teach a computer to be clairvoyant. Let me draw you a picture of the output I want and see if y'all can help:
Get variables from database
While (variables)
{
System 7, TX -- Supervisor 1
-- Tech 1 Field A
-- Tech 4 Field A
-- Tech 7 Field A
System 7, TX -- Supervisor 2
-- Tech 22 Field A
System 7, TX -- Supervisor 3
-- Tech 12 Field A
-- Tech 14 Field A
[COLOR="#FF0000"]Sum of Field A for System 7[/COLOR] <--- How do I know when to do this??
System 12, OK -- Supervisor 4
-- Tech 32 Field A
-- Tech 17 Field A
[COLOR="#FF0000"]Sum of Field A for System 12[/COLOR]
System 9, TX
}
I want a subtotal of field A for each system and a grand total at the end. How do I tell this 2$#!agg computer that I want to insert a calculation between two systems? I can't find an if statement that will work
<--- How do I know when to do this??
Immediately upon reading the following line.
Weedpacket;11026759 wrote:Immediately upon reading the following line.
That's what I thought, too. But it no workee. Here's my code:
$Quest = "SELECT Techs.FirstName AS TechFirst,
Techs.LastName AS TechLast,
Techs.TechNum,
Supervisors.FirstName AS SupFirst,
Supervisors.LastName AS SupLast,
Supervisors.SupNum,
Systems.LongSystem,
Systems.SystemNum,
InTimeAppts.WorkDate,
InTimeAppts.NumAppts,
InTimeAppts.InTime,
InTimeAppts.Late,
InTimeAppts.Early,
InTimeAppts.NotStarted,
InTimeAppts.Cancelled
FROM Techs, Systems, Supervisors, InTimeAppts
WHERE Supervisors.SupNum = Techs.Supervisor AND Systems.SystemNum = Techs.SystemNum AND InTimeAppts.TechNum = Techs.TechNum AND InTimeAppts.WorkDate = '$WorkDate'
ORDER BY Systems.LongSystem ASC, Supervisors.SupNum ASC, Techs.TechNum ASC";
$stmt = $mysqli->prepare($Quest) or die('what is the matter here?');
$stmt->execute();
$SupResult = $stmt->get_result();
mysql_data_seek($SupResult, 1);
$row = $result->fetch_row();
while ($row = $SupResult->fetch_assoc())
{
$SupNum = $row["SupNum"];
$SupFirst = $row["SupFirst"];
$SupLast = $row["SupLast"];
$SystemNum = $row["SystemNum"];
$LongSystem = $row["LongSystem"];
$TechNum = $row["TechNum"];
$TechFirst = $row["TechFirst"];
$TechLast = $row["TechLast"];
$NumAppts = $row["NumAppts"];
$InTime = $row["InTime"];
$Late = $row["Late"];
$Early = $row["Early"];
$NotStarted = $row["NotStarted"];
$Cancelled = $row["Cancelled"];
((more code))
if ($PreviousSystemNum != $SystemNum) <---
{
$InTimePer = ceil($InTimeSub / $NumApptsSub * 1000) / 10;
$LatePer = ceil($LateSub / $NumApptsSub * 1000) / 10;
$EarlyPer = ceil($EarlySub / $NumApptsSub * 1000) / 10;
$NotStartedPer = ceil($NotStartedSub / $NumApptsSub * 1000) / 10;
$CancelledPer = ceil($CancelledSub / $NumApptsSub * 1000) / 10;
$NumApptsGrand = $NumApptsGrand + $NumApptsSub;
$InTimeGrand = $InTimeGrand + $InTimeSub;
$LateGrand = $LateGrand + $LateSub;
$EarlyGrand = $EarlyGrand + $EarlySub;
$NotStartedGrand = $NotStartedGrand + $NotStartedSub;
$CancelledGrand = $CancelledGrand + $CancelledSub;
echo $InTimeGrand . ' 11 ' . $LateGrand . ' 22 ' . $EarlyGrand . ' 33 ' . $NotStartedGrand . ' 44 ' . $CancelledGrand . '<br>';
$OutString[0] = 'Totals:';
$OutString[1] = '';
$OutString[2] = '';
$OutString[3] = '';
$OutString[4] =$NumApptsSub;
$OutString[5] =$InTimePer;
$OutString[6] =$LatePer;
$OutString[7] =$EarlyPer;
$OutString[8] =$NotStartedPer;
$OutString[9] =$CancelledPer;
fputcsv($fh, $OutString);
//even more code.
} // closes the 'if' statement
$PreviousSystemNum = $SystemNum
} // loops the 'while' statement
I can't get the proper words to describe what's happening, so let me draw you a picture of what's happening:
Get variables from database
While (variables)
{
System 7, TX -- Supervisor 1
-- Tech 1 Field A
-- Tech 4 Field A
-- Tech 7 Field A
System 7, TX -- Supervisor 2
-- Tech 22 Field A
System 7, TX -- Supervisor 3
-- Tech 12 Field A
-- Tech 14 Field A
System 12, OK -- Supervisor 4
-- Tech 32 Field A
[COLOR="#FF0000"]Sum of Field A for System 7 & first tech after SystemNum changes.[/COLOR]
-- Tech 17 Field A
System 9, TX -- Supervisor 5
-- Tech 32 Field A
[COLOR="#FF0000"]Sum of Field A for Last row of System7, and the first row of System 9 after SystemNum changes. [/COLOR]
-- Tech 24 Field A
}I found this in the manual:
$Quest = large query here
$stmt = $mysqli->prepare($Quest) or die('hey you dope, what is the matter here?');
$stmt->execute();
$SupResult = $stmt->get_result();
while ($row = $SupResult->fetch_assoc())
{
mysql_data_seek($SupResult, 1); // <--- Line 186
$row = $SupResult->fetch_assoc();
$NextSystem = $row[0];
:: more code here::;
}
When I run the 'mysql_data_seek' statement, PHP returns this error:
Warning: mysql_data_seek() expects parameter 1 to be resource, object given in /Library/WebServer/Documents/DispatchReports/InTime.php on line 186I am baffled. Doesn't look like a flock of ducks is coming my way. Great! There's only so many ways to cook a duck.
change this:
mysql_data_seek($SupResult, 1); // <--- Line 186
$row = $SupResult->fetch_assoc();
$NextSystem = $row[0]; to:
mysql_data_seek($SupResult, $x); // <--- Line 186
$row = $SupResult->fetch_assoc();
$NextSystem = $row[0];
$x++that doesn't work either
mysql != mysqli that's your problem for the data_seek.
You need to check if you need to sum things before outputting, that way when num changes, you output the sum, reset it, and then start summing again and output the row that changed.
This banished the error:
$i++;
$SupResult->data_seek($i);
$row = $SupResult->fetch_row();
$NextSystemNum = $row[7];But this doesn't help either. I need to know two rows at the same time--the current row and the next row. If the two cells have different values, I need to insert the subtotal between the two. Like This:
SystemNum NextSystemNum
6 6
6 6
6 7 <------ insert subtotal between these two rows
7 8 <------ same here
This will give the two values:
$i++;
$SupResult->data_seek($i);
$row = $SupResult->fetch_row();
$SystemNum = $row[7];
$SupResult->data_seek($i+1);
$row = $SupResult->fetch_row();
$NextSystemNum = $row[7];
// more code here
if ($SystemNum != $NextSystemNum)
{
subtotal here
}
But .... this dadbern thing won't insert the subtotals between the two rows. It insists on totaling after the change instead of in between the two.
I'm still flummoxed.
$subtot = 0;
$previd = 0;
while($row = $result->fetch_assoc() )
{
if( $previd == $row['id'] ) {
$subtot += $row['number'];
} else {
if( $previd != 0 ) {
echo "subtotal is $subtot";
}
$previd = $row['id'];
$subtot = $row['number'];
}
// continue outputting current row as normal
}
Sample code, should help you get the total where you want. The code you showed seems incomplete but hopefully this sample shows you the general logic to use.
5 days later
What is the most optimized way to retrieve these values?
I don't understand that what is exactly your issue. Can you please share your table structure ?
I don't know if this is optimal, but it works:
$count = $i+1;
if ($count == $RowCount) $count = $i;
echo $count . ' x ' . $i . ' rc ' . $RowCount . '<br>';
$SupResult->data_seek($i);
$row = $SupResult->fetch_assoc();
$SystemNum = $row['SystemNum'];
$SupNum = $row['SupNum'];
$TechNum = $row['TechNum'];
$SupResult->data_seek($count);
$row = $SupResult->fetch_assoc();
$NextSystemNum = $row['SystemNum'];
$NextSupNum = $row['SupNum'];
$NextTechNum = $row['TechNum'];