php - 发送类似于职位提醒的电子邮件通知需要一些帮助

Question

我正在尝试创建一个电子邮件通知系统，该系统会在将新项目添加到数据库时发送。

用户通过提供他们的电子邮件地址并通过选择他们的偏好进行注册，如下所示 -

Color [n/a, pink, red, blue, and green]
Shape [n/a, square, rectangle, and circle]
width [n/a, narrow, wide]

这些都是下拉列表。

因此，如果用户将颜色选择为红色并将所有其他选项保留为 n/a，则这意味着他们希望接收任何颜色为红色的新项目的通知。如果他们选择 Colore = red 和 Shape = circle，他们只想接收那些颜色为红色且形状为圆形的项目的通知。

项目表

id, color, shape, width, height, name, description, image

示例行

1, red, circle, narrow, tall, ABC, ABC Descrip, yellowFlag.jpg
2, blue, circle, wide, short, XYZ, XYZ Descrip, redFlag.jpg

通知表

id, email, color, shape, width

示例行

1, test@dummy.org.uk, red, 0, 0, 0
2, xyz@alpha.com, blue, circle, 0
3, abc@abcdef.com, pink, square, wide

用户可以选择任何选项。例如，如下所示，他们可以选择

color = red, shape = 0, width = 0 [0 is n/a, selected red only]
color = 0, shape = square, width= 0 [selected shape only]
color = 0, shape = 0, width= 'narrow' [selected width only]
color = red, shape = square, width= 0 [selected both color and shape but not width]
color = 0, shape = square, width= 'narrow' [selected both shape and width but not color]
color = red, shape = square, width= narrow [selected all 3 options]
color = red, shape = 0, width= narrow [selected color and width but not shape]

我的问题是 -如果我将第 2 项(在项目表上方)添加到数据库中，我想向在通知表中匹配的用户发送通知。我能想到的查询通知表的唯一方法是类似

1. SELECT email FROM notifications WHERE color = 'blue', shape = '0', '0'
2. SELECT email FROM notifications WHERE color = 'blue', shape = 'circle', '0'
3. SELECT email FROM notifications WHERE color = 'blue', shape = 'circle', 'wide'
4. SELECT email FROM notifications WHERE color = '0', shape = '0', 'wide'
5. SELECT email FROM notifications WHERE color = 'blue', shape = 'circle', '0'
6. SELECT email FROM notifications WHERE color = 'blue', shape = 'circle', 'wide'

...等等

这是一种低效的方法。截至目前，选项数量为 3(颜色、形状、宽度)，但如果必须添加另外几个选项，它都会发疯(和我一样)。必须有更好的方法来实现这一点，因为已经有很多应用程序在做它，比如工作提醒等。你能建议我一个更好的选择/想法来管理这些。我在更改表格结构等方面没有遇到任何问题，因此欢迎提出任何想法。如果您有任何疑问，请询问。感谢您的帮助。

非常感谢。

Answer 1

我有一个表，其中收集了给定用户的所有偏好:

表格条件:

id: some ID for the criteria
label: human readable description of the criteria
column: name of the corresponding column in the notification table
values: optional comma-separated list of possible values

表 user_preferences

id: some id for the preference
user_id: the user
criteria_id: the criteria
value: the chosen value for the criteria

现在找到蓝色和方形偏好的用户:

SELECT u.id, u.email 
FROM user u, criteria c, user_preferences up
WHERE
      up.id = u.id
  AND c.id = up.criteria_id
  AND c.column = 'shape' 
  AND up.value='square'

INTERSECT

SELECT u.id, u.email 
FROM user u, criteria c, user_preferences up
WHERE
      up.id = u.id
  AND c.id = up.criteria_id
  AND c.column = 'color' 
  AND up.value='blue'

当您添加一个项目时，您会希望找到具有蓝色或方形偏好的用户:

SELECT u.id, u.email 
FROM user u, criteria c, user_preferences up
WHERE
      up.id = u.id
  AND c.id = up.criteria_id
  AND ( (c.column = 'shape' AND up.value='square')
     OR (c.column = 'color' AND up.value='blue')
  )