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
ckdoublenecks
Forum Newbie
Posts: 21 Joined: Mon Jan 24, 2011 6:47 pm
Post
by ckdoublenecks » Wed Oct 05, 2011 9:27 pm
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();
?>
twinedev
Forum Regular
Posts: 984 Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio
Post
by twinedev » Wed Oct 05, 2011 9:58 pm
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
Post
by ckdoublenecks » Wed Oct 05, 2011 10:07 pm
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.
twinedev
Forum Regular
Posts: 984 Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio
Post
by twinedev » Wed Oct 05, 2011 10:17 pm
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
Post
by ckdoublenecks » Wed Oct 05, 2011 10:41 pm
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();
?>
twinedev
Forum Regular
Posts: 984 Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio
Post
by twinedev » Thu Oct 06, 2011 12:08 am
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.