Member Avatar for stylemessiah

Hi,

Basically what im looking to implement is a function where a user performs a search on my site, and is presented with the results and an option to be emailed similar items in future.

I have all the search stuff done, but have no idea where to start on the email me similar items in future part.

Any help much appreciated

  • 2 Contributors
  • 3 Replies
  • 118 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by baki100

Dani AI

Generated

A practical pattern is to treat the "email me similar items" action as a saved subscription to a canonicalized search. Since already has the search UI, capture the exact filters (category IDs, tags, keywords, price ranges) in a structured record, include frequency and a last-run timestamp, and use that record as the single source when deciding what to email later. This keeps the UI simple and moves matching logic into a single background worker.

Suggested minimal schema (example):

saved_searches
- id INT PK
- user_id INT
- name VARCHAR(100)
- criteria JSON
- frequency ENUM('daily','weekly','immediate')
- last_run DATETIME
- active TINYINT(1)
- created_at DATETIME

For reliability, use a notification queue to store candidate matches and avoid duplicate sends:

notification_queue
- id INT PK
- saved_search_id INT
- item_id INT
- queued_at DATETIME
- sent_at DATETIME NULL

A background worker (scheduled by cron or systemd timer) iterates active saved_searches where next run is due, turns criteria into a parameterized query (or uses indexed tag/category joins / fulltext for keywords), finds items not yet sent for that search, writes rows to notification_queue, batches those into an email digest, sends via a proper mail library or transactional API, and updates sent_at / last_run. For immediate alerts, match new inserts against watchers and enqueue notifications asynchronously rather than sending inside the insert transaction.

Operational and compliance notes: index searchable columns (created_at, category_id, tag mappings), track item IDs sent to prevent duplicates, use DKIM/SPF and a reputable SMTP/API provider for deliverability, include unsubscribe tokens and respect opt-outs, and log both sends and bounces for debugging. ’s suggestion to persist user interests is the right start — extend it with structured criteria, dedup logic, and a queued worker for scale.

Member Avatar for baki100

i don't think MySql has built in email functions like Oracle's PL/SQL so instead you will have to do it from the front-end system which i assume is php.

Member Avatar for stylemessiah

i don't think MySql has built in email functions like Oracle's PL/SQL so instead you will have to do it from the front-end system which i assume is php.

no i realise that i cant perform this via mysql, im more looking for advice on how to approach it, not looking for code necessarily.

i assume it will involve cron, php and mysql of course, im just boggled by how to implement it.

Member Avatar for baki100

so first you will have a user table that links to a favourite_table that has the user id and a product category id that links to a product_category table in this table you a product category id and the category description (games, software, hardware, peripherals). so when a user visits those pages more than 5 times (or whatever amount) insert the product_category id and the user id the in the favourite_table, so every month you can run the cron and link all the new products and product category id for that user and email him/her a catalogue of the new products, here some stuff i found on the net about email function in php (i don't know php):
http://email.about.com/cs/phpemailtips/qt/et031202.htm
http://www.smartwebby.com/PHP/emailsending.asp

research some stuff on email servers

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.