Not sure if I should out this here or in the php forum, I have a question that's probably gonna include both SQL and php...

I'd like to add an image field to a table I have but I've never stored images in an SQL database before and I'm not sure how to go about it.

Ideally, what I would like to do is have the user be able to upload an image to the table and then later return the path of for that image.

Say, I store the image in the localhost directory, what I'd like to do is store that image in a table so that http://localhost/example.jpg is returned when the user searches for whatever is linked to that image in the SQL table.

If I were to list it out (cause that's how my brain works :D):

1. use is given option to upload image
2. image is stored in SQL table using appropriate query
3. user searches for something in table
4. result returns path of image

Basically I found a niffy little cover flow script that needs file paths for the images it uses. Mostly I'm stuck on how to best to store an image in an SQL table - the php and queries need to do that and then the query needed to return the images path. And really the select query will probably be self-explanatory once the insert query is clearer for me.

Thanks so much.

It is not clear from your post if you want to store the binary image data in the database or references to files on the server.

That's probably cause I'm not sure either :P However, I'm thinking references to the file would work for what I want to do?

Well, in that case you have to
- upload the image file to the server (probably a temp directory),
- move it to a location where the webserver can serve it,
- store the path of that location in the database,
- include the image file path in an image tag in your PHP generated HTML code.
Which part is it that gives you headaches?
Apart from that I found it always quite handy to store binary image data in the database and serve them directly from there. That way you do not have to worry about file paths and replication etc.

I've never done it before so I'm just kinda stuck on where to start I suppose. Basically what sort of data type to use for the image in the table, I'm having a real n00b moment at the moment. It was kinda of a whim that had me wanting to add images and I just found I have no idea how to start. Right now I'm returning a title and transforming it to a hyperlink.

<td><a href=\"http://localhost/Catalogue/php/details.php?q=". $title . "\">$title</a></td>

But I found a coverflow javascript which is set up like so.

<div id="gallery">
    <a href="http://localhost/test.html"><img src=""></a>
	<a href="http://localhost/test.html"><img src=""</a>
        width: 500,
        height: 500

And I thought it'd be neat if the $title above was like $image and my query returned the url for use in the img src tag.

Now my problem is I have no idea how to do this. I mean, lets say I store all my images in http://localhost/Catalogue/images/bookCovers

cause that will probably be my directory. So I throw all the images I want in there.

What I need to do is be able to allow the user to browse through that directory when adding a new book to the library database and then store that file name in table. Yeah, file name will work. So I guess I can just put the image column as a varchar.

okay yeah, that works in my head, cause then I can just return the file names and create the urls in the search...

OKAY! So, what I don't know how to do is the browse bit, which would be php yes?

Yes. PHP has a dir() pseudoclass (and some newer stuff) which allows you to read a directory. Gather all file names in an array, sort them and display them in a directory tree. And if you use any framework, there must be tons of ready-made components for that purpose.


This little how to might be useful for you.