Training, Open Source Computer Languages

This is page http://www.wellho.net/solutions/php-exam ... rieve.html

Our email: info@wellho.net • Phone: 01144 1225 708225

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
  [3926] Filtering PHP form inputs - three ways, but which should you use? - (2012-11-18)
  [3036] Sending out an email containing HTML from within a PHP page - (2010-11-07)
  [2135] What features does this visitors browser support? (PHP) - (2009-04-22)
  [2107] How to tweet automatically from a blog - (2009-03-28)
  [2046] Finding variations on a surname - (2009-02-17)
  [2025] Injection Attack if register_globals in on - PHP - (2009-02-04)
  [1831] Text formating for HTML, with PHP - (2008-10-11)
  [1169] Emailing as HTML (Web Page) - PHP example - (2007-04-30)
  [1136] Buffering output - why it is done and issues raised in Tcl, Perl, Python and PHP - (2007-04-06)
  [1053] Sorting people by name in PHP - (2007-01-26)
  [1001] .pdf files - upload via PHP, store in MySQL, retrieve - (2006-12-19)
  [896] PHP - good coding practise and sticky radio buttons - (2006-10-17)
  [789] Hot answers in PHP - (2006-07-02)
  [589] Robust PHP user inputs - (2006-02-03)
  [50] Current cost in your local currency - (2004-09-16)

Additional PHP Material
  [3210] Catchable fatal error in PHP ... How to catch, and alternative solutions such as JSON - (2011-03-22)
  [3118] Arrays of arrays - or 2D arrays. How to program tables. - (2011-01-02)
  [2684] Exception handling in PHP - (2010-03-18)
  [2215] If nothing, make it nothing. - (2009-06-02)
  [2073] Extra PHP Examples - (2009-03-09)
  [1623] PHP Techniques - a workshop - (2008-04-26)
  [1519] Flipping images on your web page - (2008-01-26)
  [1505] Script to present commonly used images - PHP - (2008-01-13)
  [1485] Copyright and theft of images, bandwidth and members. - (2007-12-26)
  [1451] More PHP sample and demonstration programs - (2007-12-01)
  [1391] Ordnance Survey Grid Reference to Latitude / Longitude - (2007-10-14)
  [1390] Converting from postal address to latitude / longitude - (2007-10-13)
  [1389] Controlling and labelling Google maps via PHP - (2007-10-13)
  [1270] PHP Standalone - keyboard to screen - (2007-07-18)
  [1194] Drawing hands on a clock face - PHP - (2007-05-19)
  [1104] Drawing dynamic graphs in PHP - (2007-03-09)
  [1053] Sorting people by name in PHP - (2007-01-26)
  [1020] Parallel processing in PHP - (2007-01-03)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [917] Syntax checking in PHP - (2006-11-07)
  [839] Reporting on the 10 largest files or 10 top scores - (2006-08-20)
  [822] PHP - a team member leaves - (2006-08-04)
  [806] Check your user is human. Have him retype a word in a graphic - (2006-07-17)
  [789] Hot answers in PHP - (2006-07-02)
  [687] Presentation, Business and Persistence layers in Perl and PHP - (2006-04-17)
  [665] PHP Image viewing application - (2006-04-01)
  [603] PHP - setting sort order with an associative array - (2006-02-13)
  [563] Merging pictures using PHP and GD - (2006-01-13)
  [493] Running a Perl script within a PHP page - (2005-11-12)
  [483] Double Dollars in PHP - (2005-11-02)
  [468] Stand alone PHP programs - (2005-10-18)
  [372] Time calculation in PHP - (2005-07-08)
  [337] the array returned by preg_match_all - (2005-06-06)
  [322] More maps - (2005-05-23)
  [320] Ordnance Survey - using a 'Get a map' - (2005-05-22)
  [239] What and why for the epoch - (2005-03-08)
  [54] PHP and natural sorting - (2004-09-19)

MySQL - Designing an SQL Database System
  [3494] Databases - when to treat the rules as guidelines - (2011-10-23)
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2053] What a difference a MySQL Index made - (2009-02-25)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [666] Database design - get it right from first principles - (2006-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [361] Binary Large Objects or bars - (2005-06-27)
  [59] MySQL - Pivot tables - (2004-09-22)

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.

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • 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