I'm writing a user-driven website that may reach a peak of 100,000-200,000 users. It's a highly social site, so there will be private messaging. Where there's private messaging, there have to be massive ignore/block lists. I got the individual blocking down: every user has a row in a table called `prefs` (in a mysql db) and the `prefs` table has a "blocked users" colums. The blocked users colum holds comma-seperated id's of the users who are not allowed to send private messages to this person. That works. Now, I want to create a mass filtering system. In the website preferences, a user may opt not to recieve private messages from people under 18 years of age. Or from people of the male gender. Or from people who don't live in the same city. Etc. At first, I though that after a user selects his preference, I would run a mass query, get the id's of all the users that fit the description, and put the id's in the "blocked_users" column. Then I realized that I'm dealing with 100k-200k users, and doing this is unfeasable because it will seriously slow down the database. How else can I design this system in a way that minimizes the amount of queries I have to run? To give a hint of what happens when a user is on the recipient's blocked list, here's the code for sending a private message:
Code: Select all
<?php
//more code above
$to = $_POST["to"]; //comma seperated usernames explode()'d later
$subject = $_POST["subject"];
$message = $_POST["message"];
if(($subject == "") && ($message == "")) {
print("<font color = \"red\">Please enter either a subject or a
message since a blank private message cannot be sent.</font>
<br />");
print($form);
}
else {
$user_names = explode(", ", $to); //who are we sending to? explode the comma-separated usernames from the "TO" feild
for ($i = 0; $i < sizeof($user_names); $i++) {
$userid = sql_pull("SELECT `id` FROM `users` WHERE `username` = '"
. $user_names[$i] . "'", $stats["db"]);
//make sure this user isn't blocked
$get_blocked_users = sql_pull("SELECT `blocked_users` FROM `prefs` WHERE `id`=".$userid[0]['id'], $stats['db']);
$blockedusers = explode(",",$get_blocked_users[0]['blocked_users']);
for ($i2=0; $i2<sizeof($blockedusers); $i2++){
print_r ($stats);
if ($stats['userid']==$blockedusers[$i2]){
$blocked[] = $user_names[$i];
}
else{
$result = $user->send_message($userid[0]["id"], $subject, $message);
}
}
//more code
?>