need to match two databases

[ckdoublenecks]

I'm trying to create a printout using data from two database tables( saledata & postable)(when itemno matches) and update one field(onhand) in one of the databases(postable).  I'm sure there's a simple way to do this but I haven't determined how, yet?

below is my current effort:

<?php
include("getinvno.php");
$rate = $row['rate'];
   
mysql_connect("localhost", "root", ""); 
mysql_select_db(posdb) or die("Unable to select database"); 
$query = "SELECT * FROM saledata "; 
$result=mysql_query($query);
$num=mysql_numrows($result);
while($row = mysql_fetch_array($result))
   {
$itemno = $row['itemno'];   
$sellqty = $row['sellqty'];

// mysql_connect("localhost", "root", ""); 
// mysql_select_db(posdb) or die("Unable to select database"); 
$query = "SELECT * FROM postable WHERE itemno='$itemno'";
$result=mysql_query($query);
$num=mysql_numrows($result);
echo "customer# ".$data['invno'];
echo ".. </b><br />"; 
echo "tax rate ".$data['rate'];
echo ".. </b><br />"; 
echo "<table cellspacing=1 cellpadding=1 border=0>
<tr>
<th>Sold</th>
<th>item#</th>
<th>item</th>
<th>Price</th>
<th>Due</th>
</tr>";
while($row = mysql_fetch_array($result))
{
$tax = $sellprice * $rate; 
$taxtotal = $taxtotal + $tax; 
$amtdue = $sellprice * $sellqty;     
$amtdue = $amtdue + $tax;
$total = $total + $amtdue; 
$onhand - $onhand - $sellqty;
echo "<tr>";
echo "<td>" . $row['sellqty'] . "</td>";
echo "<td>" . $row['itemno'] . "</td>";
echo "<td>" . $row['itemdescr'] . "</td>";
echo "<td align=right>" . $row['sellprice'] . "</td>";
echo "<td align=right>" . $row['amtdue'] . "</td>";
}
echo "<tr>";
echo "<td>Tax</td>";
echo "<td></td>";
echo "<td></td>";
echo "<td></td>";
echo "<td align=right>" . sprintf("%.2f",$taxtotal) .
"</td>";
echo "<tr>";
echo "<td>Total</td>";
echo "<td></td>";
echo "<td></td>";
echo "<td></td>";
echo "<td align=right>" . sprintf("%.2f",$total) .
"</td>";
echo "</tr>";
echo "</table>";

$sql = "UPDATE postable SET
onhand = '" . mysql_real_escape_string($onhand) . "'
WHERE itemno='".$_POST["itemno"]."'";
mysql_query($sql) or die("Update query failed.");
}

?> 

[teynon]

Use MySQL's INNER JOIN.

 

SELECT * FROM saledata AS t1 INNER JOIN postable AS t2 WHERE t1.itemno = t2.itemno AND t2.itemno = '{$itemno}'

 

http://dev.mysql.com/doc/refman/5.0/en/join.html

[ckdoublenecks]

I used the code as you suggested but now I get no values from either table. Below is my code:

<?php
include("getinvno.php");
$rate = $row['rate'];
mysql_connect("localhost", "root", ""); 
mysql_select_db(posdb) or die("Unable to select database"); 
$query = "SELECT * FROM saledata AS t1 INNER JOIN postable AS t2 WHERE t1.itemno = t2.itemno
AND t2.itemno = '{$itemno}'"; 
$result=mysql_query($query);
$num=mysql_numrows($result);
echo "<center><font size=+2><b> Kirk Pawn, Gun & Archery</font><br />";
echo "9921 Old Bearskin Rd<br />";
echo "Hunters Den, Alabama  32145<br /></center>";
echo "customer# ".$data['invno'];
echo ".. </b><br />"; 
echo "tax rate ".$data['rate'];
echo ".. </b><br />";    
echo "<table cellspacing=1 cellpadding=1 border=0>
<tr>
<th>Qty</th>
<th></th>
<th></th>
<th>Unit</th>
<th>Amt</th>
<tr>
<th>Sold</th>
<th>item#</th>
<th>item</th>
<th>Price</th>
<th>Due</th>
<tr>
<TH colspan=5>---------------------------------------------------------------------------------------------</TH>
</tr>";
while($row = mysql_fetch_array($result))
{
$tax = $sellprice * $rate; 
$taxtotal = $taxtotal + $tax; 
$amtdue = $sellprice * $sellqty;     
$amtdue = $amtdue + $tax;
$total = $total + $amtdue; 
$onhand - $onhand - $sellqty;
echo "<tr>";
echo "<td>" . $row['sellqty'] . "</td>";
echo "<td>" . $row['itemno'] . "</td>";
echo "<td>" . $row['itemdescr'] . "</td>";
echo "<td align=right>" . $row['sellprice'] . "</td>";
echo "<td align=right>" . $row['amtdue'] . "</td>";
}
echo "<tr>";
echo "<TH 
colspan=5>========================================================</TH>";
echo "<tr>";
echo "<td>Tax</td>";
echo "<td></td>";
echo "<td></td>";
echo "<td></td>";
echo "<td align=right>" . sprintf("%.2f",$taxtotal) .
"</td>";
echo "<tr>";
echo "<TH 
colspan=5>========================================================</TH>";
echo "<tr>";
echo "<td>Total</td>";
echo "<td></td>";
echo "<td></td>";
echo "<td></td>";
echo "<td align=right>" . sprintf("%.2f",$total) .
"</td>";
echo "</tr>";
echo "</table>";
$sql = "UPDATE postable SET
onhand = '" . mysql_real_escape_string($onhand) . "'
WHERE itemno='".$_POST["itemno"]."'";
mysql_query($sql) or die("Update query failed.");
echo "<b>We thank you for your business</b><br />";
?> 

[ckdoublenecks]

this may be a problem?

below is the code for "getinvno.php - the code is used in other programs but I added the rate to it . I use the code

echo "tax rate: $rate</b><br />"; 

to display the #

 

<?php 
mysql_connect('localhost','root',""); 
mysql_select_db('numbersdb') or die( "Unable to select database"); 
if(!empty($_POST["submit"])) 
{ 
$invno = $_POST['invno']; 
[b]$rate = $_POST['rate'];[/b] 
} 
mysql_query("UPDATE numbdata SET invno=invno+1"); 
$result=mysql_query("select invno, rate from numbdata") or die ("Error - could not retrieve invoice# from database"); 
$data=mysql_fetch_assoc($result); 
mysql_close(); 
?> 

[jtm62]

I used the code as you suggested but now I get no values from either table. Below is my code:

<?php
$query = "SELECT * FROM saledata AS t1 INNER JOIN postable AS t2 WHERE t1.itemno = t2.itemno
AND t2.itemno = '{$itemno}'"; 

 

The correct syntax for SQL join statements is:

 

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

 

This may not solve all your problems... but it should start to return some results.

[jcbones]

Teynon's query should work, BUT you need to specify from which table you want * from.

 

$query = "SELECT t1.itemno, t1.sellqty, t2.* FROM saledata AS t1 INNER JOIN postable AS t2 WHERE t1.itemno = t2.itemno
AND t2.itemno = '{$itemno}'";

 

You may get an ambiguous statement back from mysql.  If that happens, just alias the return column names.