Image handling script needed using mysql and PHP for 300 points!


I need some help please.

I have just bought a digital camera and want to get all my photos into a mysql database and then get them out again using php based on keywords.

I don't have Imagemagick as it won't install but I do have GD and what I want to do is have a search function based on keywords such as "vacation", "car" or "2002" etc... where the images can be inserted into mysql with these keywords associated..

When searched it generates a page of results in a table (3 cells by 3 cells) a random nine images (resized using GD which have the keyword associated) and which link to a new web page containing the full size image. If the page is refreshed I want it to show another different set of nine so I can just load lots in and let my friends and family go play...

I am trying to learn this stuff but this one is beyond me - I have already built the database and have tried all kinds of stuff that didn't work and now I am stuck!

If anyone can do this script for me with some comments so I can understand how it does it then I am happy to give some big points!



Who is Participating?
My suggestion at this chapter would be picking a single table called "pictures" who might contain :

- id           (int, unsigned, 32bit, autoincrement)
- filename     (tinytext)
- filesize     (int, unsigned, 32bit)
- content_type (tinytext)
- keywords   (tinytext)
- data       (datetime)

Assume an images upload directory who would also contain a directory for thumbnails, which will be created during image upload stage

Basically the user story would be something like :

- go to the admin page
- browse your hdd for a picture
- fill in the keywords needed, separating them with comma
- push the upload button

Repeat the process for each new picture you want to upload.
What would the upload script do on "upload" sequence ? Well, it would do the following things :

- upload the file
- check if valid extension/content type
- if not valid, then remove the garbage and put an error message
- if valid, copy the picture to the image directory u'd like to keep your pictures in, create a thumbnail with GD, post it into the image directory's subdirectory called "thumbs", having exactly the same filename as the original and compose the query string needed in order to insert the record in the table.

Note : the field "keywords" in the table would contain a string something like : $keywords =  ";".implode(";", explode(",", $HTTP_POST_VARS["keywords"])).";";

This will later reduce the search operation to a simple query :

$sqlstr = "select * from pictures where keywords LIKE '%;".$searched_keyword.";%' ";

To take only 9 records (or less if not existent), make your query something like :
$sqlstr = "select * from pictures where keywords LIKE '%;".$searched_keyword.";%' LIMIT 0, 9";

And finally, to take 9 random records matching your keyword, do this :

$sqlstr = "select * from pictures where keywords LIKE '%;".$searched_keyword.";%' ORDER BY RAND() LIMIT 0, 9";

If you'd like to match more keywords, not only one, just add another condition to your query. For example :

$sqlstr = "select * from pictures where keywords LIKE '%;".$searched_keyword1.";%' OR keywords LIKE '%;".$searched_keyword2.";%' ORDER BY RAND() LIMIT 0, 9";

This would look for pictures related to searched_keyword1 OR searched_keyword2. To make the query match both of them, change that OR with an AND :

$sqlstr = "select * from pictures where keywords LIKE '%;".$searched_keyword1.";%' AND keywords LIKE '%;".$searched_keyword2.";%' ORDER BY RAND() LIMIT 0, 9";

Hope this helps.
that's a lot of work.
One suggestion, don't save the picture file (only the picture name) into your database but into the file structure.

Good luck, it's not really difficult.

Maybe you could copy/paste your script here that someone could help you and improve it
Instead of having on full size image why not resize the image to a thumbnail, load the thumbnail and the full image seperately.  When doing the search it displays the thumbnails but the full image is available for what ever you are going to do with it.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

andriv's comment is interesting and it will boost your application.
Firstly storing image in database is not good idea. Would pot up considerable load on your db + extra processing would be required to get that from db and present it to user.

Instead store in files only and then store the name of the file in DB. You can also store thumbnail name in db.

You may have columns like

filename, thumb_name, keywords.

If search word matches, you display thumbname which has link to filename.


Yes, but for the search feature you will want to do what us111 suggested "One suggestion, don't save the picture file (only the picture name) into your database but into the
file structure." Because if your images are in the same table as the keywords your search will be slow as hell because it will search through all binary data. If you are stuck in storing the data to the db then use seperate tables.
We were on the same page JD
jillybabeAuthor Commented:

I have actually got two tables at the moment "thumbs" and "fullsize" and I am using these three files below to show images but the problem I have is that it is hardwired and doesn't allow me to search by keyword etc..

Does this make any sense?



>meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"<

>body bgcolor="#FFFFFF" text="#000000"<
>table width="66%" border="0" cellspacing="0" cellpadding="5" align="center"<
  >tr align="center" valign="top"<
    >td<>a href="fullsize.php?id=1"<>img src="getdata.php?id=1" border="0"<>/a<>/td<
    >td<>a href="fullsize.php?id=2"<>img src="getdata.php?id=2" border="0"<>/a<>/td<
    >td<>a href="fullsize.php?id=3"<>img src="getdata.php?id=3" border="0"<>/a<>/td<
  >tr align="center" valign="top"<
    >td<>a href="fullsize.php?id=4"<>img src="getdata.php?id=4" border="0"<>/a<>/td<
    >td<>a href="fullsize.php?id=5"<>img src="getdata.php?id=5" border="0"<>/a<>/td<
    >td<>a href="fullsize.php?id=6"<>img src="getdata.php?id=6" border="0"<>/a<>/td<
  >tr align="center" valign="top"<
    >td<>a href="fullsize.php?id=7"<>img src="getdata.php?id=7" border="0"<>/a<>/td<
    >td<>a href="fullsize.php?id=8"<>img src="getdata.php?id=8" border="0"<>/a<>/td<
    >td<>a href="fullsize.php?id=9"<>img src="getdata.php?id=9" border="0"<>/a<>/td<



if($id) {



    $query = "select bin_data,filetype from thumbs where id=$id order by filename";
    $result = @MYSQL_QUERY($query);

    $data = @MYSQL_RESULT($result,0,"bin_data");
    $type = @MYSQL_RESULT($result,0,"filetype");

    Header( "Content-type: image/jpeg");
    echo $data;

fullsize.php (same as above but different table)

I would suggest 2 tables.


for the thumb, just add _thumb (for example) to the picture filename.


then the query should be a join between PICTURE & KEYWORD where KEYWORD_DESCRIPTION = your_keyword

for random query or records:

try to do it.
But keep in mind, don't store the picture into the database.

Hello us111,

I guess you carried out normalization too far.

In as simple as his case, you really don't want to bring in picture ID and then relate keywords and file name. I really don't think so. Cause here you are capture picture ID twice which will not be used except for that join and which can be avoided.

Follow *KISS* and do it like

thumb_name and

jillybabeAuthor Commented:
Now I am realy lost! Must be the blonde hair but guys I am only a beginner so take it gently?


What we are trying to tell you is recreate you table as the descibed and instead of using the GD create a thumbnail image (resize the orignal on your PC to a small size), then upload the files but save them on the server not the database and in the database store the file names of the thumbnail & larger image, plus the keywords.  Then you can write a search script that will search the keywords then set the file name inside the image tags to display the image.

The first step is to create the table that will contain:

id, thumb_name, file_name, keywords

Then you need to create the script that will allow you to upload the images and keywords, save the image in the specific location then add the keywords, id and image name to the data base.

The last step is to create the script that will search the db and diplay the necessary information. (First get step one and two then we can go on to the last step)

do you have more than one keyword for one picture ?
if yes the structure (id, thumb_name, file_name, keywords) isn't really good,
as I said you need 2 tables.

A better solution would be , a table which contains a list of keywords.
Then for one picture, you will associate one or more keywords.

BTW, the thumb_name is not useful.
for example : testpicture.jpg (the big_one) and you can deduce the thumb testpicture_thumb.jpg
Good point us11, the way I normally handle a situation like this is when saving the image to the specified directory I name it based on the id number and just use id|keywords.  If a keyword matches, my images are referanced as 123.jpg and 123th.jpg
jillybabeAuthor Commented:
Thanks Benishor that really helped!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.