Time for action - creating the MySQL database and tables

  1. Open phpMyAdmin and create a database sphinx_feeds. You can use an existing database as well.
  2. Import the following SQL to create the database tables:
    CREATE TABLE `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `feeds` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`last_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`guid` varchar(32) NOT NULL,
`link` varchar(255) NOT NULL,
`pub_date` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `guid` (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

What just happened?

We created a new database which will be used by our PHP application. We then created the following tables:

  • categories: This table will hold the category names. A category is a collection of items sharing common attributes. Each item in the feed has one or more category assigned to it. New categories will get added to this table as and when they are found in the feed items. The data stored in this table will be used to populate the select box which, will be used for filtering the search results by categories.
  • feeds: This table will store the feed URLs. While indexing, we will query this table to fetch all feed URLs whose data needs to be fetched and indexed.
  • items: This table will store the feed items, that is, title and link. We will display the item titles in the search results.

The following screenshots demonstrate how each of the tables will be created:

categories:

What just happened?

feeds:

What just happened?

items:

What just happened?

Basic setup

Lets create the directory that will hold our application files and other dependencies, such as the Sphinx API class and SimplePie feed parser. We will also be creating a few scripts that will act as helpers in our application and will contain some common code re-used throughout the application.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset