advertisement

Alright guys, I know it’s been a while since my last PHP tutorial, so I thought you deserved a good one.

In this tutorial I will be teaching you how to create your very own topsite script from scratch, you can use this script to start your own topsite website where users can submit there own websites to your list, and users from there website can vote for there site.

Buckle up, this could be a long one!

Ok, first things first, our script is going to need to access some data from a database, create a new database, call it whatever you want. Make sure you remember the info for your database (password, username and database name) so that we can use it later on in our script.

Now that you have your database created, let’s create some tables in our database, we need to store our data somewhere! If all this database stuff seems a bit confusing to you, you might want to check out our beginners guide to MySQL tutorial.

We are going to only use 1 table in this tutorial, this will store all of our websites in it, the websites id (we give that to them), name, URL, description, votes in, hits out and password.
[mysql]
CREATE TABLE websites (
id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
name VARCHAR(40) NOT NULL,
url VARCHAR(60) NOT NULL,
description VARCHAR(255) NOT NULL,
votes INT(11) UNSIGNED NOT NULL DEFAULT 0,
hits INT(11 UNSIGNED NOT NULL DEFAULT 0,
password CHAR(40) NOT NULL,
PRIMARY KEY(id)
);
[/mysql]
Run that query, that will create a new table named websites, this will store all the data for all of our websites. This script will be pretty basic, I am going to be making a part 2 which expands on this script and adds an admin section, and a place for users to edit there previous listings and we will add more features.

Ok, let’s get started with our PHP. First we are going to setup a file so that we can connect to our database.

<?php
DEFINE ('DB_USER', 'db_user'); // Insert your database username Here
DEFINE ('DB_PASSWORD', 'db_password'); // Insert your database password here
DEFINE ('DB_HOST', 'localhost'); // Insert your host, this is 99% of the time kept as 'localhost'
DEFINE ('DB_NAME', 'db_name'); // The actual name of your database here

$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error());

@mysql_select_db (DB_NAME) OR die('Could not select the database: ' . mysql_error() );	
?>

I call this file mysql_connect.php, and that is how I refer to it in all of my other files. In pretty much all of my tutorials, I use this (if we are connecting to a database). You can see the lines that I commented, just change the information there, and you are done. This just connects to our database, I put it in a separate file so that all I have to do is include it in any file that needs to connect to the database.

Alright, so for people to be able to use this script, they are going to need a place to add there websites. This is add-site.php –

<?php
// If our form has NOT been submitted....
if (!isset($_POST['submitted'])) {
?>

	<h3>Add Your Site</h3>
	<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
	
	<p><b>Name :</b> <input type="text" name="name" size="25" maxlength="40" value="<?php if (isset($_POST['name'])) echo $_POST['name']; ?>" /></p>
	
	<p><b>Site URL :</b> <input type="text" name="url" size="25" maxlength="60" value="<?php if (isset($_POST['url'])) echo $_POST['url']; ?>" /></p>
	
	<p><b>Description :</b><br />
	<textarea name="description" rows="5" cols="40"><?php if(isset($_POST['description'])) echo $_POST['description']; ?></textarea></p>

	
	<p><div align="center"><input type="submit" name="submit" value="Add Site" /></div></p>
	
	<input type="hidden" name="submitted" value="TRUE" />
	
	</form>

This is actually more HTML then PHP, anyways, if our form hasn’t been submitted, then obviously we want to show our form. As you can see when I am done with my PHP at the top, I get out of PHP mode, because since I will be adding so much HTML it just makes life easier. This form just has 3 form fields for name, site URL and the sites description. Then a submit button. Now we need to get onto the verifying part of our script.

<?php
}
else {
// If the form HAS been submitted, continue.
	
	// Connect to your database
	require_once('mysql_connect.php');

This is the continuation of our if statement we started before, as you can see I go back into PHP mode, make our else statement and then since we will be interacting with the database, I require our database connection file that we made before.

	if (empty($_POST['name'])) {
		echo '<font color="red">Please enter in a name for your site.</font><br />';
		$n = FALSE;
	} else {
		$name = $_POST['name'];
		$query = "SELECT id, name FROM websites WHERE name = '$name'";
		$result = mysql_query($query) OR die($mysql_error());
		$num = mysql_num_rows($result);
		
		if ($num > 0) {
			$n = FALSE;
			echo '<font color="red">This name has already been taken</font><br />';
		} else {
			$n = mysql_real_escape_string($_POST['name']);
		}
	}

This is our name verifier. The first if statement that you see is if the form field name was left empty, then we throw an error at the user, and we set $n to false. Later on in our script, we will be checking all our of forms variables to see if there were any errors.

If it was not set to empty, that means something must have obviously been inputted into there, but we also don’t want 2 sites to have the same name either, so we do a small query that selects the id and name from our websites table.

If any rows are returned (if anything was found) that means the site has already been submitted, and an error is chucked at the user again, we use mysql_num_rows($result) to check how many rows were selected, we can then use this in an if statement to see that if it equals anything above 0, that means something was selected and we start our error.

If not, then we set our $n variable, which then means that everything was OK for our name field. Now we verify our URL –

	if (empty($_POST['url'])) {
		echo '<font color="red">Please enter in a URL for your site.</font><br />';
		$u = FALSE;
	} else {
		$u = mysql_real_escape_string($_POST['url']);
	}

This is just very basic URL verification for this script, and probably isn’t the best way to do it either, but it will work for now. In part 2 of this script, everything will be modified and made better. Anyways, this just checks to see that if the field was empty, give an error, if not then set our variable and everything is OK. Now we check our last field, our description –

	if (empty($_POST['description'])) {
		echo '<font color="red">Please enter in a description for your site.</font><br />';
		$d = FALSE;
	} else {
		$d = mysql_real_escape_string($_POST['description']);
	}

This is the exact same as verifying our URL. Just check if it’s empty, if not then set the variable and everything is OK.

Now we need to see if there were any errors while verifying our form –

	if ($n && $u && $d) {
		$query = "INSERT INTO websites (name, url, description, password) VALUES ('$n', '$u', '$d', SHA('$p'))";
		$result = mysql_query($query) OR die(mysql_error());

If all of our variables contain something, and are not set to FALSE, then we can continue, we create our query then execute it using mysql_query. If the query fails, we output an error.

		if ($result) {
			echo '<h3>Site Added!</h3>
			<p>Your site has been added into your database, you may now take the code below and add it to your site so people can start voting!</p>';
			
			$query = "SELECT id, name FROM websites WHERE name = '$n'";
			$result = mysql_query($query) OR die(mysql_error());
			$row = mysql_fetch_array($result, MYSQL_ASSOC);
?>

If our $result works, and everything is fine, we output a message saying that the site has been added, and now we need to give them the code that allows people to vote from there website. So that also means we need to get the ID of the site they just added, so we make a query saying to select the id and name from our websites table, where name is equal to $n, and $n is the name of the site just added. We run our query, and then fetch an array of the fetched sites. Now since we will be using quite a bit of HTML, it’s more efficient to just escape out of PHP, and do our straight HTML.

<div align="center">
<textarea name="code" cols="20" rows="2"><a href="http://www.tutorialcode.com/topsite/vote.php?id=<?php echo $row['id']; ?>">Click Here to Vote For Us</a></textarea>
</div>

This will make a text area, that contains some code for users to add to there site, the code will just be a simple HTML link, you can change this so the link is an image not just a text link. It will create a link to our vote page and use the ID that we just fetched.

<?php
		
		} else {
			echo 'There was an error inserting your site into our database.';
		}
	} else {
		echo '<font color="red">You didn\'t enter in all of your data proerply!';
	}
}
?>

Now we get back into PHP for the last part of this file, we finish off our if statements for our the if statement to see if our query worked, our if statement if everything was entered properly or not, if one of the variables wasn’t set (there was an error) then an error message is output. And then we close our main if statement with a final bracket, and we are done with this portion of the script! Now let’s get onto actually displaying our sites.

This is actually quite easy, it’s a lot less code then our add-site.php file that we just made as well :). So let’s get to it –

<?php
// Connect to our database
require_once('mysql_connect.php');

// Select sites from the database - limit to the top 50 sites
$query = "SELECT id, name, description, votes, hits FROM websites ORDER BY votes DESC LIMIT 50";
$result = mysql_query($query) OR die(mysql_error());

Since we will be querying the database, (to retrieve our sites) we will need to include our database connection file that we made at the beginning of this tutorial. Now we select our sites from our database, I use an ORDER BY clause so that it orders all of our results by votes, so the more votes a site has, the higher up on the list the site is! (The whole point of a topsite :P). I also limit it to the top 50 sites, to keep the spam down. Then I just execute the query, you might notice I add OR die(mysql_error()); after every single query that I do. This is saying that, either execute our query OR (if that doesn’t work) die. die is just a function which stops the script, and then we use mysql_error() to output the mysql error that must have occured for the query not to work.

echo '<table border="0" cellpadding="7" cellspacing="7">
	<tr>
		<td><strong>Rank</strong></td>
		<td><strong>Name/Description</td>
		<td><strong>Votes In</td>
		<td><strong>Hits Out</td>
	</tr>
	';

Now we start our table, I take away the borders, and give our table some padding and spacing. I create the headers for each column, just basic table HTML, there will be 4 columns, 1 for the sites rank, another for the name and description of the site, one for how many votes the site has gotten and a last one for how many hits we have sent to the site.

// Start the rank counter
$rank = 1;

Now, we aren’t going to use MySQL to determine the rank of a site, we are going to do this manually. Here is where we start our counter. Obviously the first site listed, will be ranked 1. In the next part of the code, we will be using a while loop to display our results, as well as increment our $rank variable by 1 each time so that each site has a new rank.

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	echo '<tr>
		<td>'.$rank.'</td>
		<td><a href="out.php?id='.$row['id'].'" target="_blank">'.$row['name'].'</a><br /><small>'.
		$row['description'].'</small></td>
		<td>'.$row['votes'].'</td>
		<td>'.$row['hits'].'</td>
	</tr>
	';
	$rank++;
}
?>

Now we start a while loop, the most common way of extracting data from a query. We input all of our data that we just extracted into an array using the mysql_fetch_array() function, now we just echo out each row of our table. Using a while function is great, because it will run until there are no more results from the query you executed. So in our query, we set a limit of 50 sites to be extracted, (there doesn’t have to be 50 sites in the database) so this will run a maximum of 50 times depending on how many sites you have extracted. If only 5 sites are extracted from your database, then the while loop will only run 5 times.

We output a new row in our table for each results that is extracted, outputting the right data for each, then we increase our rank counter by 1 by using $rank++, the ++ on the end is what adds one to the variable. Then we close our PHP and we are done with displaying our site!

Only 2 more files left, hold in there! When your done you will have an awesome starting Topsite script that you can use, and also learn from. All we have to do now is make a file that counts the amount of hits we have sent out, and another file for people to vote from. Let’s do our file for…. the amounts of hits we send out (it’s easier :)).

// Check if the ID was submitted, AND if it is numeric (a number)
if (isset($_GET['id']) && is_numeric($_GET['id'])) {

	// Connect to our database
	require_once('mysql_connect.php');

In our index.php file that we just created in the last part of this tutorial, when we output our sites, we make the name of the site into a link, except it is not a direct link to the site. The site links to this file that we are making now, so in other words it links to out.php?id=IDHERE. Obviously the part that says IDHERE, is a number, and it’s the ID of the site. This is how we track how many hits that send out to a specific site. So the first site you add, will have an ID of 1, and then the link will be out.php?id=1.

This part of our out.php file, checks to see if an id was even submitted, and then we make sure that it is an actual number – this is where we use is_numeric(), this is how we check to see that our ID is an actual number.

Then since we will be using our database, again. We include our database connection file in our script.

	$id = mysql_real_escape_string($_GET['id']);

We set our ID to a variable named $id, for easier use later on.

	// Add 1 to the sites hits
	$query = "UPDATE websites SET hits = hits + 1 WHERE id = $id";
	$result = mysql_query($query) OR die(mysql_error());

This query updates our websites table, it adds 1 to our hits column where the sites id is equal to $id (which we set our variable just before). We run our query, and our site is updated.

	unset($query);
	unset($result);

Now, we will be re-using the same variables, so it is good programming practice to erase them in a way. Using the unset() variable will unset our variable.

	$query = "SELECT id, url FROM websites WHERE id = $id";
	$result = mysql_query($query) OR die(mysql_error());
	
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$num = mysql_num_rows($result);
	$url = $row['url'];

We use a query that selects our site from our database, we need to do this so we can get the URL of the site. We execute our query, then fetch the results into an array, we set our $url variable to the variable that we extracted from our database.

*Updated Version V1.1* – I have added a new variable, and am now using a new function. As pointed out below in the comments, there was a security issue with the script, it has now been fixed though :). I make a $num variable, and assign the output of mysql_num_rows() to it. This function counts how many rows were selected from the database using our query.

	if ($num > 0) {
		header ("Refresh: 0; url=$url");
	} else {
		echo '<font color="red">This site does not exist.</font>';
	}
} else {
	echo '<font color="red">You need to select a site to visit!</font>';
}
?>

Then we finished off our script, the header() function is used for many things, we use it here to re-direct the user to the site they clicked on, that is why we needed to extract the URL from the database. Then we just finished of our starting if statement and we are done with this file!

*Updated Version V1.1* – Using our $num variable above, I make sure that if at least 1 result is selected from the database, then we can continue. If no results are selected then we output an error and we do not re-direct the user anywhere. The problem with the first version of the script was that if they input an ID that did not exist, the site would just be reloading an empty variable, because the site doesn’t exist! This could cause extreme stress on your server and cause your database to shutdown if the user knew what they are doing.

Last file, our voting one where it counts the votes. This one might get a bit tricky, so buckle up!

<?php
// If the user hasn't said they are sure they want to vote.
if (!isset($_POST['submitted'])) {

	// Check if the ID was submitted, AND if it is numeric (a number)
	if (isset($_GET['id']) && is_numeric($_GET['id'])) {

Alright, we are going to be making a file, so when a user clicks on the vote link from another persons website, they are brought here. From here there is a button asking them if they are sure they want to vote for “site name”. Where “site name” equals the site they clicked to vote from.

If they haven’t clicked on any buttons yet, then our form (for our button) hasn’t be submitted so we need to display the form. First we check to see if an ID was even submitted, and also if the ID is a number using is_numeric() again.

		// Connect to our database
		require_once('mysql_connect.php');
		
		$id = mysql_real_escape_string($_GET['id']);
		
		$query = "SELECT id, name FROM websites WHERE id = $id";
		$result = mysql_query($query) OR die (mysql_error());
		$row = mysql_fetch_array($result, MYSQL_ASSOC);
?>

This all too familiar piece of code, we include our database connection file, set our $id variable, run a query, then fetch the results into an array. We have already done this countless times in this tutorial :). I exit out of PHP because I want to do quite a bit of HTML, this just makes it easier.

		<strong>If you are sure you wish to vote for <?php echo $row['name']; ?> click the button below.</strong><br />
		
		<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
		<input type="submit" name="vote" value="Vote For <?php echo $row['name']; ?>" />
		<input type="hidden" name="submitted" value="TRUE" />
		<input type="hidden" name="id" value="<?php echo $row['id']; ?>" />
		</form>

A message is sent to the user, it asks if they are sure they want to vote for “site name”. This is where the actual sites name is put in. Then we create a form, all the form has is 1 button, it says “Vote for ‘site name'”. Again, site name is replaced by the actual sites name. We also have a hidden field, with the sites ID so we can pass it onto this same file again, except for this time instead of displaying the form, it will do our vote verification because our form WILL have been submitted.

<?php
	} else {
		echo '<font color="red">You need to select a site to vote for!</font>';
	}
} else {
	// Connect to our database
	require_once('mysql_connect.php');
		
	$id = mysql_real_escape_string($_POST['id']);

These 2 else statements just finish off our if statement that checks to see if an ID is submitted, and then the second else statement is for our first if statement. It is also where we start our voting process.

	// Select the site we want to add 1 vote to
	$query = "SELECT id, votes FROM websites WHERE id = $id";
	$result = mysql_query($query) OR die(mysql_error());
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	
	$votes = $row['votes'];
	$url = $row['url'];
	$id = $row['id'];

We need to select the site they are trying to vote for from the database. We do this, extract our results using mysql_fetch_array() and put them into an array, and set all of the info to variables.

	$cookie_name = 'vote_'.$id;

Now, we don’t want users to vote more then once every 24 hours. So we create a variable, we use this variable after to check it to see if a cookie is even submitted. This will become clear in the next part of code.

	if (!isset($_COOKIE['vote_'.$id])) {
		// Add 1 to the previous hit amount
		$votes++;

If our cookie is NOT submitted, then we can continue. We check to see if the specific cookie for this 1 site is submitted. That is why we add the $id to the end, so that a user can vote for different sites in 24 hours, just not the same one.

Then we add one to our votes variable that we extracted from our database before.

		// Update our websites hit amount
		$query = "UPDATE websites SET votes = $votes WHERE id = $id";
		$result = mysql_query($query) OR die(mysql_error());

Then we update our database, we set our votes to the new $votes variable, that we just added 1 onto.

We execute our query, and then that means this site has just gotten 1 vote!

		setcookie ($cookie_name, 1, time()+86400, '/topsite/', '', 0);

This is where we set our cookie, we name the cookie using our $cookie_name variable that we set just before, we need to set the time of the cookie so it knows when to expire, so we take our time right now, (using time()) and add +86400 seconds onto it. This is equal to exactly 24 hours.

		echo 'Your vote has been cast! <a href="/topsite/">Click here to go back to our homepage.</a>';
	} else {
		echo 'Sorry, you have already voted for this site in the past 24 hours.';
	}
}
?>

Then we finish all of our else statements, and our total script is completely finished! Phew, that was a long one. I hope you stuck with me through it all and learned a thing or 2! Keep looking back every so often, I will try and create a part 2 for this tutorial, it will include things like –

  • Better cheating prevention
  • More advanced site verification methods – using regular expressions.
  • A user login area – users can login and edit there sites info.
  • More features

Tutorial Example
Check out this tutorials full working example
Main Page – Click Here
Add Your Site! – Click Here

That’s all the links you need, the rest you can see from inside the actual script.

Download this topsite scripts full source code – Last Update : April 08, 2007
Topsite Script V1.1 – Full Source Code

If you have any questions feel free to send us an email using the contact form located on the site, or just leave a comment just below!

Special Thanks to sunjester for pointing out some security flaws!

Thanks!
Sean

*Note* – I have noticed that people are adding there sites to the toplist, this was obviously going to happen but it is also perfectly fine as well! I invite all sites to add there site to my script. If you do add your site just leave a little comment to show some appreciation maybe! Thanks!

advertisement