Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
Example - PHP form, Image upload. Store in MySQL database. Retrieve.

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 (longblob if the image might exceed 64k) 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. [[[And note - these techniques equally apply to .pdf document files and other binary data such as Word files ....]]]

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 PHP skills can adopt and adapt to their needs. If you want some tips, please see the end of this article. 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 subsequent 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($image) < 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.

TIPS IF YOU'RE INSTALLING THIS CODE YOURSELF

By its very nature, this script pulls together a whole raft of technologies which may or may not be installed / configure on your server ... if you have a shared hosting service, it may not work if facilities are missing or are not configured to a minimum level.

Firstly, you need to have installed on your server and available to you:

 a) MySQL - a recent 3.23 release, or MySQL 4 or MySQL 5
    (configured to allow you to create tables)
 b) PHP - version 4.1 or later with file upload enabled
    (if file upload is not enabled, it cannot work)
 c) the mysql_ set of mysql interfacing routines
    (this program version does NOT use the mysqli versions)

Secondly, you need to change the mysql_connect function call line to reflect the name of the database server you're using if it's a different machine, and the login account name and password that you use to access the database.

Thirdly, you need to change the mysql_select_db parameter to reflect the name of the database that you're using - one in which you have CREATE_PRIV.

If these facilities are not offered by your hosting service, then you won't be able to run my script or any other script that uses PHP and MySQL to upload images to a database. Please post on our Opentalk forum if you're not sure or want assistance - happy to help. Alas, I CANNOT help anonymous users of this page who tell me that the script doesn't work for them via the "rank and review" button ... their problem is probably one of the configuration issues listed above but I've no way of telling them :-/

A further example - handling .pdf files through PHP and a MySQL database can be found in my blog archive for December 2006.
Link to our Ask the tutor forum
August 2010 - I have added a further example set [here] in which I have split the process into three steps to help you debug / follow more closely while you're learning how to do this.

See also PHP Course details

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

PHP - HTML Web Page Data Handling
  [50] - ()
  [589] - ()
  [789] - ()
  [896] - ()
  [1001] - ()
  [1053] - ()
  [1136] - ()
  [1169] - ()
  [1831] - ()
  [2025] - ()
  [2046] - ()
  [2107] - ()
  [2135] - ()
  [3036] - ()
  [3926] - ()

Additional PHP Material
  [54] - ()
  [239] - ()
  [320] - ()
  [322] - ()
  [337] - ()
  [372] - ()
  [468] - ()
  [483] - ()
  [493] - ()
  [563] - ()
  [603] - ()
  [665] - ()
  [687] - ()
  [789] - ()
  [806] - ()
  [822] - ()
  [839] - ()
  [917] - ()
  [937] - ()
  [1010] - ()
  [1020] - ()
  [1053] - ()
  [1104] - ()
  [1194] - ()
  [1270] - ()
  [1389] - ()
  [1390] - ()
  [1391] - ()
  [1451] - ()
  [1485] - ()
  [1505] - ()
  [1519] - ()
  [1623] - ()
  [2073] - ()
  [2215] - ()
  [2684] - ()
  [3118] - ()
  [3210] - ()
  [4655] - ()

MySQL - Designing an SQL Database System
  [59] - ()
  [361] - ()
  [375] - ()
  [494] - ()
  [515] - ()
  [666] - ()
  [918] - ()
  [937] - ()
  [945] - ()
  [1423] - ()
  [1575] - ()
  [1771] - ()
  [2053] - ()
  [2085] - ()
  [2204] - ()
  [2749] - ()
  [3270] - ()
  [3361] - ()
  [3494] - ()
  [4426] - ()

resource index - PHP
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/php-exam ... rieve.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard