Database Announcements Table – Add Excutable Code Within

I have a database containing over 1,000 item information and I am now developing a system that will have this check the API source via a regular Cron Job adding new entries as they come. Usually, but not always the case, when a new item is released, it will have limited information, eg; Image and name only, more information like description can sometimes be initially withheld.

With this system, I am creating a bulletin to let everyone know new items have been released, so like most announcements, they get submitted to a database, however instead of submitting static content to the database for the bulletin, is it possible to submit something which will be executed upon the person loading that page and that bulletin data is firstly obtained then the secondary code within run?

, For example, within the database could read something like the following

<p>Today new items were released!</p>
<?php $item_ids = "545, 546, 547, 548"; ?>

And then on the page, it will fetch the latest known information from the other database table for items “545, 546, 547, 548”

Therefore, there would be no need to go back and edit any past entries, this page would stay somewhat up-to-date dynamically.

Answer

Typically you would do something like have a date field on your items, so you can show which items were released on a given date. Or if you need to have the items associated with some sort of announcement record, create a lookup table that joins your items and announcements. Do not insert executable code in the DB and then pull it out and execute it.

CREATE TABLE `announcements` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `publish_date` DATETIME NOT NULL,
    `content` text,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `items` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `title` VARCHAR(128) NOT NULL,
     `description` text,
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `announcement_item_lkp` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `announcement_id` int(11) unsigned NOT NULL,
     `item_id` int(11) unsigned NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `announcement_item_lkp_uk1` (`announcement_id`,`item_id`),
     KEY `announcement_item_lkp_fk_1` (`announcement_id`),
     KEY `announcement_item_lkp_fk_2` (`item_id`),
     CONSTRAINT `announcement_item_lkp_fk_1` FOREIGN KEY (`announcement_id`) REFERENCES `announcements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `announcement_item_lkp_fk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

With the announcement_item_lkp table, you can associate as many items to your announcement as you like. And since you have cascading deletes, if an item gets deletes, its lookup records are deleted as well, so you don’t have to worry about orphaned references in your announcements, like you would it you just stuff a string of IDs somewhere.

You’re already using a relational database, let it do its job.