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
thislead (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_name, borrower_address1,
borrower_address2, home_phone, office_phone, co_borrower_fi\
rst_name, co_borrower_last_name, prop_is, co_borrower_credit_rating,
time, homeowner, date_creation, borrower_employer, ";
$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_id=lead.id
WHERE ";
$q_avail .= " unix_timestamp(now())-unix_timestamp(date_creation)<=
(24*3600*6) and ";
$q_avail .= " (loan_type='Refinance' or loan_type='Purchase' 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_name, borrower_address1,
borrower_address2, home_phone, office_phone, co_borrowe\
r_first_name, co_borrower_last_name, prop_is,
co_borrower_credit_rating, time, homeowner, date_creation,
borrower_employer,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_AMOUNTS) $amount_fixed = $AFF_SHD_AMOUNT; else $amount_fixed =
$AFF_PERCENTAGE* .01 *
$PRICE_POINT_IN_DOLLARS;
while ($lead=mysql_fetch_assoc($r_avail))
{
$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.delivery,member.email
from (automated INNER JOIN member ON member.id = automated.member_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_midnight') and type='purchase' a\
nd comment LIKE '%automated%' "; $q_members .= " and member_id=member.id and comment LIKE
'%$type%') < max_$field "; $q_members .= " and balance_in_points > $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=member.id) "; $q_members .= " AND $aff_id NOT IN (select affiliate_locked_id
from affiliate_lockout where member_id=member.id) "; $q_members .= " AND $id NOT IN (select lead_id from purchase
where
member_id IN (select member_id_to_exclude from member_exclusion where
member_id=member.id))"; $q_members .= " ORDER BY balance_in_points DESC"; $r_members = mysql_query($q_members);
$nbdispo = $NBPERSONS_SHARED - $lead[nbsold];
while (($member=mysql_fetch_assoc($r_members)) && $nbdispo>0) {
BUY THE LEAD FOR THIS MEMBER $nbdispo--;
}
//}
} // END OF while ($lead=mysql_fetch_assoc($r_avail))
?>
Has anybody an idea ?
Thanks very much for your help
Krystoffff