[SOLVED] php only inserting one record from mysql

[tomdchi]

the script below needs to find all invoices in unpaid status and insert the info into another table.  It will only work for the first record it finds.  Can someone help me on this?

 

<?php
include ("../dbconnect.php");

$query = "SELECT inv.userid,
			 inv.total,
			 inv.id,
			 inv.status,
			 c.firstname,
			 c.lastname,
			 c.companyname 
			 FROM tblinvoices inv 
			 INNER JOIN tblclients c 
			 ON c.id=inv.userid 
			 AND inv.status='Unpaid'";

$result = mysql_query($query);

if (mysql_num_rows($result) == 0)
break;
  
while ($data = mysql_fetch_array($result)) {
$name = $data["firstname"] . " " . $data["lastname"];
$total = $data["total"];
$invoiceid = $data["id"];
$userid = $data["userid"];
$status = $data["status"];
$companyname = $data["companyname"];
}
$query = "SELECT fieldid,value from tblcustomfieldsvalues where relid='$userid' and fieldid in(3,4)";
$result = mysql_query($query);
while ($data = mysql_fetch_array($result)) $rtemp[$data['fieldid']] = $data['value'];{
$recbankacctnumber = $data['recbankacctnumber'] = $rtemp[3];
$recbankroutingnumber = $data['recbankroutingnumber'] = $rtemp[4];


$sql = "INSERT INTO tblclientpmt(transid,userid,name,routing,checking,";
$sql .= " amount,invoiceid,processed,date,companyname) ";
$sql .= "VALUES('','$userid','$name','$recbankroutingnumber','$recbankacctnumber',";
$sql .= " '$total','$invoiceid',1,now(),'$companyname')";

if (!mysql_query($sql))
	echo mysql_error();

}
?>

[akitchin]

you are re-using your variables for queries, results and data.  this means that on the second iteration of your "outside" while() loop, it will find no new records as the resultset has been replaced with your "inside" query's resource.

[tomdchi]

Thank you so much.  That did it!