Using PHP and MySQL to provide an image library - The MySQL Relational Database

Posted by DEVAL (DEVAL), 22 August 2006

I installed and run the script named in the Subject.
The table pix was created, pid
title , and imgdata were created. When I sent the files I could find the pid and title data in the database but the file uploaded is not at imgdata, (no name, no nothing).
The code:
<?php

// Connect to database

$errmsg = "";
if (! @mysql_connect("isright","isright","isright")) {
$errmsg = "Cannot connect to database";
}
@mysql_select_db("Familia");

// First run ONLY - need to create table by uncommenting this
// Or with silent @ we can let it fail every sunsequent time

$q = <<<CREATE
create table pix (
pid int primary key not null auto_increment,
title text,
imgdata longblob)
CREATE;
@mysql_query($q);

// Insert any new image into database

if ($_REQUEST[completed] == 1) {
// Need to add - check for large upload. Otherwise the code
// will just duplicate old file

// ALSO - note that latest.img must be public write and in a
// live appliaction should be in another (safe!) directory.
move_uploaded_file($_FILES['imagefile']['tmp_name'],"latest.img");
$instr = fopen("latest.img","rb");
$image = addslashes(fread($instr,filesize("latest.img")));
if (strlen($instr) < 149000) {
mysql_query ("insert into pix (title, imgdata) values (\"".
$_REQUEST[whatsit].
"\", \"".
$image.
"\")");
} else {
$errmsg = "Too large!";
}
}

// Find out about latest image

$gotten = @mysql_query("select * from pix order by pid desc limit 1");
if ($row = @mysql_fetch_assoc($gotten)) {
$title = htmlspecialchars($row[title]);
$bytes = $row[imgdata];
} else {
$errmsg = "There is no image in the database yet";
$title = "no database image available";
// Put up a picture of our training centre
$instr = fopen("../wellimg/ctco.jpg","rb");
$bytes = fread($instr,filesize("../wellimg/ctco.jpg"));
}

// If this is the image request, send out the image

if ($_REQUEST[gim] == 1) {
header("Content-type: image/jpeg");
print $bytes;
exit ();
}
?>

<html><head>
<title>Upload an image to a database</title>
<body bgcolor=white><h2>Here's the latest picture</h2>
<font color=red><?= $errmsg ?></font>
<center><img src= width=144><br>
<?= $title ?></center>
<hr>
<h2>Please upload a new picture and title</h2>
<form enctype=multipart/form-data method=post>
<input type=hidden name=MAX_FILE_SIZE value=150000>
<input type=hidden name=completed value=1>
Please choose an image to upload: <input type=file name=imagefile><br>
Please enter the title of that picture: <input name=whatsit><br>
then: <input type=submit></form><br>
<hr>
By Graham Ellis - graham@wellho.net
</body>
</html>

Here you can see what the phpAdmin shows:

imgdata longblob Binario Binario - ¡no editar! (2.2 KB) (Tamaño máximo: 51,200KB)

Please help.

Posted by admin (Graham Ellis), 22 August 2006

Are the upload_tmp_dir, etc, set up on your PHP installation? Have you tried uploading a simple file, and proved that it works? That's where I would start; remember that on some operating systems / installs, the default may not exist, and that would give rise to problems such as the one you describe.

P.S. You probably want to change your default file name that's used if no image exists as well ... unless you happen to be working with a copy of my web site!

Posted by DEVAL (DEVAL), 22 August 2006

Thanks Graham,
I work my files inside my external server Yahoo.
I tried the following code and it works:
Index.php:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Subir archivos</title>
<link rel="STYLESHEET" type="text/css" </head>

<body>
<h1>Subir archivos</h1>
<br>
<form action="itisright" method="post" enctype="multipart/form-data">
<b>Campo de tipo texto:</b>
<br>
<input type="text" name="cadenatexto" size="20" maxlength="100">
<input type="hidden" name="MAX_FILE_SIZE" value="100000">
<br>
<br>
<b>Enviar un nuevo archivo: </b>
<br>
<input name="userfile" type="file">
<br>
<input type="submit" value="Enviar">
</form>
</body>
</html>

and
subearchivo.php:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Subiendo una nueva foto</title>
<link rel="STYLESHEET" type="text/css" </head>

<body>
<h1>Subiendo un archivo</h1>
<br>
<div align="center">
<?
//tomo el valor de un elemento de tipo texto del formulario
$cadenatexto = $_POST["cadenatexto"];
echo "Escribió en el campo de texto: " . $cadenatexto . "<br><br>";

//datos del arhivo
$nombre_archivo = $HTTP_POST_FILES['userfile']['name'];
$tipo_archivo = $HTTP_POST_FILES['userfile']['type'];
$tamano_archivo = $HTTP_POST_FILES['userfile']['size'];
//compruebo si las características del archivo son las que deseo
if (!((strpos($tipo_archivo, "gif") || strpos($tipo_archivo, "jpeg")) && ($tamano_archivo < 100000))) {
echo "La extensión o el tamaño de los archivos no es correcta. <br><br><table><tr><td><li>Se permiten archivos .gif o .jpg<br><li>se permiten archivos de 100 Kb máximo.</td></tr></table>";
}else{
if (move_uploaded_file($HTTP_POST_FILES['userfile']['tmp_name'], $nombre_archivo)){
echo "El archivo ha sido cargado correctamente.";
}else{
echo "Ocurrió algún error al subir el fichero. No pudo guardarse.";
}
}
?>
<br>
<br>

<br>
</div>
</body>
</html>

And I could upload images to my folders in my yahoo server. So I think I can upload and the PHP is configured as you say.

This Phrase "You probably want to change your default file name that's used if no image exists as well ... unless you happen to be working with a copy of my web site!" is like Chinesse for me, first because I am starting making dynamic pages and my native language is spanish.
Best regards
Marcos

Posted by DEVAL (DEVAL), 22 August 2006

Graham,
but to tell the true the file is uploaded to the same folder where the index.php and subirarchivo.php are. They are not sent to a temporary folder. There is a tmp folder in the site but the file do not goes there.
Best regards.
Marcos

Posted by admin (Graham Ellis), 22 August 2006

Silly question - how big (in bytes) is your image?

Posted by DEVAL (DEVAL), 22 August 2006

I have tried with images no more than 20,000 (twenty thousands) bytes.
Regards
Marcos

Posted by admin (Graham Ellis), 22 August 2006

And is latest.img public write as described in the script?

Posted by DEVAL (DEVAL), 22 August 2006

Thanks
How can I find latest.img, if I can not see it in any folder?
Regards
Marcos

Posted by admin (Graham Ellis), 23 August 2006

If it's not in the directory that it's supposed to be in, then we're getting close to the problem. Note the comment in my original source:

// ALSO - note that latest.img must be public write and in a
// live application should be in another (safe!) directory.

As a "get it going" test, create an empty file called latest.img in the same directory as the script (yo can use touch, or FTP a file into place, or use a text editor - it doesn't matter) and then ensure the file is writable by the web server - use whatever command is appropriate to the operating system you're using - on Linux, for example, I would use chmod a+rw latest.img.

Once you're going, create a separate directory for the purpose outside the document root that's public writeable, and change the script to use that directory.

Posted by DEVAL (DEVAL), 23 August 2006

Thanks Graham,
latest.img empty created with Macromedia.
latest.img Chmod 777 with Filezilla.
is in the same directory as the script meanwhile.
Tested: the file uploaded is not at imgdata, (no name, no nothing).
Sorry
Marcos

Posted by DEVAL (DEVAL), 23 August 2006

Graham,
I can see that latest.img file in the server change her quantity of bytes acording the last upload image I tried to upload, I can not see what contain it because a file as lates.img can not be opened by a known program. The permission that appear for this latest.img in the server is -rw-------, diferent to I put it when I created it.
Fortunately at least change the size of lates.img, so it is there but can not be saw.
Regards
Marcos

Posted by admin (Graham Ellis), 23 August 2006

OK ... it does appear to be uploading now, which I don't think was the case before. If you use commands such as the file command and od you can examine the contents on the server, and if you grab a copy and open it with any image manipulation program, telling it that the file's a .jpg, that should also work.

Once you've checked that out, I suggest that you run just the few lines of code that insert the image into the database as, according to your latest report, that's what's failing. Any error messages you get should give you a clue as to what is going wrong now.

Posted by DEVAL (DEVAL), 23 August 2006

Thanks Graham,
Yes latest.img is the last .gif file that I tried to upload to the database. To see it I had to rename and make the permission to chmod 777.
¿How I run just the few lines of code that insert the image into the database ? From where to where I should make a new file with that code?
Thanks for your patient.
Regards
Marcos

Posted by admin (Graham Ellis), 23 August 2006

on 08/23/06 at 13:45:16, DEVAL wrote:

Yes latest.img is the last .gif file that I tried to upload to the database. To see it I had to rename and make the permission to chmod 777.

Fair enough. I note you say it's a .gif file, but the script handles .jpg files, which are a different format. I note that you've not changed the script to add in the capability of handling .gif files ... and I just checked back and the original script and description does indeed say ".jpg images only please". Other images will confuse the browser as it will be told its getting format "x" but it will then get format "y".

Quote:

How I run just the few lines of code that insert the image into the database ? From where to where I should make a new file with that code?

Well - you write a separate little PHP program that only includes the few lines that you need, plus an approriate "top and tail".

If you're not familar enough with PHP to do this, I suggest that you learn a bit more into programming and PHP before you work on more advanced scripts such as the one you have chosen. It's a good idea to start with something simple, rather that a "pull all the features together" complete script such as the one you've selected. There's a number of good books and tutorials around on PHP, and if you're interested I'm giving a class on the subject starting on 5th September. I would be very happy to spend extra time with you that week to ensure that your upload is working IN ADDITION to teaching you PHP.

Posted by DEVAL (DEVAL), 23 August 2006

Thanks Graham,
I have tried with gif and jpg but it was the same.
Unfortunately I am a little far from England so I have to decline your invitation.
Yes I will study more about PHP programing.
Thanks again for all your help, I learnt some new things.
Best regards
Marcos

Posted by darkasteroid (darkasteroid), 22 November 2006

I'm new to this board; I hope this thread hasn't been closed. I'm looking for something very like this, but in reverse. I have a mySQL db containing a logbook of my ham radio contacts. Along with the contact information such as name, date, etc., I also have jpg images of the confirmation postcards (called QSL cards) I have received from many of the contacts.

I have a php script that creates an HTML page on-the-fly to print an up-to-date logbook -- the problem is that I cannot seem to get the syntax correct to print the jpg images along with the rest of the information.

I would be extremely appreciative of any suggestions!
Chuck
========

You can see the working (minus images) logbook page at http://www.mr-hopper.com/ham/logGenericCss.php.

Here is the script, minus the usual html headers:

<style type="text/css">
p, td, th {
font: 0.8em Arial, Helvetica, sans-serif;
}

p1, h1, h2 {
color: #33517A;
}

.datatable {
border: 1px solid #D6DDE6;
border-collapse: collapse;
width: 80%;
}

.datatable td {
border: 1px solid #D6DDE6;
padding: 4px;
}

.datatable th {
border: 1px solid #828282;
background-color: #BCBCBC;
font-weight: bold;
text-align: left;
padding-left: 4px;
}

.datatable caption {
font: bold 0.9em Arial, Helvetica, sans-serif;
color: #33517A;
text-align: left;
padding-top: 3px;
padding-bottom: 8px;
}

.datatable tr.altrow {
background-color: #DFE7F2;
color: #000000;
}

</style>

</head>

<body>

<?php

// Connect to the database server
include('../dbConnect.inc');
if (!$dbcnx) {
die('<p>Unable to connect to the '.'database server at this time.</p>');
}

// Select the database
include('dbSelect.inc');
if (!$dbslct) {
die('<p>Unable to locate the '.'database at this time.</p>');
}

// Request the number of QSOs in the table
$num = @mysql_query('SELECT * FROM logbook'); // Returns a resource ID
if (!$num) {
die('<p>Error performing query: '.mysql_error().'</p>');
}

$num1 = mysql_num_rows($num); // Uses that resource ID to count # rows
$num2 = $num1; // Use $num2 for a row switch when displaying table.

// Time period selection
if (date("A") == "AM") {
$ampm = "morning";
} elseif((date("H")>=12) and (date("H")<1

) {
$ampm = "afternoon";
} else {
$ampm = "evening";
} //close time period selection

echo('<a HREF="/ham/ham.php">Back to Ham Radio</a><hr/>');
echo('<h1>The K9PLX Internet Logbook</h1><br />');
echo('<h2>Good '.$ampm.'. Here are the '.$num1.' QSOs logged since 2002.</h2>');
echo('<hr>');
echo('<span style="color:#33517A"><b>QSL code</b> (rightmost column): <b>C</b> = Card rec\'d | <b>E</b> = eQSL rec\'d | <b>L</b> = LoTW Confirmation</span>');
echo('<hr>');

// Request the selected fields from all the QSOs
$result = @mysql_query('SELECT QSO,qsoDate,qsoCall,qsoName,qsoBand,qsoMode,qsoCountry,qsoQsl,qslCard,qslCard2 FROM logbook order by QSO desc');
if (!$result) {
die('<p>Error performing query: '.mysql_error().'</p>');
}

// Display as a table
// First define the table and print the heading row
echo('<table summary="K9PLX Logbook" class="datatable">');
echo('<caption>In Reverse Order By Date And Time</caption>');
echo('<tr>');
echo('<th scope="col">QSO#</th>');
echo('<th scope="col">Date</th>');
echo('<th scope="col">Call</th>');
echo('<th scope="col">Name</th>');
echo('<th scope="col">Band</th>');
echo('<th scope="col">Mode</th>');
echo('<th scope="col">Country</th>');
echo('<th scope="col">QSL</th>');
echo('<th scope="col">QSL Card (front)</th>');
echo('<th scope="col">QSL Card (back)</th>');
echo('</tr>');

// Start the loop
while ( $row = mysql_fetch_array($result) ) {
$num2--; // Decrement the counter - acts as a switch for alternating rows
if (fmod($num2,2)==1){
echo('<tr>');
}
else {
echo('<tr class="altrow">');
}
/* $bytes=$row['qslCard'];
$bytes2=$row['qslCard2']; */

echo('<td>'.$row['QSO'].'</td>');
echo('<td>'.$row['qsoDate'].'</td>');
echo('<td>'.'<font color=navy>'.'<b>'.$row['qsoCall'].'</font>'.'</b>'.'</td>');
echo('<td>'.$row['qsoName'].'</td>');
echo('<td>'.$row['qsoBand']. ' ' . "Meters".'</td>');
echo('<td>'.$row['qsoMode'].'</td>');
echo('<td>'.$row['qsoCountry'].'</td>');
echo('<td>'.$row['qsoQsl'].'</td>');

/* FAILED ATTEMPT TO INCLUDE QSL CARD IMAGES, FRONT AND BACK
echo('<td>';
header("Content-type: image/jpeg");
print $bytes;
exit ();
echo('</td>'>;

echo('<td>';
header("Content-type: image/jpeg");
print $bytes2;
exit ();
echo('</td>'>;

echo('</tr>'); */

}

echo('</table>'); // Close the table

?>

</body>

</html>

Posted by admin (Graham Ellis), 22 November 2006

Nope, the threads not closed - many of the subjects that we have here have a timeless quality to them and people keep coming back

You've posted a long - VERY long - sample (we ask for it to be limited to around 30 lines to give us a chance of giving good targetted help, AND to help you tie the problem down) .... but taking a quick look I can see that you're trying to send out the image as part of the HTML. You can't do that (or, rather, it won't work!). Instead, you need to send out an img tag and have a separate script which uses the header function and sends out the image - whether you choose to have that extra scrip connect to the database again, or have the first script write a temporary file that the second script reads, is up to you.

This page and the sample script it points to many help you.

Posted by darkasteroid (darkasteroid), 22 November 2006

Thanks for the quick reply and sorry for the long posting. I think I must be getting closer now but something somewhere is still not right. I've modified the code thusly:
---
echo('<td>'.$row['QSO'].'</td>');
echo('<td>'.$row['qsoDate'].'</td>');
echo('<td>'.'<font color=navy>'.'<b>'.$row['qsoCall'].'</font>'.'</b>'.'</td>');
echo('<td>'.$row['qsoName'].'</td>');
echo('<td>'.$row['qsoBand']. ' ' . "Meters".'</td>');
echo('<td>'.$row['qsoMode'].'</td>');
echo('<td>'.$row['qsoCountry'].'</td>');
echo('<td>'.$row['qsoQsl'].'</td>');
// Purpose of the IF is to skip the echo if there is no image associated with this row.
if (!$row['qslCard']=="") {
echo('<td>'.'<img src=showQSL.php?QSO=$num2>'.'</td>');
}
if (!$row['qslCard2']=="") {
echo('<td>'.'<img src=showQSL2.php?QSO=$num2>'.'</td>');
}
---
And it runs without error -- but no images appear in the table. ($num2 is the variable associated with the current row.) I've added two scripts "showQSL" and "showQSL2" (2 images to potentially be added in each row of the table). Following is one of them, and it's one I cobbled together from something I saw elsewhere in the forum:
---
$qso=$_REQUEST['QSO'];
if ($qso) {
(... left out the lines connecting to the db and the table ...)
$sql = 'SELECT qslCard FROM logbook WHERE QSO=$qso';
$result = @mysql_query($sql);
$data = @mysql_result($result, 0, "qslCard");
$type = 'jpg';
header('Content-Type: $type');
print $data;
}
---
Sorry to be such a bother, but I'd really like to get a handle on this and nothing I've read really addresses this part of the process. Thanks!

Posted by admin (Graham Ellis), 22 November 2006

I can't spot anything obviously wrong there (in other words ... it basically looks all right). That's not much help, I know.

To debug, I would "view source" to see what's actually being sent out as the HTML, I would remover the @ signs so that you see any error messages, and I would view the images as a separate test in the location line - that way you'll know if the image part is working.

Your point concerning lack of documents on this is a valid one ... give me a month of Sundays to write something

.. seriously, I may come back with something in the next 24 hours

Posted by admin (Graham Ellis), 22 November 2006

OK .. article written and posted to my blog. The specific (archive) article can be found at this link

Enjoy ....

Posted by darkasteroid (darkasteroid), 22 November 2006

That was extremely helpful. Thanks very much. Still no results, but now I'm beginning to wonder whether perhaps when I put the images in the db to begin with I did it incorrectly and that's why they're not displaying now (I used phpMyAdmin's upload feature to put them in the db). I'll tackle that after Thanksgiving break. Just one more quick question, if you're not sick of answering -- do most people use only 1 field for storing images, or do they use additional fields to store the image type (jpg, png), size, etc?

Thanks again.

Posted by admin (Graham Ellis), 23 November 2006

You should NOT include the size of your image in the database! That can be worked out very easily indeed from the content, and to include it would add a needless complexity to your code and a support and maintainance issue. That's a prime example of one of Codd's rules of database design - "no calculated fields base purely on other data that's stored anyway".

However, you often can and will store other data associated with the image - if it's not obviously calculated and you want to store different image types then, yes, the image format. Certainly the name. Probably a desciption. Possibly a date, and a reference to a photographer table. Maybe a link to sales records - where you have sold the picture ... and you should always have a unique id field!

Here's the full description of my table:

Code:

Posted by admin (Graham Ellis), 23 November 2006

I got inspiration ... I've added a search scipt for our own image database - source code here and you can try it out here

Some examples of what it will find you ...
Pictures of Melksham
Canal Pictures

Posted by perlmonger (perlmonger), 18 December 2006

Nice tutorial. I was surprised at how few tutorials one could find on the web regarding storing binary data in a mysql database. This is one is the best of the (small) lot.

I'll apologize now if this is too off-topic but I'm trying to do basically the same thing with PDF files. I can successfully upload and display image files and ascii text but I can't get pdf files or word documents to display. I know the problem is with the escaping of the data but I can't find a solution. I've tried using addslashes() and mysql_escape_string() functions but am still having difficulties. Has anyone successfully done this? I've done it before with Perl but I'm not particularly solid with PHP.

Posted by admin (Graham Ellis), 18 December 2006

It should work for any binary data ... the one thing that I know you'll need to change will be the mime type reported back in the header, though. I suspect that should be application/pdf ....

I'm away from my office system tonight; if the change to the mime type doesn't work, post a follow up and I'll do some experimenting. After all, I'll be a bit quieter over Christmas

Posted by perlmonger (perlmonger), 18 December 2006

I'm currently sending the http header with the content length and content type of application/pdf. I seem to remember doing something with "content_transfer_encoding: binary" in Perl ages ago. Does that ring any bells?

Posted by perlmonger (perlmonger), 18 December 2006

Nevermind about the content-type-encoding, I remembered what that was all about. Not applicable here. Here is some further information that may be useful:

magic_quotes_gpc = on
magic_quotes_runtime = off
PHP 4.2.3
MySQL 3.23.32

I think my code to retrieve and display the pdf from the database is sound because if I insert a pdf file into the DB using phpMyAdmin everything works. The problem has to be with the insert. I'm currently using this stripped-down code to do the insert:
Code:

if((isset($_FILES['review_sheet'])) && ($_FILES['review_sheet']['size'] > 0)) {

$fileName = $_FILES['review_sheet']['name'];
$tmpName = $_FILES['review_sheet']['tmp_name'];
$fileType = $_FILES['review_sheet']['type'];
$fileSize = $_FILES['review_sheet']['size'];

$content = implode('', file($_FILES['review_sheet']['tmp_name']));

if (get_magic_quotes_gpc()) {
$content = stripslashes($content);
}
$content = mysql_escape_string($content);

$insertUploadSQL = "INSERT INTO reviews (pws_id, file_name, file_type, file_size, file_blob) ".
"VALUES ($currentRecordId, '$fileName', '$fileType', $fileSize, '$content')";
$Result = mysql_query($insertUploadSQL, $npdes_ilg64) or die(mysql_error());
}

I'm currently experimenting with stripping the slashes added by magic_quotes and using mysql_escape_string to escape mysql specific characters like \n,\r etc.
I really appreciate any thoughts you (or anyone else) can provide.
Thank You

Posted by admin (Graham Ellis), 19 December 2006

Your settings look similar to mine ... I wonder if you have set blob or longblob - are your .pdf documents over 64k?

I experimented with three scripts (makes a nice demo) and the following triplet works for me:

Upload form:

Code:

<html><head>
<title>Upload a .pdf to a database</title>
<h2>Please select your .pdf</h2>
<form enctype=multipart/form-data action=pdfstore.php method=post>
<input type=hidden name=MAX_FILE_SIZE value=1000000>
<input type=hidden name=completed value=1>
Please choose a .pdf to upload: <input type=file name=imagefile><br>
Please enter the title of that document: <input name=whatsit><br>
then: <input type=submit></form><br>
</body>
</html>

Script to store:

Code:

<?php

$errmsg = "";
if (! @mysql_connect("localhost","wellho","xxxxxxxx")) {
$errmsg = "Cannot connect to database";
}
@mysql_select_db("wellho");

// Create table if it doesn't exist

$q = <<<CREATE
create table pdf (
pid int primary key not null auto_increment,
title text,
imgdata longblob)
CREATE;
@mysql_query($q);

// Store the .pdf

if ($errmsg == "") {
if ($_REQUEST[completed] == 1) {
move_uploaded_file($_FILES['imagefile']['tmp_name'],"latest.img");
$instr = fopen("latest.img","rb");
$image = addslashes(fread($instr,filesize("latest.img")));
if (strlen($instr) < 1000000) {
mysql_query ("insert into pdf (title, imgdata) values (\"".
$_REQUEST[whatsit].
"\", \"".
$image.
"\")");
$errmsg = "Done";
} else {
$errmsg = "Too large!";
}
} else {
$errmsg = "Form not completed";
}}
?>
<html>
<head>
<title>.pdf uploaded</title>
<body>
Operation status: <?= $errmsg ?><br>
Click <a href=pdfget.php>here</a> to download latest .pdf
</body>
</html>

And the code to download the latest .pdf:

Code:

<?php

$errmsg = "";
if (! @mysql_connect("localhost","wellho","xxxxxxxx")) {
$errmsg = "Cannot connect to database";
print ($errmsg);
exit();
}
@mysql_select_db("wellho");

// get the .pdf

$gotten = @mysql_query("select * from pdf order by pid desc limit 1");
$row = @mysql_fetch_assoc($gotten);
$bytes = $row[imgdata];
header("Content-type: application/pdf");
print $bytes;
?>

Note - reference to "img" variables are for historic reasons - I just happened to cut and past parst of the code from my image example

Note - you may need to play with permissions / create a latest.img file that the server can write before you do the upload.

Code runs, works .... try it out here for the upload form and here to view the latest document.

Posted by perlmonger (perlmonger), 19 December 2006

This seems to work for the insert but I don't understand why... magic_quotes_gpc is on but I have to addslashes() regardless to get the insert to work. ??
Code:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "insertForm")) {
if ($_POST["submit"] != "Cancel") {
mysql_select_db($database_npdes_ilg64, $npdes_ilg64);

$insertSQL = sprintf("
INSERT INTO
pws (
npdes_no,
facility_name,
fips_code,
posting_date,
receiving_water,
rad_arse
)
VALUES (%s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['npdes_no'], "text"),
GetSQLValueString($_POST['facility_name'], "text"),
GetSQLValueString($_POST['fips_code'], "text"),
GetSQLValueString($_POST['posting_date'], "date"),
GetSQLValueString($_POST['receiving_water'], "text"),
GetSQLValueString($_POST['rad_arse'], "text")
);

if((isset($_FILES['review_sheet'])) && ($_FILES['review_sheet']['size'] > 0)) {

$tempFH = fopen($_FILES['review_sheet']['tmp_name'], "rb");
$binData = addslashes(fread($tempFH, $_FILES['review_sheet']['size']));

$Result1 = mysql_query($insertSQL, $npdes_ilg64) or die(mysql_error());
$currentRecordId = mysql_insert_id();

$insertUploadSQL = sprintf("
INSERT INTO
reviews (
pws_id,
file_name,
file_type,
file_size,
file_blob
)
VALUES (%s, %s, %s, %s, %s)",
GetSQLValueString($currentRecordId, "int"),
GetSQLValueString($_FILES['review_sheet']['name'], "text"),
GetSQLValueString($_FILES['review_sheet']['type'], "text"),
GetSQLValueString($_FILES['review_sheet']['size'], "int"),
"'".$binData."'"
);
$Result2 = mysql_query($insertUploadSQL, $npdes_ilg64) or die(mysql_error());
}
}

$insertGoTo = "../index.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}

The code to display the pdf is:
Code:

$recordID = "-1";
if (isset($_GET['recordID'])) {
$recordID = intval($_GET['recordID']);

mysql_select_db($database_npdes_ilg64, $npdes_ilg64);
$result = mysql_query("SELECT * FROM reviews WHERE pws_id = $recordID LIMIT 1");

if (mysql_num_rows($result) == 0) {
die('no file');
}

$data = mysql_fetch_assoc($result);
$contents = $data['file_blob'];

// header('Content-Disposition: attachment; filename='.$data['file_name']);
header('Content-length: '.$data['file_size']);
header('Content-type: '.$data['file_type']);
header("Content-transfer-encoding: binary");

print $contents;
mysql_free_result($result);
exit();

}

All quite dirty and without error handling but it works. I'm obviously confused on the magic_quotes issue and I'd really like to understand it. The GetSQLValueString() function looks like this:
Code:

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" .date('Y-m-d', strtotime($theValue)) ."'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

However, I'm not passing the $binData string to it so it's not affecting the pdf file contents. I'm guessing that using fopen and fread instead of imploding the lines returned by file did the trick. In hindsight it make sense because reading the file with file and doing the contatenation didn't actually remove the \r\n, it just slurped the file without undefining the record separator (end of line) character. I'm glad that it works but I really need to figure out why and more importantly if it is actually doing what I think it is.

Thank you Graham for posting the code and helping out. I'm really glad I found your site.

This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.