php - Batch processing notifications of job tracking -

at moment we're using 3 nested foreach loops information run batch. i'm sure information single mysql statement joins , sub-queries.

we have 30 categories 2000 users. our aim 100 categories 100000 users though foreach loops not ideal (even take minute run).

circumstance: users want notified if there work available trade can in area

goal: batch process (daily, weekly, etc) notifications put in outbox

technology: php, mysql

what have far:

database:

 "table.notification_options" : [id][user_id][category]  "table.user" : [id][user_id][method_of_contact][contact_frequency][center_of_work_area_long][center_of_work_area_lat][distance_from_center]  "table.work" : [id][post_date][longitude][latitude][category] 

code:

foreach user{     foreach category tracked{         foreach job in category posted <> $current_date-$batch_frequency{             if job inside workspace{                 notify_user(job);             }         }    } } 

the desired result array of arrays of job_ids user_id key [user_id]=>{jobs}

e.g.

    {         [user1]{                  job1,                  job4,                  job28                },         [user34]{                  job3,                  job4,                  job34,                  job78                 }      { 

edit:

i've got bit more efficient can select jobs 1 user. still requires foreach user.

   $category_id = get_category_from_notification_options($userid);    $user_distance = get_user_work_distance($userid);     "select distinct work.id workid, ( 6371 * acos( cos( radians(-46.409939) ) * cos( radians( jobs.lat ) ) * cos( radians( jobs.lng ) - radians(168.366180) ) + sin( radians(-46.409939) ) * sin( radians( jobs.lat ) ) ) )          distance          work,user         work.categoryid == $category_id         having distance < $user_distance         order distance"; 

i think should other way around make more efficient. below show process used create query. final query need. explain steps perhaps in future.

first select jobs. there lot less jobs users if goal 100.000 users.

select job.id, job.category table.work job 

now have jobs, lets see users want notified it.

select job.id, job.category, notify.user_id table.work job left join table.notification_options notify on job.category=notify.category notify.user_id not null 

this creates list each job, userid's want notified it. added where clause remove jobs list nobody wants see. can join users table user details aswell.

select job.id      , job.post_date      , job.longitude      , job.latitude      , usr.user_id      , usr.method_of_contact      , usr.contact_frequency      , usr.center_of_work_area_long      , usr.center_of_work_area_lat      , usr.distance_from_center      , ((acos(sin(usr.center_of_work_area_lat * pi() / 180) * sin(job.latitude * pi() / 180) + cos(usr.center_of_work_area_lat * pi() / 180) * cos(job.latitude * pi() / 180) * cos((usr.center_of_work_area_long – job.longitude) * pi() / 180)) * 180 / pi()) * 60 * 1.1515) `distance` table.work job left join table.notification_options notify on job.category=notify.category left join table.user usr on notify.user_id=usr.user_id notify.user_id not null having `distance`<=usr.distance_from_center order usr.user_id asc, distance asc 

i included distance in query. notice use having check if distance smaller user supplied. if add where clause error saying distance unknown column. added order by class first sort on user id , on distance. make easier create array want in php.

now there lot of ways implement daily/weekly intervals. 1 of them create seperate scripts each interval , select users set it. example, create script 'daily.php' run each day , have following query

select job.id      , job.post_date      , job.longitude      , job.latitude      , usr.user_id      , usr.method_of_contact      , usr.contact_frequency      , usr.center_of_work_area_long      , usr.center_of_work_area_lat      , usr.distance_from_center      , ((acos(sin(usr.center_of_work_area_lat * pi() / 180) * sin(job.latitude * pi() / 180) + cos(usr.center_of_work_area_lat * pi() / 180) * cos(job.latitude * pi() / 180) * cos((usr.center_of_work_area_long – job.longitude) * pi() / 180)) * 180 / pi()) * 60 * 1.1515) `distance` table.work job left join table.notification_options notify on job.category=notify.category left join table.user usr on notify.user_id=usr.user_id notify.user_id not null , usr.contact_frequency = 'daily' having `distance`<=usr.distance_from_center order usr.user_id asc, distance asc 

now have query, lets create php code it. can loop trough rows , create array. instead of creating array directly process result. because if create array first, need loop trough array again afterwards.

<?php $arnotify = array(); foreach ($queryresult $row) {   $userid = $row->user_id;   $jobid = $row->id;    //check if there entry user in database, else create   if (!array_key_exists($userid, $arnotify))     $arnotify[$userid] = array();    //and push job   $arnotify[$userid][] = $jobid;    //the array being created, still process job directly   //notify_user($userid, $jobid);  }  var_dump($arnotify); ?> 

there go, array want jobs sorted on closest first.