Page 1 of 1

need coding help

Posted: Wed Oct 05, 2011 9:27 pm
by ckdoublenecks
I use 2 databases,(numbersdb) has one record with the field "balance". it is read once to retrieve the balance value for storage. This database is updated at the end.

the other database (ckdb) has the several records for the report.

All works except that my code does not get the balance from the nunbersdb. I could really use some adice.

Code: Select all

<?php

// **this database has one record with a field named "balance" which
// will, at the end, be updated
mysql_connect("localhost","root",""); 
mysql_select_db('numbersdb') or die( "Unable to select database");
// query ="USE db1; Select * from table.....  
$query=" SELECT balance FROM numbdata where id='id'"; 
$result=mysql_query($query); 
$num=mysql_num_rows($result);
// echo " balance is $num <br />";

// **this database has the bank records** 
mysql_select_db('ckdb') or die( "Unable to select database");
// query ="USE db2; Select * from table..... 
$query=" SELECT * FROM ckcust ORDER BY dateord ASC"; 
$result=mysql_query($query); 
$num=mysql_numrows($result); 
error_reporting(0);

echo date("m/d/Y") . "<br />"; 
echo "<table cellspacing=1 cellpadding=0 border=0> 
<tr> 
<th>Inv#</th> 
<th>description</th> 
<th>date</th> 
<th align=right>charges</th> 
<th align=right>paid</th> 
<th align=right>owed</th> 
<th align=right>balance</th> 
<tr> 
<td></td> 
<td></td> 
<td></td> 
<td></td> 
<td></td> 
<td></td> 
<td align=right>" . sprintf("%.2f",$balance) . 
"</td> 
<tr> 
<TH colspan=9>=======================================================================</TH> 
</tr>"; 
while($row = mysql_fetch_array($result)) 
{ 

// * calcs******************************** 
$owed = $row['charges'] - $row['paidamt'];
$nubalance = $row['balance'] - $row['paidamt'];
// $nubalance = $nubalance - $row['paidamt']; 
$totpaid = $totpaid + $row['paidamt']; 
$totcharges = $totcharges + $row['charges']; 
$totowed = $totowed + $owed; 
// * end calcs***************************** 

echo "<tr>"; 
echo "<td>" . $row['invnum'] . "</td>"; 
echo "<td>" . $row['bname'] . "</td>"; 
echo "<td>" . $row['dateord'] . "</td>"; 
echo "<td align=right>" . $row['charges'] . "</td>"; 
echo "<td align=right>" . $row['paidamt'] . "</td>"; 
echo "<td align=right>" . sprintf("%.2f",$owed) . 
"</td>"; 
echo "<td align=right>" . sprintf("%.2f",$nubalance) . 
"</td>"; 
} 
echo "<tr>"; 
echo "<th colspan=9>=======================================================================</TH>"; 
echo "<tr>"; 
echo "<td>Gtotals</td>"; 
echo "<td></td>"; 
echo "<td></td>"; 
echo "<td align=right>" . sprintf("%.2f",$totcharges) . 
"</td>"; 
echo "<td align=right>" . sprintf("%.2f",$totpaid) . 
"</td>"; 
echo "<td align=right>" . sprintf("%.2f",$totowed) . 
"</td>"; 
echo "</tr>"; 
echo "</table>";

mysql_query("UPDATE numbdata SET balance='$nubalance'");
echo "New balance of $ ".$_POST["nubalance"]." has been updated";  
mysql_close(); 
?>

Re: need coding help

Posted: Wed Oct 05, 2011 9:58 pm
by twinedev
Do you really mean to get just records where the field id is set to the string 'id' as that is what the following it looking for.

Code: Select all

$query=" SELECT balance FROM numbdata where id='id'"; 
Also at the end of the script, you have it updating EVERY record with this line:

Code: Select all

mysql_query("UPDATE numbdata SET balance='$nubalance'");
Since you are using a where condition in the select, it seems you would also be wanting to do one here as well.

-Greg

Re: need coding help

Posted: Wed Oct 05, 2011 10:07 pm
by ckdoublenecks
yes greg, there is only record in the "numbersdb" database table. I want to get the balance field from that record then build a report from the 2nd database , including recalculating the balance until the last record in the 2nd database is processed; at which replace the balance field with the new value.

Re: need coding help

Posted: Wed Oct 05, 2011 10:17 pm
by twinedev
You also never use the $result from the first query before you reassign it to the second query:

Code: Select all

$query=" SELECT balance FROM numbdata where id='id'";
$result=mysql_query($query);
$num=mysql_num_rows($result);
// echo " balance is $num <br />";

// **this database has the bank records**
mysql_select_db('ckdb') or die( "Unable to select database");
// query ="USE db2; Select * from table.....
$query=" SELECT * FROM ckcust ORDER BY dateord ASC";
$result=mysql_query($query);
$num=mysql_numrows($result);
error_reporting(0);

Re: need coding help

Posted: Wed Oct 05, 2011 10:41 pm
by ckdoublenecks
shouldn't this work:?

Code: Select all

 <?php

// **this database has one record with a field named "balance" which
// will, at the end, be updated

mysql_connect("localhost","root","");
mysql_select_db('numbersdb') or die( "Unable to select database");
if(!empty($_POST["submit"]))
{
 $balance = $_POST['balance'];
 $query="SELECT * FROM ckcust Where balance='$balance'"; 
 $result=mysql_query($query); 
$num=mysql_num_rows($result);
// echo " balance is $num <br />";

// **this database has the bank records**
mysql_select_db('ckdb') or die( "Unable to select database");
Select * FROM ckcust ORDER BY dateord ASC"; 
$result=mysql_query($query); 
$num=mysql_numrows($result); 
error_reporting(0);

echo date("m/d/Y") . "<br />"; 
echo "<table cellspacing=1 cellpadding=0 border=0> 
<tr> 
<th>Inv#</th> 
<th>description</th> 
<th>date</th> 
<th align=right>charges</th> 
<th align=right>paid</th> 
<th align=right>owed</th> 
<th align=right>balance</th> 
<tr> 
<td></td> 
<td></td> 
<td></td> 
<td></td> 
<td></td> 
<td></td> 
<td align=right>" . sprintf("%.2f",$balance) . 
"</td> 
<tr> 
<TH colspan=9>=======================================================================</TH> 
</tr>"; 
while($row = mysql_fetch_array($result)) 
{ 

// * calcs******************************** 
$owed = $row['charges'] - $row['paidamt'];
$nubalance = $row['balance'] - $row['paidamt'];
// $nubalance = $nubalance - $row['paidamt']; 
$totpaid = $totpaid + $row['paidamt']; 
$totcharges = $totcharges + $row['charges']; 
$totowed = $totowed + $owed; 
// * end calcs***************************** 

echo "<tr>"; 
echo "<td>" . $row['invnum'] . "</td>"; 
echo "<td>" . $row['bname'] . "</td>"; 
echo "<td>" . $row['dateord'] . "</td>"; 
echo "<td align=right>" . $row['charges'] . "</td>"; 
echo "<td align=right>" . $row['paidamt'] . "</td>"; 
echo "<td align=right>" . sprintf("%.2f",$owed) . 
"</td>"; 
echo "<td align=right>" . sprintf("%.2f",$nubalance) . 
"</td>"; 
} 
echo "<tr>"; 
echo "<th colspan=9>=======================================================================</TH>"; 
echo "<tr>"; 
echo "<td>Gtotals</td>"; 
echo "<td></td>"; 
echo "<td></td>"; 
echo "<td align=right>" . sprintf("%.2f",$totcharges) . 
"</td>"; 
echo "<td align=right>" . sprintf("%.2f",$totpaid) . 
"</td>"; 
echo "<td align=right>" . sprintf("%.2f",$totowed) . 
"</td>"; 
echo "</tr>"; 
echo "</table>";

mysql_query("UPDATE numbdata SET balance='$nubalance'");
echo "New balance of $ ".$_POST["nubalance"]." has been updated";  
mysql_close(); 
?>

Re: need coding help

Posted: Thu Oct 06, 2011 12:08 am
by twinedev
As mentioned, you never retrieve data from the first query, you merely assign the number of returned rows to $num but just like $result, you reassign the next query to them before using the values.