optimisation of a code

Hi all

I wrote a script in PHP with mysql (hum ... to be honnest, I wrote
this script under PostGreSQL but we recently migrated to mysql, so I
had to adapt my code to mysql ... sorry about that ... anyway, it is
the same kind of query, with subqueries !) but this is a very very
long script ...

I was wondering if there is a way to optimize this one, by doing some
JOIN (because the subqueries don't seem to be very powerful ...)

Here is the script :

To resume, it is a script that :
- list all the leads available (1st query)
- For each lead, find 3 members that can buy this lead (2nd query)
- For each member, buy the lead


<?

....

// FIRST QUERY
$q_avail = "select id, loan_type, affiliate_id,
borrower_credit _rating, prop_state, loan_amount, current_value, email,
nb_units, refinance_date, balance, down_payment, purchase_\
price, prop_address1, prop_address2, prop_city, prop_zip,
borrower_first_ name, borrower_last_n ame, borrower_addres s1,
borrower_addres s2, home_phone, office_phone, co_borrower_fi\
rst_name, co_borrower_las t_name, prop_is, co_borrower_cre dit_rating,
time, homeowner, date_creation, borrower_employ er, ";
$q_avail .= " 1 as period, ";
$q_avail .= " $PRICE_SH_TIME1 as price, count(id) as nbsold ";
$q_avail .= " from lead LEFT JOIN purchase ON purchase.lead_i d=lead.id
WHERE ";
$q_avail .= " unix_timestamp( now())-unix_timestamp( date_creation)< =
(24*3600*6) and ";
$q_avail .= " (loan_type='Ref inance' or loan_type='Purc hase' or
loan_type='Home Equity (AAA credit)') ";
//$q_avail .= " and (exclusive=NULL or exclusive=0) ";
$q_avail .= " group by id, loan_type, affiliate_id,
borrower_credit _rating, prop_state, loan_amount, current_value, email,
nb_units, refinance_date, balance, down_payment, purch\
ase_price, prop_address1, prop_address2, prop_city, prop_zip,
borrower_first_ name, borrower_last_n ame, borrower_addres s1,
borrower_addres s2, home_phone, office_phone, co_borrowe\
r_first_name, co_borrower_las t_name, prop_is,
co_borrower_cre dit_rating, time, homeowner, date_creation,
borrower_employ er,period,price ";
$q_avail .= " having count(id) <3 ";

$r_avail = mysql_query($q_ avail);
//echo $q_avail."<BR>< BR><BR>";

$today_midnight = strtotime(date( 'Y-m-d 00:00:00'));

if ($AFF_FIXED_AMO UNTS)
$amount_fixed = $AFF_SHD_AMOUNT ;
else
$amount_fixed = $AFF_PERCENTAGE * .01 *
$PRICE_POINT_IN _DOLLARS;


while ($lead=mysql_fe tch_assoc($r_av ail))
{

$n = $lead[period];
if ($lead[loan_type] == "Refinance" ) $type="refi";
else if ($lead[loan_type] == "Purchase") $type="pur";
else $type = "homeq";
$field = $type."_t$n";
$price = $lead[price];
$id = $lead[id];
$aff_id = $lead[affiliate_id];

// SECOND QUERY
// find the members that fit all the required criterias
$q_members = "select member.id, automated.deliv ery, member.email
from (automated INNER JOIN member ON member.id = automated.membe r_id)
";
$q_members .= " where activated=1 ";
$q_members .= " and website='$SITE_ NAME'";
$q_members .= " and (select count(*) from trans_member where
(unix_timestamp (now())-unix_timestamp( date)) <
(unix_timestamp (now())-'$today_midnigh t') and type='purchase' a\
nd comment LIKE '%automated%' ";
$q_members .= " and member_id=membe r.id and comment LIKE
'%$type%') < max_$field ";
$q_members .= " and balance_in_poin ts > $price ";
$q_members .= " and credit_ratings_ t$n LIKE
'%$lead[borrower_credit _rating]%' ";
$q_members .= " and states LIKE '%$lead[prop_state]%' ";
$q_members .= " and ltv_t$n/100 >= (cast($lead[loan_amount] as
unsigned) / cast($lead[current_value] as unsigned)) ";
$q_members .= " and amount_t$n < $lead[loan_amount] ";
$q_members .= " and $id NOT IN (select lead_id from purchase where
member_id=membe r.id) ";
$q_members .= " AND $aff_id NOT IN (select affiliate_locke d_id
from affiliate_locko ut where member_id=membe r.id) ";
$q_members .= " AND $id NOT IN (select lead_id from purchase where
member_id IN (select member_id_to_ex clude from member_exclusio n where
member_id=membe r.id))";
$q_members .= " ORDER BY balance_in_poin ts DESC";
$r_members = mysql_query($q_ members);

$nbdispo = $NBPERSONS_SHAR ED - $lead[nbsold];

while (($member=mysql _fetch_assoc($r _members)) && $nbdispo>0)
{

BUY THE LEAD FOR THIS MEMBER
$nbdispo--;

}


//}
} // END OF while ($lead=mysql_fe tch_assoc($r_av ail))


?>

Has anybody an idea ?
Thanks very much for your help
Krystoffff