PHP 和 mysql 订阅搜索/匹配查询
我有一个房地产网站，有多个类别。 我希望用户能够通过电子邮件订阅特定的优惠列表，它们可以是来自某个类别的优惠……也可以是来自搜索列表的优惠。
我如何以最好的方式在 mysql 中存储它们的订阅...以及我如何进行通知。
我想出的最好的方法是将一个 mysql 字符串与用户 ID 一起存储在 mysql 表中，然后运行一个 cron 来获取所有这些内容并迭代它......运行 cron，如果有新广告，它会发送一封电子邮件。
但我不认为这是最好的想法，因为这也意味着如果数据库设计发生变化（例如字段名称），那么所有这些查询都会变坏。
多谢
I have a real estate website that has several categories.
And i want the users to be able to subscribe via email to a certain list of offers, they can either be offers from a category ... or offers from a search list.
How can i store they're subscription the best way in mysql ... and also how do i go about and do the notification.
The best i came up with is to have a mysql sting stored in mysql table with the user ID and run a cron that takes all of that and iterates through it ... runs the cron and if there are new ads it sends an email.
But i don't think that this is the best idea, since it also means that if there is a change in database design ( ex, field names ) then all those queries would go bad.
thanks a lot
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一般来说，您希望将表设计得尽可能灵活，以应对意外的变化。 如果这些字段发生变化，您的查询无论如何都需要更改。
如果没有具体细节，这些将是我的指导方针：
有一个包含索引和电子邮件的用户表
有一个订阅类型表
有一个用户 ID + 订阅查找表
有一个待处理消息表
当有新更新时，收集所有电子邮件并在待处理消息表中创建消息日志条目。
构建您的 Cron，每隔几分钟抓取 20 条未发送的消息，并在消息发送后将其删除。
我之所以说通过 CRON 迭代发送它们，是因为随着订阅者的增长，您不希望同时发送数百封电子邮件并占用服务器的邮件队列资源。 您可以根据需要继续添加消息，您的队列将持续发送它们。
我们一直在所有的群发电子邮件应用程序、错误记录和提醒通知系统中非常有效地使用这种方法。 它可能不是最好的解决方案，但事实证明它非常易于管理和灵活。
In general you want to design your tables to be as flexible as possible to account for unexpected change. If those fields change your queries would need to change anyway.
Without having the specifics, these would be my guidelines:
Have a Users Table with the index and email
Have a subscription types table
Have a User ID + Subscription lookup table
Have a pending messages table
When a new update is present, collect all the emails and create a message log entry into a pending messages table.
Build your Cron to grab 20 unsent messages every few minutes and delete the message once it's sent.
The reason I say to iteratively send them via the CRON is because as your subscribers grow you don't want to have hundreds of emails have to go out at once and tie up your server's resources for the mail queue. You can just keep adding messages as need be and your queue will consistently send them out.
We have been using this method pretty effectively for all our mass-emailing applications, error logging and reminder notification systems. It may not be the best solution but it's shown to be very manageable and flexible.
如果你改变数据库设计，你肯定需要修复一些东西，这就是这个世界的运作方式。
如果由于某种原因您很难更改
cron任务，您可以创建一个始终返回适合该任务的结果集的存储过程，然后更改该存储过程。If you change database design, you'll certainly have to fix something, that's how this world works.
If for some reason it's hard for you to change the
crontask, you can create a stored procedure that always returns a resultset suitable for the task, and change the stored procedure instead.