do calculation in 2nd select query based on 1st select query

[hazrizal84]

hi guys..i really need your help to solve my dummy problem coz i'm new in sql and php..actually i have 2 queries statements.. the 1st one will produce id_iklan based on some criterias entered.. so the 2nd query will find the sum of hit_final, using the id_iklan in the previous query. the problem is that, the 1st query produce thousands of data which each of data will be used in the 2nd query. the 2nd query also produce thousands of data.. so what i did will used a lot of time and some will be ignored by server.. the codes are as follow :


$cari_lot=mysql_query("SELECT * FROM lot a, iklan b WHERE a.id_team='$id_team' and a.active_date<='$sdb' and a.active_date<'$today' and a.expiry_date>'$sdb' and a.expiry_date>'$today' AND a.id_iklan=b.id_ads");

while ($res3=mysql_fetch_array($cari_lot)){
$hit_final=0;
$id_iklan=$res3['id_ads'];
$id_lot=$res3['id_lot'];
$hit=$res3['hit'];
$id_member=$res3['id_member'];

$cari_payment=mysql_query("SELECT * FROM payment WHERE id_ad='$id_iklan'");
if(mysql_num_rows($cari_payment)){
while($res4=mysql_fetch_array($cari_payment)){
$hit_final+=$res4['hit_final'];
}

[rajmv]

you can have the sql server do the calculation for you, perhaps that'll speed things up;

(1 sql statement:)

SELECT COUNT(*) FROM payment GROUP BY id_ad WHERE id_ad IN (
SELECT a.id_iklan FROM lot a, iklan b WHERE a.id_team='$id_team' and a.active_date<='$sdb' and a.active_date<'$today' and a.expiry_date>'$sdb' and a.expiry_date>'$today' AND a.id_iklan=b.id_ads
)

it's called a "nested sql statement".
(i'm just guessing here as to what fields to use)

it's hard to get these things right unless you have full specifications of the database, it's tables and fields and rowcounts.

and my sql's a bit rusty, so this might not work / get what you want the first time around. i recommend sql tutorials found via google, there are plenty of them, also for this.