[thelist] Query Quandry

[Jay Blanchard]

[snip]
I have discovered that for each son. there is one sot. BillDate and
BillingNumber are the only commonalities which can be related between
tables. Since there is one of each in each table there needs be no GROUP BY,
or SUM for that matter. Methinks I should go with a sub-query.
[/snip]

Well, since there is a one-to-one relationship between the tables and there
was no way (due to lack of foreign keys, but I cannot change the tables, and
due to MySQL 3.2x not having UNION or sub select functions) to query them
all in one batch. It did turn out that each record is entered in datetime
order in the table when the table is populated, so the earliest datetime for
son. matches up with the earliest dattime for sot. I had to perform two
queries, one for each table, and order by datetime entries. Then I could
write the info out in PHP by doing this;

<?
while($dbrowa = mysql_fetch_object($dbseta)){
	$dbrowb = mysql_fetch_object($dbsetb);

?>
	<td><?print("$dbrowa->BillDate")?></td>
	<td><?print("$dbrowa->BillingNumber")?></td>
	<td><?print("$dbrowa->SOCompletionDate")?></td>
	<td><?print("$dbrowa->SONumber")?></td>
	<td><?print("$dbrowa->PONumber")?></td>
	<td><?(convertCR($dbrowb->USONetChange))?></td>
	<td><?(convertCR($dbrowb->USOFractionalAmount))?></td>
	<td><?(convertCR($dbrowb->USOOneTimeAmount))?></td>
	<td><?(convertCR($dbrowb->USOBilledAmount))?></td>

<?}?>

(The convertCR function converts negative numbers to credits)

That solved the problem since the last four items (prefixed by 'USO') did
not need to be summed. Had that been the case I would have still been in a
quandry.

Thanks!

Jay

<tip type="PHP" author="Jay Blanchard>
Do you want to alternate row colors in tables? Here is one quick method;

$rowcount = 0;
while(whatever){
$rowcount ++;
if ($rowcount == 1){
	print("<tr bgcolor=COLOR");
	}
	elseif ($rowcount <> 1) {
		print("<tr>");
		$rowcount = 0;
	}

	PRINT ROW
</tr>
<?}?>

Initially $rowcount is set to 0, then it is incremented to 1, if that is
true the row is printed with color. On the next round the $rowcount is
incremented to 2, which is not equal to 1, so the row is printed without
color (or with a different color) and then $rowcount is set to 0 again,
which is incremented to 1 on the next go, and so on and so forth....
</tip>