How would you make this query work?

[tomdchi]

The code below is for a phone payment system that works fine but I want to add to it.  In the second query below it looks into tblinvoices to find a unpaid invoice.  Somtimes I may have a customer pay before a invoice is generated so that would cause a problem.  How would I change this so that if no unpaid invoice is found that $amount is inserted into tblclients.credit instead and the command at the bottom-(addInvoicePayment($invoiceid,$bankrouting,$amount,"","bankdraft");  does not get executed?

 

 

$phone = $_GET['phone'];

$bankrouting = $_GET['bankrouting'];

$bankaccount = $_GET['bankaccount'];

$amount = $_GET['amount'];

 

 

$query = "SELECT * FROM tblclients WHERE phonenumber = '$phone'";               

$result = mysql_query($query) or die('Error, query failed');

 

while ($data = mysql_fetch_array($result)) {

$name = $data["firstname"]." " .$data["lastname"];

$unit = $data["address1"];

$residentid = $data["id"];

}

 

$query = "SELECT * FROM tblinvoices WHERE status = 'Unpaid' and userid = '$residentid'";

$result = mysql_query($query) or die('Error, sql failed');

 

while ($data = mysql_fetch_row($result)) {

$invoiceid = $data[0];

}

 

                       

@$pfw_strQuery = "INSERT INTO `phonepmt`(`date`,`phone`,`id`,`name`,`unit`,`routing`,`checking`,`amount`)VALUES (now(),\"$phone\",\"$residentid\",\"$name\",\"$unit\",\"$bankrouting\",\"$bankaccount\",\"$amount\")" ;

$pfw_result = mysql_query($pfw_strQuery);

 

addInvoicePayment($invoiceid,$bankrouting,$amount,"","bankdraft");

 

$done=true;

 

[Barand]

something like this using a LEFT JOIN. If there is no matching invoice, $invoiceid will be null.

<?php
$phone = $_GET['phone'];
$bankrouting = $_GET['bankrouting'];
$bankaccount = $_GET['bankaccount'];
$amount = $_GET['amount'];


$query = "SELECT c.firstname, c.lastname, c.address, i.invoiceid, c.id 
            FROM tblclients c
            LEFT JOIN tblinvoices i
            ON c.id = i.userid AND i.status = 'Unpaid'
            WHERE c.phonenumber = '$phone'";               
$result = mysql_query($query) or die('Error, query failed');

while ($data = mysql_fetch_row($result)) {
   $invoiceid = $data[3];
   $name = "{$data[0]} {$data[1]}";
   $unit = $data[2];
   $residentid = $data[4];
   
   $pfw_strQuery = "INSERT INTO `phonepmt`(`date`,`phone`,`id`,`name`,`unit`,`routing`,`checking`,`amount`)
      VALUES (now(),'$phone','$residentid','$name','$unit','$bankrouting','$bankaccount','$amount')" ;
   $pfw_result = mysql_query($pfw_strQuery);
   
   if ($invoiceid)
   {
       addInvoicePayment($invoiceid,$bankrouting,$amount,"","bankdraft");
   }
   $done=true;  
}
?>