Link to home
Start Free TrialLog in
Avatar of jillybabe
jillybabe

asked on

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

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

Avatar of us111
us111
Flag of Luxembourg image

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
Avatar of andriv
andriv

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

JD

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
Avatar of jillybabe

ASKER

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)

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:
https://www.experts-exchange.com/php/Q.20281089.html

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

filename,
thumb_name and
keywords.

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

Jilly

:-)
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
ASKER CERTIFIED SOLUTION
Avatar of benishor
benishor

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Benishor that really helped!

Jilly