Picture in database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
xionhack
Forum Contributor
Posts: 100
Joined: Mon Nov 10, 2008 9:22 pm

Picture in database

Post by xionhack »

Hello. I have a database with a table called "member_info" in that table is all the info about the member. I want to be able to display with the name of the member, their picture. I just dont know how to save the pictures in the database or how should I do the process of updating or inserting pictures for new members. Can somebody help me? It's the first time that I work with php/mysql and pictures. Thanks!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Picture in database

Post by John Cartwright »

You should generally never store binary data (such as photos) inside the database. It is far more efficient to store the filename and other relevant meta data in the database, and the file itself on the filesystem.
xionhack
Forum Contributor
Posts: 100
Joined: Mon Nov 10, 2008 9:22 pm

Re: Picture in database

Post by xionhack »

Ok. I have the pictures in the server. How should I name them and how do I store the location in the database? Thanks!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Picture in database

Post by John Cartwright »

How should I name them
I generally store the image names as a hash of the original name, along with their user-id to avoid anyone predicting other filenames.

a rough example,

Code: Select all

$originalfile = 'foobar.jpg';
 
$filehash = md5($originalfile) . .'-'. $userid .'.jpg';
how do I store the location in the database
Do you know how to know how to perform an UPDATE query?

Code: Select all

$sql = "
   UPDATE member_info 
   SET filename = '". mysql_real_escape_string($filename) ."'
   WHERE id = ". (int)$userid ."
";
xionhack
Forum Contributor
Posts: 100
Joined: Mon Nov 10, 2008 9:22 pm

Re: Picture in database

Post by xionhack »

Thanks! Also. What if I already have the picture in the server. How can I assign it to a user through the php?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Picture in database

Post by John Cartwright »

xionhack wrote:Thanks! Also. What if I already have the picture in the server. How can I assign it to a user through the php?
The principle is exactly the same as the example I previously posted. You need to collect the filename you want to use, basename() will help in this case, and then update the user record like I also have demonstrated.
xionhack
Forum Contributor
Posts: 100
Joined: Mon Nov 10, 2008 9:22 pm

Re: Picture in database

Post by xionhack »

But for example, Im going to save all the pictures in the server, but Im not going to administrate the data base, so somebody is going to have to assign the picture to the person that it belongs to. How can I make something to show the pictures that havent being assigned and for them to assign it to somebody. Thanks!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Picture in database

Post by John Cartwright »

I don't know the context of which your asking the questions, which makes it dificult for me to give advise. You going to have to ask much more specific (programming) related questions.

It sounds to me like you do not have a basic understanding of SQL, which is going to make it very dificult to help you without doing it for you. If that's the case, I can only suggest you spend some time learning the basics before asking application specific questions. Can you post what you've tried so far?
xionhack
Forum Contributor
Posts: 100
Joined: Mon Nov 10, 2008 9:22 pm

Re: Picture in database

Post by xionhack »

I understand sql. What I want is, the pictures that are saved in the server, to make a php file that would show all the pictures that are not assigned to a member and for you to be able to assign it to a member that has no picture. How can I do that?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Picture in database

Post by John Cartwright »

Firstly, you need to collect a list of filenames on your server. IMO glob() the easiest and most flexible way to collect the filenames into an array.

Secondly, and I'm hoping someone will chime in and suggest a more efficient way, but you need to query which of the files exist in the users database.

$filenames = glob( .... );

Code: Select all

$sql = "
  SELECT filename 
  FROM member_info
  WHERE file_name IN ('". implode("','", $filenames) ."')
";  
(make sure file_name is indexed)

From here, you can iterate this result set and again collect an array of the filenames that are in use. Then, you can perform an array_diff() on the db result and your original array of filenames to determine which files are unassigned.

Then you can iterate your free images assigning them to the members with an empty file_name field.

Of course, some details may differ depending on your implementation.

That's about as far as I'll go without you trying some stuff, and posting some code.
Post Reply