mysql query help-it almost works

[tomdchi]

I have the query:

$query = "select * from tblinvoices where duedate<'$startdate1'";
$result = mysql_query($query);
while($data = mysql_fetch_array($result)){
$invoiceid=$data["id"];

  $query2 = "update quickbooks_trigger set qb_status_code='PROC' where qb_table_name='tblinvoices' and qb_id_value='$invoiceid'";
	mysql_query($query2) or die('Error, query failed');

$query3 = "select * from tblaccounts where invoiceid='$invoiceid'";
$result2 = mysql_query($query3);
while($data = mysql_fetch_array($result2))
$id=$data["id"];

  $query4 = "update quickbooks_trigger set qb_status_code='PROC' where qb_table_name='tblaccounts' and qb_id_value='$id'";
	mysql_query($query4) or die('Error, query failed');
	$done=true;
}

 

The first update query works ok.  the problem is with the second.  It does as instructed if there are no multiple records to update.

It is getting payment id's and marking them processed.  It gets the id's by the invoice id.  Some invoices have more than one payment so there would be more than one record to update for that invoice.  It only updates one payment record for every invoice.  I have tried everything I can think of but nothing makes it update all records it is supposed to.

I have run the just the: query select id from tblaccounts where invoiceid='1010' and it returns two id's for this invoice.  Why is this only updating one of them??

[fenway]

why not simply write a multi-table update?

[tomdchi]

I don't think a multi update will work since the first query will only have a list of single invoice id's and the second could have more than one invoice id.  But I may be wrong

 

I have narrowed it down some I think and I have this query:

 

SELECT tba.id as 'pid', ti.id FROM tblaccounts tba JOIN tblinvoices ti ON ( tba.invoiceid = ti.id ) AND ti.duedate < '2009-01-01'

 

This returns:

 

pid  id

7    1010

88  1010

21  1012

8    1014

18  1015

9    1016

4    1016

5    1017

 

I have tried using a alias for tba.id, hard coded in the date but it will not update.

Is there anything wrong with the code below?

$query = "SELECT tba.invoiceid, tba.id as 'pid', ti.id FROM tblaccounts tba JOIN tblinvoices ti ON ( tba.invoiceid = ti.id ) AND ti.duedate < '2009-01-01'";
$result = mysql_query($query);
while($data = mysql_fetch_array($result)){
$pid=$data["id"];

  $sql = "UPDATE quickbooks_trigger SET qb_status_code='PROC' WHERE qb_table_name='tblaccounts' AND qb_id_value='$pid'";
	mysql_query($sql) or die('Error, query failed');

}

 

 

[fenway]

I don't think a multi update will work since the first query will only have a list of single invoice id's and the second could have more than one invoice id.  But I may be wrong

I don't know what you mean... you're simply taking a bunch of values from a select statement and using them to update another table, right?