Page 1 of 1

Retrieve and display files from MySQL DB [SOLVED]

Posted: Thu Mar 10, 2005 8:37 am
by pthomas
Anyone know how to display a picture or anything else in your webpage after getting it from a mySQL DB? Google helped me to run into this tutorial:
http://www.php-mysql-tutorial.com/php-mysql-upload.php
for being able to download files from a mysql DB, but I can;t figure out how to show them.

I've got a few sound files that are actually flash files, .SWF. I'm storing them in my DB and am trying to figure out how to grab one of the .swf files from the DB and display it in my page. This is the code I have so far. It does grab the file from the DB but when I go to display it I get garbage: its printing the file contents and not actaully using the file as it should.

Code: Select all

<?PHP
        $query   = "SELECT name, type, size, content FROM Sound_Files WHERE id = '5'";
        $result  = mysql_query($query) or die('Error, query failed');
        list($name, $type, $size, $content) = mysql_fetch_array($result);
        echo '<embed src="'.$content.'" width="1" height="1"></embed>';
?>
Thanks,
Paul

Posted: Thu Mar 10, 2005 8:44 am
by pthomas
Here's the upload page I'm using:

Code: Select all

if(isset($_POST&#1111;'upload']))
{
        $fileName = $_FILES&#1111;'soundfile']&#1111;'name'];
        $tmpName  = $_FILES&#1111;'soundfile']&#1111;'tmp_name'];
        $fileSize = $_FILES&#1111;'soundfile']&#1111;'size'];
        $fileType = $_FILES&#1111;'soundfile']&#1111;'type'];
        $error    = $_FILES&#1111;'soundfile']&#1111;'error'];

        if (upload_success($error, $msg))
        {
                $fp = fopen($tmpName, 'rb');            //Open in read-only and in binary mode
                $content = fread($fp, $fileSize);
                $content = addslashes($content);
                fclose($fp);
//May need to add in escape slashes $fileName = addslashes($fileName);
                connect_db();
                $query = &quote;INSERT INTO Sound_Files (ID, name, size, type, content ) &quote;.
                        &quote;VALUES ('', '$fileName', '$fileSize', '$fileType', '$content')&quote;;
                mysql_query($query) or die('Error, file insertion failed, file already exists?');

                echo &quote;&lt;br&gt;File $fileName uploaded: $msg&quote;;
                echo &quote;&lt;BR&gt;size: $fileSize&quote;;
                echo &quote;&lt;BR&gt;Type: $fileType&lt;BR&gt;&quote;;
        }
        else
                echo &quote;There was an error uploading the file: $msg\n&quote;;
}
######################
#       Functions
######################
function upload_success($error, &amp; $msg)
{
        switch($error){
        case 0:
                $msg = &quote;Upload successful.&quote;;
                return true;
                break;
        case 1:
                $msg = &quote;The file exceeds PHP's upload_max_filesize.&quote;;
                break;
        case 2:
                $msg = &quote;The file exceeds HTML's MAX_FILE_SIZE specified in the HTML.&quote;;
                break;
        case 3:
                $msg = &quote;The file was only partially uploaded.&quote;;
                break;
        case 4:
                $msg = &quote;No file was uploaded.&quote;;
                break;
        case 6:
                $msg = &quote;Missing temporary folder to store file.&quote;;
                break;
        default:
                $msg = &quote;An unknown error has occured. <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>.&quote;;
                break;
        }
        return false;
}
######################
#       The BEEF!!
######################
?&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Upload File To MySQL Database&lt;/title&gt;
&lt;meta http-equiv=&quote;Content-Type&quote; content=&quote;text/html; charset=iso-8859-1&quote;&gt;
&lt;style type=&quote;text/css&quote;&gt;
&lt;!--
.box {
    font-family: Arial, Helvetica, sans-serif;
    font-size: 12px;
    border: 1px solid #000000;
    }
--&gt;
&lt;/style&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;form action=&quote;&quote; method=&quote;post&quote; enctype=&quote;multipart/form-data&quote; name=&quote;uploadform&quote;&gt;
&lt;table width=&quote;350&quote; border=&quote;0&quote; cellpadding=&quote;1&quote; cellspacing=&quote;1&quote; class=&quote;box&quote;&gt;
&lt;tr&gt;
        &lt;td width=&quote;246&quote;&gt;
        &lt;input type=&quote;hidden&quote; name=&quote;MAX_FILE_SIZE&quote; value=&quote;2000000&quote;&gt;
        &lt;input name=&quote;soundfile&quote; type=&quote;file&quote; class=&quote;box&quote; id=&quote;soundfile&quote;&gt;
        &lt;/TD&gt;
        &lt;td width=&quote;80&quote;&gt;
        &lt;input name=&quote;upload&quote; type=&quote;submit&quote; class=&quote;box&quote; id=&quote;upload&quote; value=&quote;  Upload  &quote;&gt;
        &lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;&lt;/form&gt;&lt;/body&gt;&lt;/html&gt;
And Here's the download page that currently doesn;t work:

Code: Select all

connect_db();
if(isset($_GET['id'])){
        $id      = $_GET['id'];
        header("Content-Disposition: attachment; filename=$name");
        header("Content-length: $size");
        header("Content-type: $type");
        echo $content;
        exit;
}
####################
#       The Beef
####################
?>
<html>
<head>
<title>Download File From MySQL</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?PHP
        $query   = "SELECT name, type, size, content FROM Sound_Files WHERE id = '5'";
        $result  = mysql_query($query) or die('Error, query failed');
        list($name, $type, $size, $content) = mysql_fetch_array($result);
        echo '<embed src="'.$content.'" width="1" height="1"></embed>';

?>
</body>
</html>
-The raw file contents are displayed instead of using the file.

Paul

Posted: Thu Mar 10, 2005 8:55 am
by feyd
the html output and content must be sent in seperate streams. This requires a seperate database query and page request for each file you want to display.

Posted: Thu Mar 10, 2005 9:03 am
by pthomas
Whoa, you lost me there. I have no idea what you mean by seperate streams!

I understand that for each item I wish to display, I'll need to make another DB query to retrieve it. What do you mean by making a seperate page request?

Thanks,
Paul

Posted: Thu Mar 10, 2005 9:18 am
by feyd
instead of outputing $content, which is the binary data that of the file, you need to output a script url that will send the binary data on request.

Posted: Thu Mar 10, 2005 9:56 am
by pthomas
I have no idea how to do this:
you need to output a script url that will send the binary data on request.
I know that I'm outputting the binary data, I have that stated below the code I pasted above. But how do I do what you're saying?

I've found a few decent examples on downloading from a mysql DB from googling:
http://www.devarticles.com/c/a/MySQL/Bl ... and-MySQL/
http://www.php-mysql-tutorial.com

But all deal with downloading, which doesn't help me. Can you point me to a site that gives a how-to or some further insight on how to display binary data or give me a bit more of an explanation?

Paul

Posted: Thu Mar 10, 2005 10:01 am
by feyd
you actually are creating a download system. The link you use will download the binary data to the browser for its use.

for example.. you output "myattachedfile.php?id=1234"

this should load the record corresponding to 1234 from the myattachedfile.php script and dump the binary data (along with proper headers I hope. ;))

Posted: Thu Mar 10, 2005 10:15 am
by pthomas
Ok, so let me get this straight. Being able to get any file for download is actually part of the puzzle because the website will be requesting the file essentially has to download it.

So the PHP page that gets the file (get.php) kinda has it attached and would be something like this:

Code: Select all

if(isset($_GET['id'])) {
    $id      = $_GET['id'];
    $query   = "SELECT name, type, size, content FROM upload WHERE id = '$id'";
    $result  = mysql_query($query) or die('Error, query failed');
    list($name, $type, $size, $content) = mysql_fetch_array($result);

    header("Content-Disposition: attachment; filename=$name");
    header("Content-length: $size");
    header("Content-type: $type");
    echo $content;

    exit;
}
So if that page essentially got a flash .swf file that I wanted to display, I could use something on the content page like

Code: Select all

&lt;embed src=&quote;get.php?id=3&quote; width=&quote;1&quote; height=&quote;1&quote;&gt;&lt;/embed&gt;
?
Is that what you mean?

Paul

Posted: Thu Mar 10, 2005 10:23 am
by feyd
essentially, yes.

Posted: Thu Mar 10, 2005 12:25 pm
by pthomas
Bad-ass! Here's how the code ended up coming out for those interested. (I always hate it when someone asks a question, finds the answer and doesn;t post it!)

Thanks for pointing me in the right direction feyd.

Download file:

Code: Select all

<?PHP  $query   = "SELECT id FROM `Sound_Files` ORDER BY RAND() LIMIT 1";
  $result  = mysql_query($query) or die('Error, query failed');
  $row = mysql_fetch_row($result);
  $id = $row[0];

####################
#       The Beef
####################
//This takes the random ID and gets the sound file associated with
//it. It should actually display/play the .swf file from the DB
?>
<html>
<head>
<title>Download File From MySQL</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
        <embed src="get.php?id=<?PHP echo $id; ?>" width="1" height="1"></embed>
</body>
</html>
The file responsible for getting the file out of the DB and returning it to the requested page:

Code: Select all

<?PHP
if(isset($_GET['id'])) {
        $id     = $_GET['id'];
        $query   = "SELECT name, type, size, content FROM Sound_Files WHERE id = '$id'";
        $result  = mysql_query($query) or die('Error, query failed');
        list($name, $type, $size, $content) = mysql_fetch_array($result);

        header("Content-Disposition: attachment; filename=$name");
        header("Content-length: $size");
        header("Content-type: $type");
        echo $content;
        exit;
}
else
        exit;
?>
I always wondered how sites like newegg.com display all their content. There's no doubt in my mind that they use someing very similar to the above to get all their images, details and such to dynamically build all those pages.

Neat-o,
Paul