如何在将新产品添加到数据库时自动向用户发送电子邮件？

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;

Answer 1

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.

</div>