Hi,

I have been searching daily for longer than I care to remember for a solution where I have a form, where I can upload a products information, along with an Image of it.

I'm a bit of a neat-freak so I would love to have them stored in the MySQL Database.

I have found many many solutions to this problem, but either they are lacking some parts (I'm a designer, not a coder) or I just don't understand them.

What I would really love is if someone could post the complete code to add the information and image to the database (inc. form) and the complete code to a page which will retrieve them.

On some of the retrieval pages I have seen, its all PHP and nothing is displayed in the WYSIWYG part, and as I'd like to be able to customize its appearance without having to figure it all out through the code side of things, I would prefer if it is shown in the WYSIWYG so I can customize it.

So to clarify!

1 Complete page to upload data and an image
1 Complete page to view data and the image

and lets say for arguments sake that my SQL Details are as follows:

Server: localhost
Username: root
Password: password

If someone could help me with this (I know its asking a lot) then I would be very appreciative!

Many thanks guys!

- Keir

Hi, Keir ... good question ... I know I have a sample somewhere. I'll look it up in the morning and post it!

Thanks Graham,

That would be fantastic!

OK ... I've needed to write a sample to show this sort of thing for a while.   Now done.

Narrative and source code listing in our solution centre

Live copy (for you to try out) on our server in the demo area

It's quite short, but it uses a lot of techniques which aren't what you learn on your first day of MySQL or PHP - so I've not tried to put in an explanation of every line.  If you look around here you'll find plenty of pointer to training courses   , books, and other help.  But please do follow up on this thread with any specific queries / comments!

Hi Graham,

Thanks so much for that. Works a treat!

Now, just a couple more questions!

How can I create another, for example, text field to input the data to the database, and also, can I select which images are displayed.

Basically, I need another field for Category, and then i need a page to display only those images that have, for example 'category1' in the category field.

If that is possible then that would be perfect, but eitherway, thanks again!

You can add another field (such as category) by

1. adding to the form something like:

Please enter the category: <input name=categ><br>

2. adding an extra row into the table - something like

   category text,

in the create statement and

               $_REQUEST[categ].
               "\", \"".

in the insert.   Also changing the insert field specification - e.g.

             (title, categ, imgdata)

On the display side, a pulldown menu for categories, products, etc that then displays everying that matches is fine and not too hard but the application is growing ... and really I would suggest you learn your way somewhat into the PHP to do.  I know this sort of application all too well (I wrote the engine behind the Express Cleaning Supplies site for example) and I know where you're headed.

I will "help you on the way" with a further example tomorrow morning unless my mailbox gets jammed overnight - too shattered this evening ....


OK ... Here's another program that works alongside the first one ... selects up to three images with text that matches in the title of the image and displays them ...

Run this code via http://www.wellho.net/demo/pic_alog.php4.   Example - this link will give you the latest (up to 3) images containing "ob" in their titles.

Code:

<?php // Connect to database $errmsg = ""; if (! @mysql_connect("localhost","trainee","abc123")) {        $errmsg = "Cannot connect to database";        } @mysql_select_db("test"); // Find out about images to display if ($_REQUEST[iwant]) {        $whereclause = "where title like \"%".                $_REQUEST[iwant].                "%\"";        $gotten = @mysql_query("select * from pix $whereclause order by pid desc limit 3");        while ($row = @mysql_fetch_assoc($gotten)) {                $imcolumn .= "<tr><td><img src=?gim=$row[pid] width=144><br>";                $imcolumn .= htmlspecialchars($row[title])."</td></tr>";                $nim++;        }        if (! $nim) $imcolumn = "<tr><td>No matching images</td></tr>"; } else {        $imcolumn .= "<tr><td>Images will appear here</td></tr>"; } // If this is the image request, send out the image if ($_REQUEST[gim]) {        $gotten = @mysql_query("select * from pix where pid = $_REQUEST[gim]");        $row = mysql_fetch_assoc($gotten);        $bytes = $row[imgdata];        header("Content-type: image/jpeg");        print $bytes;        exit ();        } ?> <html><head> <title>Selection of up to 3 images from a database</title> <body bgcolor=white><h2>Selects recent matching images</h2> <font color=red><?= $errmsg ?></font> <center><table border=1> <b><?= $imcolumn ?></table></center> <hr> <h2>Please choose text you want to find</h2> <form> Please enter the text: <input name=iwant><br> then: <input type=submit></form><br> <hr> By Graham Ellis - graham@wellho.net</b> </body> </html>

Hi Graham and all!

Newbie seeking guidance please ?

I'm having trouble getting your image/blob script to work at all on my set-up using your supplied code: (The only changes I have made are my user details and the name of the database):

I am simply saving all this code to one file (?) and naming it say, foo.php then dropping it into my Apache root folder. Is this right ? I have checked that my MySQL is set up and creating dbs OK and that Apache is running etc. All is well there - because I am running other scripts that work ok.

My set up is:
MacOSX 10.3.9 (local server)
PHP 5.0.3
MySQL 4.0.21
Apache 1.3.33

If anyone can spot the source of the problem I'd be very grateful to you!

Thanks in advance and thanks Graham for your personal replies and code.

Here's the code I'm using:
---------------------------------

<?php

// Connect to database

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

// 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>

If you can tell us exactly how it fails ("does not work" isn't a very exact description) that might help.

Also try checking the MySQL database directly rather than through this application - does the imagedb database exist?   Has the table been created correctly?   If it has, does it contain any rows of data?

The answers to these questions will help narrow down the problem ... otherwise, it's like looking for a needle in a haystack.  The example that you've stared with shows how lots of faclities role toghether and if it's not working for you you need to cut it down somewhat and check each component.

Hi Graham,

OK- fair enuff! Firstly, I checked to see whether the code was creating the db in MySQL. It wasn't.

I tried creating one called 'imagedb' at the mysql> prompt which did work.
And creating the table data 'pix' which also worked.

I have left the db in existence in MySQL now and tried removing the CREATE table code from the original code.

If I try to run the script now, the HTML page loads (without any connection errors it seems) and allows me (it seems) to name and upload an image file.

What is then displayed is: The name of the image under where the image should be but no image (only a broken link (?) symbol). I have tried in both Safari and Firefox with the same result.

When I then check my db entries in 'imagedb' via the MySQL prompt, no data has been added.

Sorry about all this but I'd love to know what I'm doing wrong!

Thanks.

Tim

How big is your image?   The sample code carefully limits it to 150k so that you don't (on the live copy on my server) put up huge images that burn up all my space.

It's tiny. 16k.

I just read here:

http://support.jodohost.com/showthread.php?p=25898

that storing imgs as a BLOB within the db puts extra pressure on the server and that an alternative is to store only the link to the image in an external directory is better. If so how I do that ? Maybe that might work...

Tim



My view is that in most circumstances, you're better to store the image in the database.   Keeps it all in one place (which aids data integrity) and databases can cope with a lot of images in the same table, whereas directory structures start creaking at the seams once the number of images gets a bit high.  Best analyse and sort out the existing problem rather than swapping it for a whole set of new ones too  

Sure, you're right I guess.

I have just checked my db connection via the tutorial you recommended at VT Web Wizard http://www.vtwebwizard.com/tutorials/mysql/index.php
and all seems to be working fine on my setup. PHP is writing and reading fine to the db.

So I'm still stumped (being a newbie) why the image-upload code isn't functioning as it should.

If you or anyone else has the answer sometime - I'd love to know it!

Thanks for your time & patience.

tim

Could be something like "file_uploads" not being set in your PHP configuration.

A question ... where are you located?  If you happen to be "not a million miles" from us, you'ld be welcome to pop by one evening and I'm sure we could get it sorted in a few minutes.

Unfortuanately I'm in London. But thanks! Maybe I should book to do a course and learn the 'proper' way...

Here is section from my php.ini file:
---------------

;;;;;;;;;;;;;;;;
; File Uploads ;
;;;;;;;;;;;;;;;;

; Whether to allow HTTP file uploads.
file_uploads = On

; Temporary directory for HTTP uploaded files (will use system default if not
; specified).
;upload_tmp_dir =

; Maximum allowed size for uploaded files.
upload_max_filesize = 2M

---------------

Does that make any sense ? ;-?



Yes ... using the system defaults ... should work provided that the default directory exists.

Next steps to "nailing" this one ...

- modify the script to print out the file contents of what's been uploaded back into the browser, and upload a text file to check that end of the mechanism.  That may give a clue.

- second test; upload an image through FTP and modify the script to put THAT into the database, no matter what you upload.

Depending on which of these work(s), you should get a further clue to help narrow down the problem.

hmm,

Thanks but I don't really know how to do those things you suggested...but would like to learn.

Maybe if I uncommented and added a directory to: (in php.ini)

;upload_tmp_dir =

What path would I use here ?

(Anyway, when I try to edit my php.ini file (ie test; register_globals = On) the Apache server/PHP Module doesn't recognize the changes anyway whichever of three different php.ini files/locations I use).

Tim



When you make any changes to the php.ini file, you'll have to restart the Apache server for any changes to happen.  As for the temp upload directory, it doesn't matter where you put it...just put the path in to whichever directory you want to use.

---