How to improve your website with Yahoo-like web directory
Create your own web directory engine and improve your website functionality

Copyright © Sergey Popov, xllentSOFT.com - August, 2003

Download sample sources here: webdir.zip

Who should use this tutorial?

Prerequisites

What is web directory?

Why web directory?

Description of the database structure

"MyCategories" table:
Field Name Type Description
iPKMyCategoryID INT Autoincremental field, unique category ID, primary key
iParentCategoryID INT ID of the parent category for subcategories.
It will be 0 for topmost categories.
sCategoryName VARCHAR Category name

"MyCategories" table creation query:

CREATE TABLE MyCategories
(
iPKMyCategoryID INT UNSIGNED NOT NULL AUTO_INCREMENT,
iParentCategoryID INT UNSIGNED NOT NULL,
sCategoryName VARCHAR(50) NOT NULL,
PRIMARY KEY(iPKMyCategoryID)
)

"MyLinks" table:
Field Name Type Description
iPKMyLinkID INT Autoincremental field, unique link ID, primary key
iCategoryID INT ID of the category
sLinkName VARCHAR Link name
sLinkURL VARCHAR URL
sLinkDescription VARCHAR Link description

"MyLinks" table creation query:

CREATE TABLE MyLinks
(
iPKMyLinkID INT UNSIGNED NOT NULL AUTO_INCREMENT,
iCategoryID INT UNSIGNED NOT NULL,
sLinkName VARCHAR(100) NOT NULL,
sLinkURL VARCHAR(255) NOT NULL,
sLinkDescription VARCHAR(255) NOT NULL,
PRIMARY KEY(iPKMyLinkID)
)

You can execute above SQL queries through your favorite MySQL client manually or run the "createMyTables.php" script.

The "createMyTables.php" script includes the "dbConfig.inc.php" file, which contains definitions for the following MySQL server connection parameters:

• $sMySQLHost - MySQL server host address
• $sMySQLUser - MySQL user name
• $sMySQLPassword - MySQL password
• $sMySQLDatabase - MySQL database name
  
  These parameters MUST be defined before the "createMyTables.php" script execution. You need to replace these values with your own MySQL server parameters values.

The "createMyTables.php" script uses simple algorithm:

• Establish MySQL server connection
• Set active MySQL database
• Execute the "MyCategories" table creation query and process possible errors
• Execute the "MyLinks" table creation query and process possible errors
• Close MySQL server connection
  
  This database structure will allow us to run simple web directory engine.

Simple web directory engine scripts

• dbConfig.inc.php - contains definitions for the MySQL server connection parameters (see description above)
• dbUtils.inc.php - auxiliary include file that contains definition of the "GetCategoriesPath" utility function
• viewWebDirectory.php - page that displays web directory categories and links
• editCategories.php - page that allows new categories and links addition
• processData.php - page that executes category or link insertion query and processes errors.
  
  All these scripts are quite simple, clear and fully commented, so you will be able to catch the idea easily. Now we will review these scripts more closely.

dbUtils.inc.php auxiliary include file

The second parameter is a name of the page, which used for the correct link creation.

The "GetCategoriesPath()" function uses active MySQL connection. Please keep this in mind if you want to use it for your own purposes.

We use the following SQL query to obtain name of the specified category and parent category ID:

SELECT iParentCategoryID, sCategoryName
FROM MyCategories
WHERE iPKMyCategoryID=[Current Category ID]

This query executed each time with new [Current Category ID] value. The ID of the parent of the current category used as [Current Category ID] for the next iteration. Function finishes execution and returns result when parent category ID is equal to 0.

viewWebDirectory.php script

Page layout:

• Current category path - it created with the "GetCategoriesPath()" function and placed in the header of the page.
• Subcategories list for the current category - bulleted list.
• Links list for the current category - bulleted list.
  
  The logic of this script is quite simple. We use these queries to obtain subcategories and links lists:

Categories List

SELECT iPKMyCategoryID, sCategoryName
FROM MyCategories
WHERE iParentCategoryID=[Current Category ID]

Links List

SELECT sLinkName, sLinkURL, sLinkDescription
FROM MyLinks
WHERE iCategoryID=[Current Category ID]

The "mysql_query()" PHP function sends these queries to the currently active database on the MySQL server.

We use "while" PHP loop to transform each row of results of these queries to separate HTML tag. The "mysql_fetch_assoc()" PHP function used to fetch a result row as an associative array.

editCategories.php script

Page layout:

• Current category path - it was created with the "GetCategoriesPath()" function and placed in the header of the page.
• Form containing drop-down list populated with subcategories of the current category. It placed here to simplify web directory navigation for the user.
• New category entering form.
• New link entering form.
  
  First of all, we execute two simple queries to determine subcategories and links count for the specified category:

Subcategories count

SELECT COUNT(*)
FROM MyCategories
WHERE iParentCategoryID=[Current Category ID]

Links count

SELECT COUNT(*)
FROM MyLinks
WHERE iCategoryID=[Current Category ID]

Than we populate subcategories drop-down list. We use the same technique as in the "viewWebDirectory.php" script. The drop-down list is created using <SELECT> HTML element. Selected category ID is submitted to the same "editCategories.php" script using the GET method. So the user can navigate through the web directory categories staying in the editing mode.

Two simple HTML forms are placed below on the page:

"frmCatAdd" form for entering new category name
"frmLinkAdd" form for entering new link name, URL and description
Both forms use the POST method to submit data to the server and contain two hidden parameters: "sDataType" and "iParentID". The "sDataType" parameter specifies the table to which new record should be added. The "iParentID" parameter specifies the ID of the category for which new link or subcategory should be created. The <INPUT TYPE="HIDDEN"> HTML element is used to create hidden parameters.

processData.php script

The "switch" PHP statement is used to determine which data was passed. The "mysql_query()" PHP function is used to send INSERT query to the MySQL server.

How to use samples

Unpack webdir.zip into a single folder on your web server.
Modify database parameters in the "dbConfig.inc.php" for your specific MySQL Server.
Create "MyCategories" and "MyLinks" database tables. You can do it manually or use the "createMyTables.php" script.
Open the "viewWebDirectory.php" page in your browser.
How to improve web directory functionality
Proposed web directory engine is quite simple, but you can improve it by adding some useful functionality. Here are some ideas:

Add attractive web design. Web pages in my sample are very simple, so you can improve them easily.
Modify "editCategories.php" script to allow categories and links editing. Add user input verification.
Add password protected administrative page that will allow approving user links, adding new links or removing dead links.
Show related categories on the page.
Add link ranking
Add web directory search. This feature will require storing keywords for each link.
You can choose your own way how to build unique web directory.

Last note

Feel free to send to us your comments or questions: feedback@xllentsoft.com