How to autosend email to user when new product is added to database?

Question

I have table 'emails' and row 'kategorija' where are stored values(they are in table 'proizvodi' in row 'kategorija') from checkboxes.

In row 'email' are stored emails from users, and users can check checkboxes(values in table 'proizvodi', row 'kategorija'), checked values are stored in table 'emails' in row 'kategorija'.

When new product(it has values like 'alati, satovi and others') is added to database, I need somehow autosend email with information that new product is available in 'kategorija', which has user selected in checkboxes, to users adress stored in table 'emails'.

Table 'emails':

CREATE TABLE IF NOT EXISTS `emails` (
  `id` int(15) NOT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_croatian_ci NOT NULL,
  `kategorija` varchar(255) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Table 'proizvodi' :

CREATE TABLE IF NOT EXISTS `proizvodi` (
  `id` int(11) NOT NULL,
  `naziv` varchar(55) CHARACTER SET utf8 DEFAULT NULL,
  `cijena` decimal(8,2) DEFAULT NULL,
  `slika` text CHARACTER SET utf8,
  `opis` text CHARACTER SET utf8,
  `kategorija` enum('alati','glazbeni_instrumenti','smartphone','laptop','fotoaparat_kamera','tehnicka_roba_ostalo','sportska_oprema','satovi','kucanski_aparati','ostalo_ostalo') CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=latin1;

What does the relevant PHP code look like? I suspect you may want to handle this in PHP when you insert a new product. — devlin carnate, Feb 10 '16 at 16:04
If you have some UI to add product kategorija then at that time you can implement simple observer pattern and trigger the functionality which send mail to user with whatever details you want. this link may help you https://sourcemaking.com/design_patterns/observer/php — Jimit Shah, Feb 10 '16 at 16:08
How can I set some function to autosend emails to users with information about new products iin category which was selected by user? — , Feb 10 '16 at 18:08

score 0 · Answer 1 · answered Feb 10 '16 at 16:12

I might not understand the question properly. I think it is quite simple:

When you insert the new product, retrieve its product id and category.
Then select email from emails where kategorija where categoria like '%%' to get the emails to use.
Finally iterate over those email addresses and send out the emails with your favorite email lib in your favorite language.

You might have find a better solution to store multiple categories for the emails, now I can see that you're using a plain string. It is not totally rocket proof, you shall rather use an n:n relation between a (yet to define) category table and the email table, and use the (yet to define) category table as a foreign key instead of using a hardcoded enum in the table definition of products for categories. Having a dedicated category table allows you to add further categories at any time without changing any code or table structure.

I (although I am not a native English speaker either) found a very good idea to use plain English terms for table names, column names, as well as variables, etc. This allows you to involve later on other developers.

Some other databases, for example Oracle has a built-in procedural language which allows you sending an email right from the database. Combining that with a database trigger when a new product is inserted, you can have this whole functionality implemented in about 10 lines of PL/SQL code.

I found that Oracle well worth of its price, and if you can't afford that, Oracle XE is available at free of charge. With the built-in PL/SQL language and lots of built-in packages, it makes database oriented application development a snap. This offers two magnitude faster speed than e.g. Hibernate plus MySQL in a heavy I/O transactional application.

I need some function to autosend emails when new product are added, without my interfering to send email. — , Feb 10 '16 at 17:13

score 0 · Answer 2 · edited May 23 '17 at 10:28

Sorry, it is not possible to achieve your goal in MySQL. See https://stackoverflow.com/questions/31667462/how-to-send-an-email-from-mysql-using-a-stored-procedure for the reasons.

A possible workaround by making the database outputting emails into files, which is then processed by another application is described here: How to send email from MySQL 5.1

Another workaround is to create a custom UDF which can be called from your trigger on the product table. User-defined functions (UDF) allow you to call external code from your database, this way you can implement whatever feature you need.

If you could use Oracle, the built-in UTL_MAIL package allows you sending email directly from the database, for example from a trigger on the product table. An example of sending an email is here: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL

score 0 · Accepted Answer · answered Feb 19 '16 at 09:03

I have created new table 'obavijest'.

CREATE TABLE IF NOT EXISTS `obavijest` (
  `id` int(10) unsigned NOT NULL,
  `email` varchar(255) COLLATE utf8_bin NOT NULL,
  `kategorija` varchar(255) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

On table 'emails' I created trigger to auto send values to table obavijest.

CREATE TRIGGER `obavijesttrig` AFTER INSERT ON `emails`
 FOR EACH ROW BEGIN  INSERT INTO obavijest (id, email, kategorija) 
        VALUES (NEW.id, NEW.email, NEW.kategorija);
 END

Only thing left is to create a php script for getting emails from table 'obavijest' and send to this email addresses and run it with cron job.

How to autosend email to user when new product is added to database?

3 Answers3