Jump to content

Methods for conting downloads

Xable
 Share

Recommended Posts

dman

dman

Posted
Posted (edited)

OK caveman, we just about got the basics in place. Here is index.php with all links and in color. Also download.php. You need to change the “file” column in your stats db to “fk_file” and make it an integer field. We use this to relate our stats table to main table in order to derive total bandwidth for downloads (size * number).

Also be a good Idea to save the CSS as an external stylesheet so browser only has to download it once.

index.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<head>
	<title>Freedll.co.uk</title>
	<style type="text/css">
<!--
a:link {
	color: #FFFFFF;
	text-decoration: none;
}
a:visited {
	text-decoration: none;
	color: #FFFFFF;
}
a:hover {
	text-decoration: none;
	color: #0066FF;
}
a:active {
	text-decoration: none;
	color: #000kk0;
}

body {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	color: #FFFFFF;
	text-align: left;
	padding-left: 10px;
	align: center;
	font-size: .9em;
}

span.curpage {
	color: black;
}
span.whitetext {
	color: white;
}
div.alphamenu {
	text-align: center;
	align: center;
	background-color: #ffcf31;
	padding-bottom: 3px;

}
div.alphamenu table {
	text-align: center;
	margin: 0 auto;
	border-collapse: collapse;
	font-weight: bold;

}

div.pagemenu {
	align: center;
	text-align: center;
	color: #1234567;
}
div.pagemenu table {
  margin: 0 auto;
	border-collapse: collapse;
	align: center;
	width: 40%;
	font-weight: bold;
}
div.pagemenu td {
	text-align: center;
	padding-left: 10px;
	padding-right: 10px;
	font-size: .8em;
	padding-bottom: 3px;
	padding-top:3px;
	background-color: #ffcf31;
	color: black
}
div.dlllist {
	align: center;
	text-align: center;
}

div.dlllist table  {
  margin: 0 auto;
	border-collapse: collapse;
	width: 40%;
	align: center;
	padding-left: 10px;
 }

div.dlllist tr {
           color: white;

 }

 div.dlllist tr.header {
           color: white;
	height: 25px;
	font-size: 1.1em;
 }
div.dlllist th {
	color: white;
	background-color: #00cf31;
	font-size: .9em;
	width: 33%;
 }

div.dlllist td {
	color: white;
	font-size: .8em;
	padding-left: 10px;
 }
 div.dlllist td.col1 {
	text-align: left;
	width: 33%;
 }
 div.dlllist td.col2 {
	text-align: center;
	width: 33%;
 }
 div.dlllist td.col3 {
	text-align: center;
	width: 33%;
 }
 div.dlllist td.header {
	text-align: center;
	width: 100%;
 }

 -->
</style>
</head>
<body>
<?php
// this value sets the number of rows we want to show on each page
$pagesize=30;

// this is the current page name. This lets us name our page anything we want and it will still work
$self = basename($_SERVER['PHP_SELF']);

// next line checks if a "get" parameter was passed to the script. If it was, we set a variable named $folder to the value
// of the dlls folder, and $pageindex to our starting point
// If not it means someone has just entered the site and we set the page to "A-1" 
if (isset($_GET['folder'])) 
{
	$folder=$_GET['folder'];
	if (strlen($folder) > 1)
	{
  echo "<span class=\"curpage\">Invalid Submission</span>";
  die ('Invalid Submission');
	}

	$pageindex=$_GET['pidx'];
	if($pageindex < "0" or $pageindex > "10000")
        {
  echo "<span class=\"curpage\">Invalid Submission</span>";
  die ('Invalid Submission');
	}
} else {
	$folder="A";
	$pageindex=0;
}

// Create letter links
// we are going to build a string that consists of the HTML needed to make our alphabet menu, then we are going
// to output it to the HTTP stream using the PHP "echo" command
$alphamenu="<div class=\"alphamenu\"><table>\n<tr>\n";

// this loop will repeat 26 times, one for each letter of the alphabet. each time through we use the chr() function
// to create a letter, starting with chr(65), which is the ASCII code for capital "A". 
// The ".=" means append the string to the current string, $i++ tells the $i variable to increment once each loop
// the "\t" and "\n" in the strings are not actually part of the code, they are used
// to add a tab or newline to the output so the HTML output code looks pretty.
for ($i = 1; $i <= 26; $i++) 
{
	$letter=chr($i+64);
	if ($letter == $folder)
	{
  $alphamenu .= "\t<td><span class=\"curpage\">" . $letter . "</span> | </td>\n";
	} else {
  $alphamenu .= "\t<td><a href=\"" . $self . "?folder=" . $letter . "&pidx=0\">" . $letter . "</a> | </td>\n";
	}

}
// lastly we add the link for 0-9
$alphamenu .= "\t<td><a href=\"" . $self . "?folder=0&pidx=0\">0-9</a></td>\n";
$alphamenu .= "</tr>\n</table>\n</div><br />";

// and finally write the string to the HTTP stream
echo $alphamenu;

// now we make a connection to our MySQL database server
$link = mysql_connect('localhost', 'root', '')
   or die('Could not connect: ' . mysql_error());

// and open our database
mysql_select_db('t2') or die('Could not select database');

// now we see if dll starts with a letter or 0-9 and set our SQL query to match. 
// We have set the parameter for 0-9 to "0" in our menu link
// the query will give us a count of how many rows match the letter or 0-9 we use this to determine how
// many pages worth there are to display
if ($folder=="0") {
	$whereclause = " LEFT(file,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')";
} else {
	$whereclause = " LEFT(file,1)='" . $folder . "'";
}
// and now we set up our query with the proper where clause inserted
$query = "SELECT count(*) FROM dlls WHERE " . $whereclause;

// now we run the query on the database
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// get the count from the recordset for the total number of rows
$num=mysql_result($result,0);

// now we divide the number of rows by our page size and round it up with the ceiling function to get number of pages
$numpages=ceil($num / $pagesize);

// here we start to build our output HTML string for our page menu
// we loop here once for each page. each time through the loop we add the first letter
// of the file name plus the page number  to our menu.
// the first line tells us not to create a page menu if there is only one page
$pagemenu="<div class=\"pagemenu\"><table>\n<tr>\n";
if ($num > $pagesize)
{
	if ($pageindex==0)
	{
  $pagemenu .= "\t<td colspan=\"3\" class=\"header\">\n\t\t" . $folder . "1 <span class=\"whitetext\">-</span> \n";
	} else {
  $pagemenu .= "\t<td colspan=\"3\" class=\"header\">\n\t\t<a href=\"" . $self . "?folder=" . $folder . "&pidx=0" . "\">" . $folder . "1</a> <span class=\"whitetext\">-</span> \n";
	}	
	for ($i = 1; $i < $numpages; $i++) 
	{
  $pos=($i*$pagesize);
  $lc=$i+1;
  $menucap = $folder . $lc;
  if ($i * $pagesize == $pageindex)
  {
 	 $pagemenu .= "\t\t" . $folder . $lc;	
  } else { 
 	 $pagemenu .= "\t\t<a href=\"" . $self . "?folder=" . $folder . "&pidx=" . $pos . "\">" . $menucap . "</a>";
  }	
  if ($i < $numpages-1)
  {
     $pagemenu .= " <span class=\"whitetext\">-</span> \n";
  }
  if ($pos>=$num)
  {
 	 break;
  }	
	}
} else {
	$pagemenu .= "<td colspan=\"3\" class=\"header\">" . ($folder=="0" ? "0-9" : $folder . "1");
}

// finish the page menu html
$pagemenu .= "\n\t</td>\n</tr>\n</table></div>";

// write the page menu to HTTP stream
echo $pagemenu;

// release the result set so we can use it for next query
mysql_free_result($result);

// now we set up the main query to retrieve our page worth of dlls
$query = "SELECT * FROM dlls WHERE " . $whereclause . " ORDER BY file LIMIT " . $pageindex . "," . $pagesize;

// now we run the main query on the database
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// and get the count
$num=mysql_numrows($result);

// create our main output string for the list table
$i=0;
$htmlout = "<div class=\"dlllist\">\n<table>\n";
$htmlout .= "<tr class=\"header\">\n\t<th>File Name</th><th>Version</th><th>Size in kb</th>\n</tr>\n";
while ($i < $num) {
	$file=trim(htmlspecialchars(mysql_result($result,$i,"file")));
	$version=trim(mysql_result($result,$i,"version"));
	$size=trim(mysql_result($result,$i,"size"));
	$dl="download.php?pk=" . mysql_result($result,$i,"pk");
	$htmlout .= "<tr style=\"cursor: pointer;\" onclick=\"document.location.href='" . $dl . "'\" onmouseover=\"this.bgColor='#00CCFF';\" onmouseout=\"this.bgColor='#0099FF';\" bgcolor=\"#0099ff\">\n";
	$htmlout .= "\t<td class=\"col1\">$file</td>\n";
	$htmlout .= "\t<td class=\"col2\">$version</td>\n";
	$htmlout .= "\t<td class=\"col3\">$size</td>\n";
	$htmlout .= "</tr>\n";
    $i++;}
$htmlout .= "</table>\n</div>\n";

// write the table to HTTP stream
echo $htmlout;

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);

// write the menus at the bottom of the page
echo $pagemenu;
echo "<br />";
echo $alphamenu;
?> 
</body>
</html>

download.php

<?php

$MYSQL_USER = "root";             //The username used to connect to MySQL
$MYSQL_PASS = "";         //The MySQL Password for the user
$MYSQL_HOST = "localhost";        //The host to connect to
$MYSQL_DB   = "t2";             //The database in which the dl_count table is in

$link = @mysql_connect($MYSQL_HOST, $MYSQL_USER, $MYSQL_PASS);
@mysql_select_db($MYSQL_DB, $link);

if(isset($_GET['pk'])) {
	$pk = $_GET['pk'];
    // since we are using primary key to pass parameters makes very easy to validate
	// if the submission is not a number between 0 and 10000 we know its a bad query... very secure
	if($pk < "0" or $pk > "10000") {
  echo "Invalid Submission";
  exit;
	}
	$query = "SELECT file FROM dlls WHERE pk = " . $pk;
	$result = mysql_query($query) or die('Query failed: ' . mysql_error());
	$num=mysql_numrows($result);
	$file=mysql_result($result,0,"file");
	$test = substr($file,0,1);
	if ($test >="0" and $test <="9")
	{
  $test="0";
	}
	$FILES_DIR = "dlls/" . $test . "/";
	mysql_free_result($result);

	$query = "SELECT * FROM dl_count WHERE fk_file = $pk";
	$result = mysql_query($query, $link);
	if(!$result) {
  echo mysql_error();
  exit;
	}
	if(mysql_num_rows($result) == 0) {
  //first use of this file
  $query = "INSERT INTO dl_count (fk_file, count) VALUES($pk, 1)";
  $result = mysql_query($query, $link);
	} else {
  $query = "UPDATE dl_count SET count = count + 1 WHERE fk_file = $pk";
  $result = mysql_query($query);
	}
	//mysql_free_result($result);
	header("Location: " . $FILES_DIR . $file);
}
// Closing connection
mysql_close($link);

?>

Also you will want to add an index to the dlls table of your db. Indexs really speed up SQL querys if you put them on the right column. Since we will be doing our selects by file name we put an index on the "file" table column.

ALTER TABLE `dlls` ADD INDEX `file` ( `file` )

And the statistics view page:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<head>
	<title>FreeDLL File Download Statistics</title>
</head>
<body>
<?php
// Connecting, selecting database
$link = mysql_connect('localhost', 'root', '')
  or die('Could not connect: ' . mysql_error());

mysql_select_db('t2') or die('Could not select database');

//SQL query for totals
$query = 'SELECT sum(count) as tot, sum(count*size) as tbw FROM dlls, dl_count WHERE dlls.pk = dl_count.fk_file ORDER BY count DESC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num=mysql_numrows($result);
$tot = "Total Downloads = " . trim(mysql_result($result,0,"tot"));
$tbw = "Total Bandwidth = " . trim(mysql_result($result,0,"tbw") . "kb");
// Free resultset
mysql_free_result($result);

// Performing SQL query
$query = 'SELECT file, count, (count*size) AS bw FROM dlls, dl_count WHERE dlls.pk = dl_count.fk_file ORDER BY count DESC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num=mysql_numrows($result);

// Printing results in HTML
$i=0;
$htmlout="<table align=\"center\" border=\"1\"><tbody>\n<tr><th colspan=\"3\"><b>FreeDLL File Download Statistics</b></th></tr>\n";
$htmlout .= "<tr><th colspan=\"3\">$tot</th></tr>\n";
$htmlout .= "<tr><th colspan=\"3\">$tbw</th></tr>\n";
$htmlout .= "<tr><th>File</th><th>Count</th><th>BW in kb</th></tr>\n";
while ($i < $num) {
	$fname=htmlspecialchars(mysql_result($result,$i,"file"));
	$fcount=trim(mysql_result($result,$i,"count"));
	$bw=trim(mysql_result($result,$i,"bw"));
	$htmlout .= "<tr>";
	$htmlout .=  "<td>$fname</td>";
	$htmlout .=  "<td>$fcount</td>";
	$htmlout .=  "<td>$bw</td>";
	$htmlout .=  "</tr>\n";
    $i++;
}
$htmlout .=  "</tbody></table>\n";
echo $htmlout;

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>
</body>
</html>

download index.php Here

download download.php Here

download dlstats.php Here

PS: Run it through an HTML validator like TIDY. No Errors - No Warnings! :thumbup

NOTE TO OTHERS: This code is very generic skeleton for a Indexed list type of website. Change the database and fields and modify the HTML output code a little and it could easily be a contact list, inventory, schedule... any kind of indexed list.

If you have 2 or more HTML pages that are basically the same, just different data you might want to try a PHP/MySQL solution like this.

Edited by dman
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...