| Solution Centre
Using PHP and MySQL to provide an image library
I'm often asked if MySQL can be used to store images - in
other words as an image library. Yes, it can; you'll store
the data using a "blob" type and you need to ensure that the
four characters " ' \ and null are encoded to that they
don't cause the SQL statements any problems. Once you're
aware of that, it shouldn't be any great problem.
When you come to "fronting" the database with PHP, it gets
slightly more complex - you need to use an unusual encoding
type in your form, and be very careful to get your file
permissions, addslashes, stripslashes and htmlspecialschars
all correct. If you're providing for public image upload,
you also need to protect your script and server against the
upload of copyright images, pornography, adverts for
services that you don't want to advertise and other things
against you acceptable user policy.
Here's a sample script that provide for image upload
(maximum image size 145k, .jpg images only please) as a
demonstration. It will only show you the most recent image
even though lots are stored on the database.
This script may be run at /demo/pic_up.php4
The code is commented so that anyone with basic PHP skills
should be able to adopt and adapt to their needs. A link
back to our site if you do this would be appreciated ;-)
Link to us logo is at /resources/linktous.html
<?php
// Connect to database
$errmsg = "";
if (! @mysql_connect("localhost","trainee","abc123")) {
$errmsg = "Cannot connect to database";
}
@mysql_select_db("test");
// 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 blob)
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=?gim=1 width=144><br>
<b><?= $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>
Note that this is a fully working example that you can try
out on our server using the link above. For security
reasons, we have changed the logins above but it works
exactly as it's displayed above on our test systems. As
you'll appreciate, various measures are taken with the
online example (and those measures may change from time to
time) to ensure the security and acceptability of content
posting and this security may include changes that prevent
posting and / or monitor your activity. See our privacy and
copyright statement that's available as a link in the footer
of this page.
See also PHP Course details
Our ref: phup/php-example-php-form-image-uplaod-store-in-mysql-database-retreive
At Well House Consultants, we provide training courses on
subjects such as Perl, Python, Tcl/Tk, PHP and MySQL. We're asked (and answer)
many questions, and answers to those which are of general interest are
published in this directory. Please also see Our privacy policy and copyright statement.
If you
would like to learn more about us, this web site also includes a Listing and schedule of
public courses, and information about Specially Run Courses and Private Courses.
You can Add a comment or ranking to this page
|
