Hello everyone!

I have a problem having loops in Php. The process is, it will take all the records in the database table through looping based on the date specified. For example the billing period is from February 01, 2007 to February 28, 2007. It will take all the records with in the transaction date. My problem is they require me to take the total time during the loop process in which i can take the total time only after the loop ends. What i did is i created the same loop then place it before the next same loop but it only performs the first loop and the next loop is not executed. My next solution is i place the first loop within the next loop but there is an error during the process. The error is:

Database error: Invalid SQL: SELECT attorney_level FROM attorney_level_range WHERE now() >= from_date AND now() <= to_date AND attorney_id=
MySQL Error: 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2)
Session halted.


Here is my code for my first option. It works without an error but the second loop is not executed. The other i option i made is i place the first loop in the second loop but there is an error. Pls help................... Thanks....

//test loop start-------------------------------------------------------------------------------------
while($next_record )
{
//-------------------------------
// Create field variables based on database fields
//-------------------------------
$fldattorney_id = $db->f("a_attorney_nick");
$fldtime = $db->f("s_time");
$next_record = $db->next_record();
//-------------------------------
// Replace Template fields with database values
//-------------------------------

$total_hours=$total_hours+$fldtime;
$atty_id = dlookup("attorney", "attorney_id", "attorney_nick='$fldattorney_id'");
$level = get_attylevel($atty_id,$flddate);

}
//test loop end-------------------------------------------------------------------------------------

echo "<br>First Loop total time: $total_hours";


while($next_record )
{

//-------------------------------
// Create field variables based on database fields
//-------------------------------
$fldattorney_id = $db->f("a_attorney_nick");
$fldclient_id = $db->f("s_client_id");
$flddate = $db->f("s_date");
$fldmatter_id = $db->f("s_matter_id");
$fldservice_description = $db->f("s_service_description");
$fldtime = $db->f("s_time");
$next_record = $db->next_record();
$curr_date = $flddate;
//test---------------------------------------------------------------------------------------------
echo "<br>rate: $atty_rate";
//test ends----------------------------------------------------------------------------------------
//test-------------------------------------------------------------------------------------------
echo "<br>date: $curr_date";
echo "<br>time: $fldtime";
//test end --------------------------------------------------------------------------------------

//-------------------------------
// service CustomShow Event begin
//-------------------------------
//-------------------------------
// Replace Template fields with database values
//-------------------------------

$tpl->set_var("item", tohtml($flditem));
$tpl->set_var("matter_id", tohtml($fldmatter_id));
$tpl->set_var("client_id", tohtml($fldclient_id));
$tpl->set_var("date", tohtml(date1($flddate)));
$tpl->set_var("service_description", tohtml($fldservice_description));
$tpl->set_var("attorney_id", tohtml(get_atty_fullname($fldattorney_id)));
$tpl->set_var("time", tohtml(number_format($fldtime,2)));

$total_hours=$total_hours+$fldtime;
$atty_id = dlookup("attorney", "attorney_id", "attorney_nick='$fldattorney_id'");
$level = get_attylevel($atty_id,$flddate);

$package = get_billingmode($fldmatter_id,$flddate);
$pa_package = get_pa_billingmode($fldmatter_id,$flddate);

if ($package=="moving monthly retainer") {
$retainer_amount = dlookup("billing","package_amount","master_id='$fldmatter_id' AND from_date<='$flddate' AND to_date>='$flddate'");
$amount = compute_retainer($fldmatter_id, $atty_id, $level, $flddate, $fldtime, $fldclient_id, $package, $pa_package, $fldper_appearance);
$atty_rate = compute_retainer($fldmatter_id, $atty_id, $level, $flddate, 1.0, $fldclient_id, $package, $pa_package, $fldper_appearance);
$trial_amount = $total + $amount;
if ($trial_amount > $retainer_amount) {
if ($exceeded==0) {
$exceeded=1;
$retainer_bal = round($retainer_amount - $total,2);
$retainer_rate = compute_retainer($fldmatter_id, $atty_id, $level, $flddate, 1.0, $fldclient_id, $package, $pa_package, $fldper_appearance);
$retainer_time = round($retainer_bal/$atty_rate,2);
$retainer_amount = round(compute_retainer($fldmatter_id, $atty_id, $level, $flddate, $retainer_time, $fldclient_id, $package, $pa_package, $fldper_appearance),2);
$regular_time = round($fldtime-$retainer_time,2);
$regular_amount = compute_regular($fldmatter_id, $atty_id, $level, $flddate, $regular_time, $fldclient_id, $package, $pa_package, $fldper_appearance);
$regular_rate = compute_regular($fldmatter_id, $atty_id, $level, $flddate, 1.0, $fldclient_id, $package, $pa_package, $fldper_appearance);

//$atty_rate = number_format($retainer_rate,2) . "<br>" . number_format($regular_rate,2);
$amount = round($retainer_amount+$regular_amount,2);
$tpl->set_var("time", number_format($retainer_time,2) . "<br>" . number_format($regular_time,2) . "<br>");
$tpl->set_var("rate", number_format($retainer_rate,2) . "<br>" . number_format($regular_rate,2) . "<br>");
}
else {
$amount = round(compute_regular($fldmatter_id, $atty_id, $level, $flddate, $fldtime, $fldclient_id, $package, $pa_package, $fldper_appearance),2);
$atty_rate = compute_regular($fldmatter_id, $atty_id, $level, $flddate, 1.0, $fldclient_id, $package, $pa_package, $fldper_appearance);
$tpl->set_var("rate", tohtml(number_format($atty_rate,2)));
}
}
else {
$tpl->set_var("rate", tohtml(number_format($atty_rate,2)));
}
}
else {
$amount = round(compute_regular($fldmatter_id, $atty_id, $level, $flddate, $fldtime, $fldclient_id, $package, $pa_package, $fldper_appearance),2);
$atty_rate = compute_regular($fldmatter_id, $atty_id, $level, $flddate, 1.0, $fldclient_id, $package, $pa_package, $fldper_appearance);
$tpl->set_var("rate", tohtml(number_format($atty_rate,2)));
}
$fldamount = $amount;
$total = round($total + $amount,2);
$total_amount = $total;

//check cap
if ($package=="one-time package" OR $package=="fixed monthly retainer") {
if ($package=="one-time package") $tpl->set_var("package_note", "One time package");
$total = dlookup("billing","package_amount","master_id='$master_id' AND to_days('" . get_session("BillDateFrom") . "') >= to_days(from_date) AND to_days('" . get_session("BillDateTo") . "') <= to_days(to_date)");
$total_amount = $total;
//test---------------------------------------------------------------------------------------------
echo "<br>amount = $total_amount";
//$fldtimetotal = $fldtimetotal + $fldtime;

$OTPlegalfees = number_format(($total_amount/$fldtime),2);
echo "<br>Legalfees = $OTPlegalfees";

$bsnum=get_param("bsnum");
echo "<br>Attorney ID: $atty_id";
$currency = get_curr(get_param("master_id"));
$tpl->set_var("currency", $currency);
//$atty_rate1 = number_format((($total_amount/$fldtime) * $fldtime),2);
//$legalFees = $atty_rate1 * $fldtime;
$indvat_amount = $OTPlegalfees * 0.12;

$result = mysql_query("insert into billingstatement(bs_number, date, transactionType, subject_id, matter_id, client_id, attorney_id, work_credit, rate, time, currency_nick, legalFees, fax, misc, misc_1, misc_2, misc_3, misc_4, photocopying, postage, printing, processing, telephone, transportation, vat, particulars) values('$bsnum', '$curr_date', 'LF', $id[1], $id[2], $id[0], $atty_id, 0.00, 0.00, $fldtime, '$currency', '$OTPlegalfees', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, '$indvat_amount', 'Legal Service Render for date $curr_date' )");
//test end-----------------------------------------------------------------------------------------
}
else {
$package_amount = dlookup("billing","package_amount","master_id='$master_id' AND to_days('" . get_session("BillDateFrom") . "') >= to_days(from_date) AND to_days('" . get_session("BillDateTo") . "') <= to_days(to_date)");
$cap_amount = dlookup("billing","cap_amount","master_id='$master_id' AND to_days('" . get_session("BillDateFrom") . "') >= to_days(from_date) AND to_days('" . get_session("BillDateTo") . "') <= to_days(to_date)");
if ($total<$package_amount) $total_amount = $package_amount;
if (dlookup("billing","cap","master_id='$master_id' AND to_days('" . get_session("BillDateFrom") . "') >= to_days(from_date) AND to_days('" . get_session("BillDateTo") . "') <= to_days(to_date)")){
$capped=1;
$total_raw = $total;
if ($total > $cap_amount) {
$total = $cap_amount;
$total_amount = $total;
}
}
else $capped=0;
}
$tpl->set_var("amount", tohtml(number_format($fldamount,2)));
$tpl->set_var("total", tohtml(number_format($total,2)));
$tpl->set_var("total_amount", tohtml(number_format($total_amount,2)));
$tpl->parse("DListservice", true);
//-------------------------------
// service CustomShow Event end
//-------------------------------

//-------------------------------
// Move to the next record
//-------------------------------
//test---------------------------------------------------------------------------------------------
$bsnum=get_param("bsnum");
echo "<br>Attorney ID: $atty_id";
$currency = get_curr(get_param("master_id"));
$tpl->set_var("currency", $currency);
$legalFees = $atty_rate * $fldtime;
$indvat_amount = $legalFees * 0.12;

$result = mysql_query("insert into billingstatement(bs_number, date, transactionType, subject_id, matter_id, client_id, attorney_id, work_credit, rate, time, currency_nick, legalFees, fax, misc, misc_1, misc_2, misc_3, misc_4, photocopying, postage, printing, processing, telephone, transportation, vat, particulars) values('$bsnum', '$curr_date', 'LF', $id[1], $id[2], $id[0], $atty_id, 0.00, $atty_rate, $fldtime, '$currency', '$legalFees', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, '$indvat_amount', 'Legal Service Render for date $curr_date' )");
//test end-----------------------------------------------------------------------------------------

}

try to execute the query string directly which you are using in mysql_query

Thanks for help.

I have solved the problem. What i did is to set the first loop as a function then i call it inside the 2nd loop i created. Thanks a lot. I very much appreciate your help.