Wednesday, February 25, 2009

BLOBing me...

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):

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

and in your MySQL my.cnf

  1. 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

blob_20022009_145148
<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

blob_20022009_145155

<?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

blob_20022009_145346

<?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

blob_20022009_145414


blob_20022009_145405


blob_20022009_145434

<?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…

Saturday, January 17, 2009

Ask What!!


Woit....tak buat keje ker???..huhuhuh

Friday, January 16, 2009

Tagged!! (MagMyPic)


Waaarrggghhhh!!!

I'be been tag by my wife, hope you'll enjoy it!!

Wednesday, January 14, 2009

Metallica Rulezzz!!!

Rock On!!The Devil's Horn


Been dying to see all Metallica vids? Here you go!!
From "One" to the latest "All Nightmare Long"...they got it!


Death Magnetic Guitar Tablature

Since the 1st day Death Magnetic out..this is the book i've been waiting for..but when i'm going to buy it huh??

Tuesday, December 23, 2008

Holidays!!

off we go!!
be back after new year.

Monday, December 22, 2008

Safe or Save?

is it truly safe?
i'm no expert, but then, is it?



or it just to save?

(credit to the star for the graphics)

Saturday, December 20, 2008

first contact..