need coding help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ckdoublenecks
Forum Newbie
Posts: 21
Joined: Mon Jan 24, 2011 6:47 pm

need coding help

Post 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(); 
?>
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: need coding help

Post 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
ckdoublenecks
Forum Newbie
Posts: 21
Joined: Mon Jan 24, 2011 6:47 pm

Re: need coding help

Post 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.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: need coding help

Post 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);
ckdoublenecks
Forum Newbie
Posts: 21
Joined: Mon Jan 24, 2011 6:47 pm

Re: need coding help

Post 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(); 
?>
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: need coding help

Post 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.
Post Reply