Solved

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

Posted on 2002-03-25
16
229 Views
Last Modified: 2008-03-03
Hi

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!

Anyone?

Jilly

0
Comment
Question by:jillybabe
  • 5
  • 5
  • 3
  • +2
16 Comments
 
LVL 8

Expert Comment

by:us111
ID: 6894456
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
0
 
LVL 5

Expert Comment

by:andriv
ID: 6894483
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.
0
 
LVL 8

Expert Comment

by:us111
ID: 6894497
0
 
LVL 8

Expert Comment

by:us111
ID: 6894501
andriv's comment is interesting and it will boost your application.
0
 
LVL 5

Expert Comment

by:dkjariwala
ID: 6894519
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.

JD

0
 
LVL 5

Expert Comment

by:andriv
ID: 6894523
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.
0
 
LVL 5

Expert Comment

by:andriv
ID: 6894525
We were on the same page JD
0
 

Author Comment

by:jillybabe
ID: 6894530
Hi

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?

Jilly

Showit.html:

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

>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<
  >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<
  >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<
  >/tr<
>/table<
>/body<
>/html<

getdata.php

>?

if($id) {

    @MYSQL_CONNECT("xxx","xxx","xxx");

    @mysql_select_db("photos");

    $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)

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 8

Expert Comment

by:us111
ID: 6894569
I would suggest 2 tables.

PICTURE:
PICTURE_ID
PICTURE_FILENAME

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


KEYWORD:
PICTURE_ID
KEYWORD_DESCRIPTION

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

for random query or records:
http://www.experts-exchange.com/php/Q.20281089.html

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



0
 
LVL 5

Expert Comment

by:dkjariwala
ID: 6894590
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

filename,
thumb_name and
keywords.

JD
0
 

Author Comment

by:jillybabe
ID: 6895861
Now I am realy lost! Must be the blonde hair but guys I am only a beginner so take it gently?

Jilly

:-)
0
 
LVL 5

Expert Comment

by:andriv
ID: 6898574
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)


 
0
 
LVL 8

Expert Comment

by:us111
ID: 6898690
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
0
 
LVL 5

Expert Comment

by:andriv
ID: 6898828
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
0
 
LVL 1

Accepted Solution

by:
benishor earned 300 total points
ID: 6904907
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.
0
 

Author Comment

by:jillybabe
ID: 6970862
Thanks Benishor that really helped!

Jilly
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now