Private Message Filtration System

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Private Message Filtration System

Post by evilmonkey »

Hello,

I am writing a member driven portal, and a part of that is a private messaging system. I am using PHP and a MySQL database (pretty standard). Before a private message is allowed to be sent, I need to make sure that the user trying to send this message can bypass the recipient's filter. The filter is based on a few variables, including the person's age, sex, location, etc. For the simplicity of this example, let's use those three varaibles. I was considering doing it like this: In the user control panel, have a bunch of dropdown boxes specifying the values for each of the filter variables. In this case, it's $agefrom, $ageto, and $location. Once the form is submitted, I would do something like this:

Code: Select all

$filter = $_POST['agefrom'].$_POST['ageto'].$_POST['location'];
mysql_query ("INSERT INTO `filters` (`id`, `filter`) VALUES ({$_SESSION['id']}, '$filter')");
So this leaves me with something like 2327FThornhill, meaning only recieve private messages from females ages 23 to 27 who live in Thornhill. When someone is trying to send a message, I would run the following check:

Code: Select all

//assume that $user is an array of user information
$result = mysql_fetch_assoc(mysql_query("SELECT * FROM `filters` WHERE `id` = '{$_POST['recipient']}'"));
if (strstr($result['filter'], $user['sex']) && strstr($result['filter'], $user['location'])){
   //send
}
else {
//don't send
}
I have three questions. First of all, is it a good idea to do it like this? Second of all, how do I deal with the ages? And finally, how do I deal with wildcards?

Thanks! :D
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Private Message Filtration System

Post by Christopher »

It may be easier to just search for the recipient with the constraints added and see if you get a row back. I'd prefer separate fields for the constraints rather than packing them all into a string.

Code: Select all

$result = mysql_fetch_assoc(mysql_query("SELECT * FROM filters WHERE `id` = '$recipient' AND age BETWEEN $agefrom AND $ageto AND location='$location'"));
if (mysql_num_rows() > 0){
   //send
}
else {
//don't send
}
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Private Message Filtration System

Post by Jenk »

arborint wrote:It may be easier to just search for the recipient with the constraints added and see if you get a row back. I'd prefer separate fields for the constraints rather than packing them all into a string.

Code: Select all

$result = mysql_fetch_assoc(mysql_query("SELECT * FROM filters WHERE `id` = '$recipient' AND age BETWEEN $agefrom AND $ageto AND location='$location'"));
if (mysql_num_rows() > 0){
   //send
}
else {
//don't send
}
You can cut down that code to:

Code: Select all

<?php

if (mysql_num_rows(mysql_query("SELECT * FROM filters WHERE `id` = '$recipient' AND age BETWEEN $agefrom AND $ageto AND location='$location'")) > 0){ 
   //send 
} 
else { 
//don't send 
}

?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

That really reduces readability though, something you should never sacrafice IMO.

Is there any specific reason you combine 3 variables into 1 column? Why not have a column for each?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

horses for courses.. I can read it fine :P

However, the code I cut down from was syntactically incorrect anyway - $result would contain the first row and mysql_num_rows() would cry for not having a resource passed to it :)
User avatar
Zoram
Forum Contributor
Posts: 166
Joined: Sun Aug 18, 2002 3:28 pm
Location: Utah
Contact:

Post by Zoram »

If you were to use just a string as a constraint wouldn't you want to have a delimiter to separate the different variables... then you could explode it later? But isn't there a better db solution that you could use to eliminate that and make it easier to search for specific criteria?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Jenk wrote:horses for courses.. I can read it fine :P

However, the code I cut down from was syntactically incorrect anyway - $result would contain the first row and mysql_num_rows() would cry for not having a resource passed to it :)
I think I proved both your and Jcart's points! ;) This horse missed the mysql_fetch_assoc() completly. I always put them on separate lines so I check the result of the query before attempting the fetch.
(#10850)
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Okay guys, hang on. I have a `users` table where all the information is stored about every user (such as his age, location, sex, etc.). The `filters` database only has the constraints of the person TO WHOME the message is being sent. For instance, if I'm id=1, I want to recieve mail from only certain people, I will have an entry in the `filters` table.. Something like this:

id || filter
---------------
1 || "18,23,m,Vancouver"

That is assuming I do it using strings.

Jenk and Arborint, can you show me how you'd set up the tables to do what you're suggesting, because I don't really understand. The filters table does not hold values for the variables of the person who is SENDING the message, just the constraints set by the recipient. The `users` table holds all values for every user on the site. Having delimiters is actually a good idea, thanks Zoram. :)

Thanks everyone, looking for more feedback. :)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

CREATE TABLE `users` (
`userid` INT NOT NULL, 
`username` VARCHAR(30) NOT NULL, 
`surname` VARCHAR(30), 
`age` TINYINT(2)) 
PRIMARY KEY (`userid`);
:)

Separate each type of info into it's own column.

Messages table:

Code: Select all

CREATE TABLE `messages` (
`messageid` INT NOT NULL, 
`from_userid` INT NOT NULL, 
`to_userid` NOT NULL, 
`message` TEXT NOT NULL, 
`datesent` DATE NOT NULL) 
PRIMARY KEY(`messageid`);
Then to select the users incoming messages:

Code: Select all

SELECT `u`.`username`, `m`.`message` 
FROM `messages` AS `m` 
JOIN `users` AS `u` 
ON `m`.`from_userid` = `u`.`userid`
WHERE `m`.`to_userid` = <userid>;
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

That's exactly what I'm doing...where's the filter part though? :?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

you've already been given the answer to that with the "`age` BETWEEN .." part of the SQL statement.

The other way to do it is to form an arbitrary age grouping system..

18-25
25-30
30-35
35-40

etc..
and assign them a group tag, namely 0,1,2,3,4,etc..

then instead of `age` BETWEEN .. you can just do WHERE `agegroup` = '$ageGroup'
User avatar
Zoram
Forum Contributor
Posts: 166
Joined: Sun Aug 18, 2002 3:28 pm
Location: Utah
Contact:

Post by Zoram »

The way that I would do it, not meaning its the best way, is this:

You have:
1 || "18,23,m,Vancouver"
If you were to use this system and assign each filter to a user then you could explode it out on the comma. Personally rather than making all the code to go through and figure out what array item corresponds to what and comparing it i would use a table. As i see if if you have 15 different filter options you are going to have a string like :
1 || "18,23,m,Vancouver,,,,,,,(etc...)"
For all the people. And if you change or add to it you have to rethink your code that checks it and manipulate it. If you were to use a table for the same process then you could even have multiple filters per user if they have more than one criteria and just give each option as much space as needed... then you can use a select statement when the person is trying to load the form to send the message and if they don't add up to the critera that isn't set to NULL in the table then they can send the message, else display so sorry, you just can't do that.

That is, once again, just how i would go about it...
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Yeah, Zoram, that sounds like the best way. Thanks everyone!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

evilmonkey wrote:For instance, if I'm id=1, I want to recieve mail from only certain people, I will have an entry in the `filters` table.. Something like this:

id || filter
---------------
1 || "18,23,m,Vancouver"
So an "unpacked" DB table would look like:

id || agemin || agemax || sex || city
---------------
1 || 18 || 23 || m || "Vancouver"

My idea was that you search records where "id" equals the id of the recipient, and the age, sex and city check are on the sender's values. If the sender has data that matches one or more criteria of the recipient's entries then the sender is allowed to send the message. No records need to be fetched (saving time) because all that matters is if 1 or more rules match.

The SQL would look like this:

SELECT * FROM filters WHERE `id` = '$recipient' AND agemin<=$age AND agemax>=$age AND location='$location'
(#10850)
Post Reply