Retrieve and display files from MySQL DB [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pthomas
Forum Commoner
Posts: 68
Joined: Wed Jan 19, 2005 11:28 am
Location: Cincinnati, OH

Retrieve and display files from MySQL DB [SOLVED]

Post 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
Last edited by pthomas on Thu Mar 10, 2005 12:28 pm, edited 1 time in total.
User avatar
pthomas
Forum Commoner
Posts: 68
Joined: Wed Jan 19, 2005 11:28 am
Location: Cincinnati, OH

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
pthomas
Forum Commoner
Posts: 68
Joined: Wed Jan 19, 2005 11:28 am
Location: Cincinnati, OH

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
pthomas
Forum Commoner
Posts: 68
Joined: Wed Jan 19, 2005 11:28 am
Location: Cincinnati, OH

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. ;))
User avatar
pthomas
Forum Commoner
Posts: 68
Joined: Wed Jan 19, 2005 11:28 am
Location: Cincinnati, OH

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

essentially, yes.
User avatar
pthomas
Forum Commoner
Posts: 68
Joined: Wed Jan 19, 2005 11:28 am
Location: Cincinnati, OH

Post 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
Post Reply