lipatdua : Me trying to do something...: February 2009

i always wanted to put files(images, pdf etc..) in MySQL database. Yesterday i manage to have the time to google it on the web...and found it at devarticles...modified it and succesfully test it....but then...when it comes to non-image files...it will download the php file...not the actually file...again...google it..found it at here...modified it...and finally succesfully test it...here is how..
but first..make sure these are set in your php.ini (by phpinfo() it):

file_uploads = On (to allows upload thru php)
upload_max_filesize = ?M (maximum file size allowed to be upload to the webserver eg. 2M = 2MByte file size)..i set it to 16M..
upload_tmp_dir = the location where php will store the uploaded file temporary in your webserver..

and in your MySQL my.cnf

max_allowed_packet must same value with PHP's upload_max_filesize value...else..your will get mysql error like "MySQL server has gone away" or "increase max_allowed_packet" message...here for detail explaination..

Secondly, run this script in your MySQL database :

create database myFiles;
use myFiles;
create table myBlobs
(
    blobId int auto_increment not null,
    blobTitle varchar(50),
    blobData longblob,
    blobType varchar(50),
    blobFileName varchar(255),
    primary key(blobId),
    unique id(blobId)
);

and now…create 4 php files and paste these code in it…

1 - getfiles.php

<html>
<head>
<title> Upload a File </title>
</head>
<body bgcolor="#FFFFFF">
<form enctype="multipart/form-data" name="frmUploadFile" action="grabfile.php" method="post">
<a href="http://www.devarticles.com">
<img border="0" src="http://www.devarticles.com/dlogo.gif">
</a>
<table border="0" cellpadding="0" cellspacing="0" bordercolor="#111111" width="100%">
<tr>
<td width="100%" bgcolor="#FF9900" height="22" colspan="2">
<p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
Upload a File</font></b></td>
</tr>
<tr>
<td width="100%" bgcolor="#FFE3BB" colspan="2">
<p style="margin-left: 10; margin-right: 10"><font face="Verdana" size="2">
<br>Please select a file from your local computer to upload to our web server
for saving in our database. This file can be of any type you like. Once you
have chosen a file, please click on the &quot;Upload this file&quot; button below.&nbsp;
&nbsp;<br>&nbsp;</font></td>
</tr>
<tr>
<td width="15%" bgcolor="#FFE3BB">
<p style="margin-left: 10"><font face="Verdana" size="2">
File Description:</font></td>
<td width="85%" bgcolor="#FFE3BB">
<input type="text" name="strDesc" size="20" maxlength="50"></td>
</tr>
<tr>
<td width="15%" bgcolor="#FFE3BB">
<p style="margin-left: 10"><font face="Verdana" size="2">File Location:</font></td>
<td width="85%" bgcolor="#FFE3BB">
<font face="Verdana" size="2">
<input type="file" name="fileUpload" size="20"></font></td>
</tr>
<tr>
<td width="33%" bgcolor="#FFE3BB">
<p style="margin-left: 10"><font face="Verdana" size="2">
<br>
<br>
&nbsp;</font></td>
<td width="67%" bgcolor="#FFE3BB">
<font face="Verdana" size="2">
<input type="submit" value="Upload this file" name="cmdSubmit"></font></td>
</tr>
</table>
</form>
</body>
</html>

2 - grabfile.php

<?php
// GrabFile.php: Takes the details
// of the new file posted as part
// of the form and adds it to the
// myBlobs table of our myFiles DB.
$strDesc = $_POST['strDesc'];
$fileUpload = $_FILES['fileUpload']['tmp_name'];
$fileUpload_name = $_FILES['fileUpload']['name'];;
$fileUpload_size = $_FILES['fileUpload']['size'];;
$fileUpload_type = $_FILES['fileUpload']['type'];;
if(empty($strDesc)  $fileUpload == "none")
 die("You must enter both a description and file");

// Database connection variables
$dbServer = "localhost";
$dbDatabase = "myFiles";
$dbUser = "root";
$dbPass = "";
$fileHandle = fopen($fileUpload, "r"); //open the uploaded file from temp location
$fileContent = fread($fileHandle, $fileUpload_size); //reads the content of the file
$fileContent = addslashes($fileContent); //add backslash "\" to escape character ',",\,NUL to save in db
$sConn = mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");
$dConn = mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");
$dbQuery = "INSERT INTO myBlobs VALUES ";
$dbQuery .= "(0, '$strDesc', '$fileContent', '$fileUpload_type','$fileUpload_name')";
mysql_query($dbQuery) or die("Couldn't add file to database<br/>".mysql_error());
echo "<h1>File Uploaded</h1>";
echo "The details of the uploaded file are shown below:<br><br>";
echo "<b>File name:</b> $fileUpload_name <br>";
echo "<b>File type:</b> $fileUpload_type <br>";
echo "<b>File size:</b> $fileUpload_size <br>";
echo "<b>Uploaded to:</b> $fileUpload <br><br>";
echo "<a href='getfiles.php'>Add Another File</a>";
echo "<br/><a href='showfiles.php'>Show File List</a>";
?>

3 - showfiles.php

<?php
// Database connection variables
$dbServer = "localhost";
$dbDatabase = "myFiles";
$dbUser = "root";
$dbPass = "";
$sConn = mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");
$dConn = mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");
$dbQuery = "SELECT * ";
$dbQuery .= "FROM myBlobs ";
$dbQuery .= "ORDER BY blobTitle ASC";
$result = mysql_query($dbQuery) or die("Couldn't get file list");
?>
<a href="http://www.devarticles.com"><img border="0" src="http://www.devarticles.com/dlogo.gif"></a>
<table border="1" cellpadding="0" cellspacing="0" bordercolor="#111111" width="100%">
<tr>
<td width="25%" bgcolor="#FF9900" height="21">
<p style="margin-left: 10"><b><font size="2" face="Verdana" color="#FFFFFF">
Description</font></b></td>
<td width="25%" bgcolor="#FF9900" height="21">
<p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
Type</font></b></td>
<td width="25%" bgcolor="#FF9900" height="21">
<p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
Filename</font></b></td>
<td width="25%" bgcolor="#FF9900" height="21">
<p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
Download</font></b></td>
</tr>
<?php
while($row = mysql_fetch_array($result))
{
?>
<tr>
<td width="34%" bgcolor="#FFDCA8" height="21">
<p style="margin-left: 10; margin-right: 10">
<font face="Verdana" size="1">
<?php echo "&nbsp;".$row["blobTitle"]; ?>
</font>
</td>
<td width="33%" bgcolor="#FFDCA8" height="21">
<p style="margin-left: 10">
<font face="Verdana" size="1">
<?php echo $row["blobType"]; ?>
</font>
</td>
<td width="33%" bgcolor="#FFDCA8" height="21">
<p style="margin-left: 10">
<font face="Verdana" size="1">
<?php echo $row["blobFileName"]; ?>
</font>
</td>
<td width="33%" bgcolor="#FFDCA8" height="21">
<p style="margin-left: 10"><font face="Verdana" size="1">
<a href="downloadfile.php?fileId=<?php echo $row["blobId"]; ?>">
Download now
</a></font>
</td>
</tr>
<?php
}
echo "</table>";
?>

4 - downloadfile.php

<?php
$blobId = $_GET['fileId'];
if(!is_numeric($blobId))
die("Invalid blobId specified");
// Database connection variables
$dbServer = "localhost";
$dbDatabase = "myFiles";
$dbUser = "root";
$dbPass = "";
$sConn = mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");
$dConn = mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");
$dbQuery = "SELECT * ";
$dbQuery .= "FROM myBlobs ";
$dbQuery .= "WHERE blobId = $blobId";
$result = mysql_query($dbQuery) or die("Couldn't get file list");
if(mysql_num_rows($result) == 1)
{
    $fileType = @mysql_result($result, 0, "blobType");
    $fileContent = @mysql_result($result, 0, "blobData");
    $file_name = @mysql_result($result, 0, "blobFileName");
  
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: public");
    header("Content-Description: File Transfer");
    header("Content-Type: ".$fileType);
    if($fileType == "audio/mpeg"  $fileType == "application/octet-stream" )
        header("Content-Disposition: attachment; filename=\"$file_name\"");
    header("Content-Transfer-Encoding: binary");
    echo $fileContent;
}
else
{
    echo "Record doesn't exist.";
}
?>

Lastly, open getfiles.php…notice that, on file mime type audio/mpeg and application/octet-stream, browser will ask where to download on your pc…you can add more mime type as you like.

Try it…and gud luck!!!

Happy Blobing…

Lost in time?

From others

Yesterday Reckoning

Statistical info

Mob gang

Man Ana?

meet the band

Wednesday, February 25, 2009

BLOBing me...