Last Updated:

Reference directory  | Examples | PHP

So, I decided to write an article on creating a link directory, because a lot of people want to get such scripts, and most of the ready-made ones are too "sophisticated". The directory is based on the well-known Yahoo

You need to first create 3 tables, in the 1st (category) the names of the categories will be stored, in the 2nd (sub_categories) there will be subcategories, and already in the 3rd (links) the links themselves. Create:

CREATE TABLE categories (
# unique identificator
id int(11) NOT NULL auto_increment,
# name of category
name varchar(255) NOT NULL default '',
PRIMARY KEY (id), UNIQUE KEY id (id, name)
)

CREATE TABLE sub_categories (
# unique subcategory id
id int(11) NOT NULL auto_increment,
# identifier of the category that this subcategory belongs to
cid int(11) NOT NULL default '0',
# subcategory name
name varchar(255) NOT NULL default '',
PRIMARY KEY (id), UNIQUE KEY id (id)
)

CREATE TABLE links (
# unique ident-r
id int(11) NOT NULL auto_increment,
# short name of the link
name varchar(100) NOT NULL default '',
# Internet address (http://vasya.pupkin.ru)
url varchar(255) NOT NULL default '',
# full description of the link
description text,
# subcategory id
sid int(11) NOT NULL default '0',
# counter hits links from your site
counter int(11) NOT NULL default '0',
PRIMARY KEY (id), UNIQUE KEY id (id)
)

All these tables can be created using the very convenient phpMyAdmin (http://phpwizard.net/phpMyAdmin) or the following script (install.php attached)

<?
include "config.php"; // here is the connection to the database
mysql_query("CREATE TABLE categories (id int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL default '', PRIMARY KEY (id),
UNIQUE KEY id (id,name))");
mysql_query("CREATE TABLE sub_categories (id int(11) NOT NULL auto_increment,
cid int(11) NOT NULL default '0', name varchar(255) NOT NULL default '',
PRIMARY KEY (id), UNIQUE KEY id (id))");
mysql_query("CREATE TABLE links (id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '', url varchar(255) NOT NULL default '',
description text, sid int(11) NOT NULL default '0',
counter int(11) NOT NULL default '0', PRIMARY KEY (id),
UNIQUE KEY id (id))");
?>

Scripts are created (index.php, add.php, config.php, go.php).

Let's look at index first.php

<?
include "config.php";
include "header.inc.php"; // page header

// if category (cid) or subcategory (sid) is not specified
// display a list of all main categories (main page)
if(!isset($cid) && !isset($sid)) {
// find out the number of categories
$cats = tablecount($t_category);

$i = 0;
// send a request to select categories from the database
$qc = mysql_query("select * from $t_category order by name");

print "<div align=center>\n";
print "<table width=600 cellspacing=2 cellpadding=2 border=0>\n";

// iterate through all categories sequentially
while($qcrow = mysql_fetch_array($qc)) {
// if the index of the category is even, then we start a new line in the table
if($i%2 == 0) { print "<tr>\n"; }

print "<td width=50%>\n";

// display a link to the list of subcategories in this category
print "<b><a href="index.php?cid=".$qcrow["id"]."">".
       $qcrow["name"]."</a></b><br>\n";

// here is a selection of subcategories for this category
// and limit ourselves to the first five (further there is a condition)
$qs = mysql_query("select * from $t_subcategory where cid='".$qcrow["id"]."'
      order by name");
$k = 0;
while($qsrow = mysql_fetch_array($qs)) {
// if this is the sixth one, display a link to the list of all subcategories
if($k == 5) {
print "<a href="index.php?cid=".$qcrow["id"]."">...</a>";
break;
}
print "<a href="index.php?sid=".$qsrow["id"]."">".$qsrow["name"].
      "</a>\n";
$k++;
}
print "</td>\n";
// if this is the last subcategory and their total number is odd, output
// empty table element
if($i+1 == $cats) { if($cats%2 != 0) { print "<td> </td>\n"; } }

// if odd, output the end of the string
if($i%2 != 0) { print "</tr>\n"; }
$i++;
}
print "</table>\n";
print "</div>\n";
} elseif(isset($cid)) {
// List all subcategories for the specified category $cid

// get the total number of subcategories for $cid, this function is in
// file config.php
$cats = tablecount($t_subcategory, "where cid=$cid");

// request to select a category from the table (to get its name)
$qn = mysql_query("select * from $t_category where id='$cid' order by name");
// get the first category (only one :))
$qnrow = mysql_fetch_array($qn);
// select _all_ subcategories for $cid
$qs = mysql_query("select * from $t_subcategory where cid='$cid' order by name");
$i = 0;

print "<div align=center>\n";
print "<b><a href="index.php">Home</a> > ".
       $qnrow["name"]."</b><br><br>\n";
print "<table width=600 cellspacing=2 cellpadding=2 border=0>\n";
// bypass all subcategories and display them in the browser (principle is the same as
// and for categories)
while($qsrow = mysql_fetch_array($qs)) {
if($i%2 == 0) { print "<tr>\n"; }
print "<td width=50%>\n";
print "<a href="index.php?sid=".$qsrow["id"]."">".$qsrow["name"].
      "</a>\n";
print "</td>\n";
if($i+1 == $cats) { if($cats%2 != 0) { print "<td> </td>\n"; } }
if($i%2 != 0) { print "</tr>\n"; }
$i++;
}
print "</table></div>\n";
} elseif(isset($sid)) {
// And here, in fact, we already display all the links

// get the subcategory $sid (to find out the name of the subcategory)
$qs = mysql_query("select * from $t_subcategory where id='$sid' order by name");
$qsrow = mysql_fetch_array($qs);
// and here we find out the name of the category
$qc = mysql_query("select * from $t_category where id='".$qsrow["cid"].
      "'order by name");
$qcrow = mysql_fetch_array($qc);

// Display the location and start of the table
print "<div align=center>\n";
print "<b><a href="index.php">Home</a> >
       <a href="index.php?cid=".$qcrow["id"]."">".$qcrow["name"].
      "</a> > ".$qsrow["name"]."</b><br><br>\n";
print "<table width=600 cellspacing=2 cellpadding=2 border=0>\n";

// query for all links for the given $sid subcategory, sorted by name
// you can of course output like the most popular
// (most clicks - order by counter)
$ql = mysql_query("select * from $t_links where sid='$sid' order by name");

// if the received number of links is 0, then report it
if(mysql_num_rows($ql) == 0) { print "<tr><td>No
links</td></tr>"; }
else {
// now iterate over all links one by one and display them.
while($qlrow = mysql_fetch_array($ql)) {
print "<tr>\n";
print "<td>\n";

print "Name: <b><a target=_blank href="go.php?id=".$qlrow["id"].
      "">".>$qlrow["name"]."</a></b><br><br>\n";
print "Description: ".$qlrow["description"]."<br><br>";
print "Clicks: ".$qlrow["counter"];
print "</td>\n";
print "</tr>\n";
}
}
print "</table>\n</div>\n";
}
include "footer.inc.php";
?>

add.php - a script for adding categories/subcategories (add.php?what=category) and the links themselves (add.php?what=link).

<?
include "config.php";
include "header.inc.php";

print "<div align=center>\n";
print "<table width=600 cellspaing=2 cellpadding=2 border=0>\n";

// the what variable stores the category/link value so we know what to add
if(@$what == "category") { //
// if the button "Add" (Submit) is not pressed, display the form for filling
if(!isset($submit)) {
?>
<form method=post action="add.php?what=category">
<tr><td width=50%>Select the category to add
    subcategory:<br>Or enter a new one</td>
<td width=50%>
<select class=select name=category size=1 style="width: 100%;">
<option selected value=#>Select a category</option>
<? // output all categories
$qc = mysql_query("select * from $t_category");
while($qcrow = mysql_fetch_array($qc)) {
print "<option value=".$qcrow["id"].">".$qcrow["name"]."</option>\n";
}
?>
</select>
<input type=text name=categorystr value="" style="width: 100%">
</td></tr>
<tr><td width=50%>Enter subcategory name</td>
<td width=50%>
<input type=text name=subcategory value="" style="width: 100%">
</td></tr>
<tr><td colspan=2><input type=submit name=submit
value="Add">
</td></tr>
</form>
<?
} else { // handle the "Add" button click
if($categorystr != "") {
// find out if this category is already in the table
$qcheck = mysql_query("select * from $t_category where name='$categorystr'");
if(mysql_num_rows($qcheck) == 0) { // no
// If the name of the new category is entered, then add it
$qi = mysql_query("insert into $t_category values('', '$categorystr')");
$category = mysql_insert_id(); // get the id for the new category
// display a message about what was added.
print "<tr><td>New category added
      (<i>$categorystr</i>)</td><tr>\n";
} else {
// if the category already exists, then find out
// its identifier and write it to $category
$qcrow = mysql_fetch_array($qcheck);
$category = $qcrow["id"];
}
if($subcategory != "") { // add a subcategory if one is entered
mysql_query("insert into $t_subcategory values('', '$category',
            '$subcategory')");
print "<tr><td>Subcategory <i>$subcategory</i>
added.</td></tr>";
}
}
} elseif(@$what == "link") {
// Adding a new link
if(!isset($submit)) { // if the Add button is not pressed, display the input form
?>
<form method=post action="add.php?what=link">
<tr><td width=50%>Add to:</td>
<td>
<?
print "<select name=category size=1 style="width: 100%;">";
print "<option selected value="#">Select a category</option>\n";

// Display all categories in the following format Cat1 -> subcat1, Cat1 ->
tackle2 well, etc.
$qc = mysql_query("select * from $t_category order by name");
while($qcrow = mysql_fetch_array($qc)) {
$qs = mysql_query("select * from $t_subcategory where cid = '".$qcrow["id"]."'
order by name");

while($qsrow = mysql_fetch_array($qs)) {
print "<option value=".$qsrow["id"].">".$qcrow["name"]." >
".$qsrow["name"]."</option>\n";
}
}
print "</select>\n";
?>
</td>
</tr>
<tr>
<td>Site name:</td>
<td><input type=text name=name value=""
style="width: 100%"></td>
</tr>
<tr>
<td>Site address (url):</td>
<td><input type=text name=url value=""
style="width: 100%"></td>
</tr>
<tr>
<td>Site Description:</td>
<td><textarea name=description rows=5
style="width: 100%"></textarea></td>
</tr>
<tr>
<td colspan=2><input type=submit name=submit
value="Add"></td>
</tr>
<?
} else { // handle clicking on the "Add" button
// Add a new link to the table
mysql_query("insert into $t_links values('', '$name', '$url', '$description',
            '$category', '0')");
print "<tr><td>Link added to directory.
<a href="index.php?sid=$category">Go to subcategory</a>
</td></tr>\n";
}
}
print "</table>\n";
print "</div>\n";

include "footer.inc.php";
?>

go.php - script to redirect the browser to the link and increase the counter (counter)

<?
include "config.php";

$error = 0; // set error flag to 0
// if the link identifier is not specified, then an error
if(!isset($id)) $error = 1;
else {
// select a link from the table (there should be only one, so
// limit the selection - limit 1)
$result = mysql_query("select * from $t_links where id = '$id' limit 1");

// if a link with this identifier is not found, then an error
if(mysql_num_rows($result) == 0) {
$error = 2;
} else {
// get a row from the table about the specified link
$row = mysql_fetch_array($result);
// increment the counter and write it back to the table (update)
$counter = $row["counter"];
$counter++;
$url = $row["url"];
$resultu = mysql_query("update $t_links set counter=$counter where id=$id");
// redirect to our found link
header("Location: $url");
}
}
// Handle errors and report them.
if($error != 0) {
print "<font size=+1><b>";
switch($error) {
case 1: print "You didn't provide an identifier"; break;
case 2: print "You have entered an invalid identifier"; break;
}
print "</font></b>";
}
?>

And in the config script.php there is a simple connection to the MySQL database and a function to get the total number of rows in the table:

<?
$database["name"] = "mysql";
$database["host"] = "localhost";
$database["database"] = "links";
$database["user"] = "root";
$database["password"] = "";

$t_category = "categories";
$t_subcategory = "sub_categories";
$t_links = "links";

$db = mysql_connect($database["host"], $database["user"],
$database["password"]);
mysql_select_db($database["database"], $db);

function tablecount($name, $cond = "") {
$q = mysql_query("select count(*) as total from $name $cond");
if(mysql_num_rows($q) == 0) return 0;

$qrow = mysql_fetch_array($q);
return $qrow["total"];
}
?>

It seems like everything, but here you can endlessly create something new, make sorting of the links displayed (by name, the number of clicks on them - the most popular) and much more.