dman Posted July 27, 2005 Share Posted July 27, 2005 (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 streamecho $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 databasemysql_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 displayif ($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 streamecho $pagemenu;// release the result set so we can use it for next querymysql_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 streamecho $htmlout;// Free resultsetmysql_free_result($result);// Closing connectionmysql_close($link);// write the menus at the bottom of the pageecho $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 connectionmysql_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 resultsetmysql_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 resultsetmysql_free_result($result);// Closing connectionmysql_close($link);?></body></html> download index.php Heredownload download.php Heredownload dlstats.php HerePS: Run it through an HTML validator like TIDY. No Errors - No Warnings! 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 August 1, 2005 by dman Link to comment Share on other sites More sharing options...
Xable Posted July 28, 2005 Author Share Posted July 28, 2005 great job dman, appreciate it. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now