I think you should do it the other way around to make it more efficient. Below I will show you the process that I used to create the request. So only the final request is what you need. But I explain the steps, so maybe this will help you in the future.
First, I would select all the tasks. Most likely, there are far fewer jobs than users if your goal is 100,000 users.
select JOB.id, JOB.category FROM table.work JOB
Now that we have all the tasks, let's see which users want to be notified about this.
select JOB.id, JOB.category, NOTIFY.user_id FROM table.work JOB LEFT JOIN table.notification_options NOTIFY ON JOB.category=NOTIFY.category WHERE NOTIFY.user_id IS NOT NULL
This creates a list for each job, all the identifier of the user who wants to be notified of this. I added a WHERE to remove all jobs from the list that no one wants to see. Now we can JOIN users table to get user information.
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) AS `distance` FROM 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 WHERE NOTIFY.user_id IS NOT NULL HAVING `distance`<=USR.distance_from_center ORDER BY USR.user_id ASC, distance ASC
I included the distance in the request. Please note that I am using HAVING to check if the distance is shorter than that provided by the user. If you add it to the WHERE , you will get the error message distance - unknown column. I also added the ORDER BY class to sort it first by user id and then by distance. This will make it easier to create the array you want in PHP.
Now there are many ways to implement daily / weekly intervals. One of them is to create separate scripts for each interval and select only those users who installed it. For example, you can create a script 'daily.php' that you run every day and have the following request
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) AS `distance` FROM 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 WHERE NOTIFY.user_id IS NOT NULL AND USR.contact_frequency = 'daily' HAVING `distance`<=USR.distance_from_center ORDER BY USR.user_id ASC, distance ASC
Now we have a request, let's create PHP code for it. We can iterate over all rows and create an array. Obviously, instead of creating an array, you can also directly process the result. Because if you create an array first, you need to loop around that array again.
<?php $arNotify = array(); foreach ($queryresult as $row) { $userid = $row->user_id; $jobid = $row->id; //check if there is an entry for the user in the database, else create it if (!array_key_exists($userid, $arNotify)) $arNotify[$userid] = array(); //and then push the job $arNotify[$userid][] = $jobid; //the array is being created, but I still like to process the job directly //notify_user($userid, $jobid); } var_dump($arNotify); ?>
There you go, an array, as you want, with jobs sorted by the nearest first.