download.php setup with mysql

desibaba

I need help to set-up my MySQL database using php

I am trying to make a download.php which would let users to download files after 5 seconds.

The only problem is that my /download directory has many sub directory.

For example if someone wants to download FireFox, then firefox.exe is located in /download/FireFox/firefox.exe

and I want somewhat like this www.domain.com/download.php?fileid=firefox.exe as an output URL to download. I know that I can use MySQL and setup a nice database to do something like this as long as fileid is unique.

Any ideas on how to setup the MySQL database so that I can easily add multiple files from different sub directory at the same time.

Thanks for reading the topic. Have a good one.

WDPEjoe

Here's one way to do it:

The table would have two columns, fileid and filepath. Filepath stores the path between /download and the fileid. When you upload new files (to be downloaded by your users), you just need to have the filepath entered correctly into the database.

E.g. (for your Firefox example)
fileid --> firefox.exe
filepath --> /FireFox/

To access it, assuming that you use *_fetch_assoc(), this is the filepath:

$filepath = "/download" . $array['filepath'] . $array['fileid'];

It is VERY IMPORTANT that fileid is unique, otherwise you will get unexpected behavior for your users.

bpat1434

Hmm... while that's good, a more extensible and probably better suiting database design would be:

ID_FILE int(11) NOT NULL auto_increment
filename varchar(255) NOT NULL
filepath varchar(100) NOT NULL
added int(11) NOT NULL
description SMALLTEXT NULL

That way you can store information about the downloads you have. And you could also post when the downlaod was posted. Plus, now you have the ability to add a comments table to add comments about the download. An example of that table would be:

ID_COM int(11) NOT NULL auto_increment
ID_FILE int(11) NOT NULL
posterName varchar(32) NOT NULL
posterEmail varchar(255) NOT NULL
emailHidden int(1) NOT NULL -- 1 = Yes, 0 = No
comments SMALLTEXT NOT NULL

So you'd have a form two input boxes (name and email), a checkbox (Hide your email address?) and the text-area for comments. THen when you query your downloads database, you cand do a secondary query to see what comments (if any) have been posted and display the relevant ones.

desibaba

i appreciate you help.

But one problem that I am having is that how can i add 10 or more files each day without adding into mysql manually?

I mean is there any php function that can find those new files onto the server and add them into MYSQl directly and not overwrite ole old versions.

one more thing, while I appreciate your help to setup comments on the site, I think for now I am only worried about download links, once I figure that out, I will add the comments page after 1-2 years 🙂

bpat1434

Well gee, to set add the links automatically, you could just set it up so that you have a web interface to upload the files too 😉 Or, I guess you could set up a cron-job. That cron would execute a script which would itterate through your downloads directory, find each file. Based on the filename, see if it's in the database. If not, add it, otherwise skip over and go to the next. Something like the following (which works by the way 😉):

<?php

function itterateDir($dir, $array=array())
{
	$dh = opendir($dir);
	while(false !== ($item = readdir($dh)))
	{
		if($item != '.' && $item != '..')
		{
			if(is_dir($dir.'/'.$item))
			{
				$array = itterateDir($dir.'/'.$item, $array);
			}
			else
			{
				$array[$dir][] = $item;
			}
		}
		clearstatcache();
	}
	if(is_resource($dh))
		closedir($dh);

return $array;
}

function updateItem($base, $item)
{
	$path = $base.'/'.$item;
	$timestamp = time();
	$query = sprintf("INSERT INTO `files` (filename, filepath, added) VALUES ('%s', '%s', $timestamp)",
					mysql_real_escape_string($item),
					mysql_real_escape_string($path)
					);
	$res = @mysql_query($query);

if(!$res)
	return false;
else
	return true;
}

// Okay, so we itterate through our returned array
// Querying to see which files are there or not ;)
// We'll also update them as necessary.
function updateFiles($array)
{
	$db = @mysql_connect('mysql.localhost', 'username', 'password');

// If we don't have a database connection... fail
if(!is_resource($db))
{
	echo 'Unable to connect to Database.';
	exit;
}

@mysql_select_db('database', $db);

foreach($array AS $base=>$ary)
{
	foreach($ary AS $id=>$item)
	{
		$query = sprintf("SELECT * FROM `files` WHERE `filepath` LIKE '%%%s%%'",
						mysql_real_escape_string($item)
						);
		$result = @mysql_query($query);

		$num = mysql_num_rows($result);

		if(!$result)
		{
			echo 'Query error [#'.mysql_errno().']: '.mysql_error().'<br>';
			break;
		}
		else if($num == 0)
		{
			$rslt = updateItem($base, $item);
			if($rslt)
				echo 'Successfully added '.$item.' to the downloads database.<br>';
			else
				echo 'Unsuccessful in adding '.$item.' to the downloads database.<br>';
		}
		else
			echo 'File already in database ('.$item.')<br>';
	}
}
@mysql_close($db);	
}

// Okay, so when it's called, we need to first call itterate dir
// (possibly with a specific directory) and then update the files
// as needed as per updateFiles says we need.
$root = './downloads';
$array = itterateDir($root);



if(is_array($array) && !empty($array))
{
	// Okay, so we have a valid array which isn't empty
	// Let's update the database.
	updateFiles($array);
}
?>