Retreive images from database based on username

[jespin]

Hi all,

This is my first post to be gentle. I'm currently researching for my dissertation project where i'm planning to make a forum that allows users to discuss crusing events. The site will allow user login and will have admin features.

My issue occurs on the members side of things. I wish to have member images/avatars displayed in both my forum posts and on a user profile page. Whilst the code for these would pretty much be similar I am wondering if I am going about my current code attempts the right way. While I am not a PHP expert I am quite adept in it but have not had to use it for over a year and so have forgotten a lot.

I already have my login scripts and have a basic forum. What I am looking to do is query the database based upon a users username to display their profile picture next to their profile name when then make forum posts. Any ideas?

Jespin

[diafol]

You'd probably want to use the user 'id' (integer) rather than use the user 'name'. You need the sql statement, something like:

SELECT avatar FROM users WHERE user_id = '{$id}';

[jespin]

So if I passed that statement into a PHP variable, how would I then go about printing that variable into a html image link to display the image? Would it be along the following lines:

<?php
$avatar=mysql_query("SELECT avatar FROM users WHERE user_id = '{$id}' ";

echo "<img src='.$avatar.'>";
?>

[almostbob]

sorry Jespin, one wrong detail

echo "<img src='.$avatar.' >"; // will fail the concatenation is wrong dquotes are not closed at the dot
/* but */
echo "<img src='$avatar' >"; // variable inside dquotes OK
echo "<img src='".$avatar."' >"; // variable concatenated into the text OK

assuming user Joe, avatar joepic
output html from the above three samples would be something like

<img src='.joepic.jpg.' > <!-- oops -->
<img src='joepic.jpg'>
<img src='joepic.jpg' >

[diafol]

OK, you seem to be struggling with the basics.

If you are building a forum page with displayed posts: avatar/name/other user info in a side cell with an adjacent message/post. Your SQL will be quite complicated.

You will need to use at least one INNER JOIN.

I imagine your DB structure (highly simplified here) is something like:-

user
user_id
name
avatar
joined

posts
post_id
post_body
dated
topic_id
user_id

forums
forum_id
forum_name

topics
topic_id
topic_title
dated (optional - could be taken from the first post in the topic)
user_id (optional - could be taken from the first post in the topic)
forum_id

If so, when displaying a topic page, you will need to JOIN topics, posts and user. The creation of html from php is trivial compared to getting your SQL correct and your DB tables structured correctly.

The topic page with a number of posts in dated order will require you to use a while loop. This will spit out your html as well, e.g.

$output = "<table><thead><tr><th>User</th><th>Post</th></tr></thead>";
while($data = mysql_fetch_array($result)){

   ...(clean your db values and place them into 'easy to type' variables)...

    $output .= "<tr><td><a href='/profiles.php?id={$uid}' title='go to user profile page'>{$username}</a><br /><img src='{$avatar}' /><br />Location: {$location}</td><td>{$title}<br />{$post}</td></tr>";
}

    $output .="</tbody></table>";

The above is just something simple off the top of my head. The whole table will now be in a variable, ready to be displayed anywhere you want on the page, e.g.

<div id = "post_table"><?php echo $output;?></div>

I haven't included any classes or ids as table element attributes - you can avoid using these by using CSS descendants. Also HTML output not beautified with newlines or indenting. THis can be done by adding "\n" and "\t" respectively.

[baudday]

When I did avatars for the first time I made it so when the person uploaded their avatar, it would save to some directory /images or something and then wrote the filename to the db. That way, when I while looped to spit out the data like someone has already mentioned i would put the img src= '/images/avatar.jpg' avatar.jpg having been recalled from the db. Might not be the most effective way of doing it, but I'm a noob as well and that was the simplest way I could think of doing it.

[jespin]

When I did avatars for the first time I made it so when the person uploaded their avatar, it would save to some directory /images or something and then wrote the filename to the db. That way, when I while looped to spit out the data like someone has already mentioned i would put the img src= '/images/avatar.jpg' avatar.jpg having been recalled from the db. Might not be the most effective way of doing it, but I'm a noob as well and that was the simplest way I could think of doing it.

This was the way in which I did it for a previous uni project however, if you do not limit the size of the upload then you can end up screwing up things like bandwidth etc with whoever you are hosting with. Also, from experience this is a very messy way to do things and can usually end up causing more harm than good hence why this time I want to do it a proper way. A 'standards' sort of way in a database if you like.

As for 'ardav' I already have all my forum scripts and they are working fine so don't want to be tinkering with them. Furthermore, as I hate databases with a passion I was hoping to keep it all relatively simple with isolated tables. The way in which my forum script works is it stores the user who made the post in a posts table. The recent reply value is then overwritten each time a new person makes a reply to that post.

What I was hoping to do was, for when a user makes a post it will instead take their username value (stored in session variable_ and input it instead of the text field I have currently. Then it would be a matter of simply retreiving the image from the users table each time the page loads based upon the set username. I'm not sure whether you know what I mean but I know for certain that it works as I have seen many others do it last year in one of my projects.

[somedude3488]

So pretty much you are storing the image data in the database, right?

You have never stated this clearly enough that we understood what you are trying to do.

[jespin]

It says quite clearly in my original post that the image will be pulled based on the username. Where else would it be pulled from?
And sorry if you don't consider me clear, it's just i need answers as I'm in the planning stage but have most of the code for the other things. I need to ensure that I know I can do everything in my project that I am going to suggest before I suggest it.

[diafol]

As for 'ardav' I already have all my forum scripts and they are working fine so don't want to be tinkering with them. Furthermore, as I hate databases with a passion I was hoping to keep it all relatively simple with isolated tables. The way in which my forum script works is it stores the user who made the post in a posts table. The recent reply value is then overwritten each time a new person makes a reply to that post.

Fair enough, but I find it incredible that you're building a DB app if you despise them so. The key to building a forum app is to create a robust db structure and to have well formed and thought out queries.

There may be trouble ahead
But while there's moonlight and music
And love and romance
Let's face the music and dance

Your post table field is overwritten every time a new post message is placed into a topic. Is that right? If so, you're heading too fast around a sharp corner. What happens if you get 100 long-winded replies to a post, is all this info stored in a single record field? I don't think that's a methodology that'll work in the long run. Good luck.

[somedude3488]

It says quite clearly in my original post that the image will be pulled based on the username. Where else would it be pulled from?
And sorry if you don't consider me clear, it's just i need answers as I'm in the planning stage but have most of the code for the other things. I need to ensure that I know I can do everything in my project that I am going to suggest before I suggest it.

The reason I asked is because most people who don't understand something, use the wrong terminology to describe their problem, confusing everyone trying to help. I would say 90% of the people who have asked this question pretty much don't understand how to put a URL from a row in the database into a html anchor tag. You didn't specify that you stored the image in a blob field of a database. That's important info.

Since you are doing it this way, I have to tell you that its not the proper way to do it. Storing images in the database is really hard on them. That amount of data being pulled every time will affect performance greatly. Storing images/files on the server is the way to go.

As for the problem, you need to create a separate page that can retrieve the image data based on the username (_GET) and echo it out to the browser with the proper headers.

Example: if I went to image.php?user=kkeith29

if ( !isset( $_GET['user'] ) ) {
  die('No username provided'); //or show a default image
}

$user = mysql_real_escape_string( $_GET['user'] );
$query = mysql_query("SELECT `avatar` FROM `users` WHERE `username` = '{$user}' LIMIT 1");
if ( mysql_num_rows( $query ) == 1 ) {
  list( $data ) = mysql_fetch_row( $query );
  header('Content-Type: {image content type here}');
  echo $data;
  exit;
}
else {
  die('Username doesn\'t exist');
}

[jespin]

To ardav

No. This is not the case. The forum shell is largely from elsewhere but I have adapted it to meet my needs. It is about 3 weeks since I last looked at it however as I am currently working on displaying a profile page for each member hence why I needed some help with diplaying images based on a username. This could then be adapted to display within the forum.

From what I remember of the forum from just taking a look at it, all posts and replies are stored in the same table. However, when replies are made, they are assigned a parent ID so that they are a child of the original post. It works and currently I have over 1000 sample entries in there which are working fine.

Furthermore, I am creating a website with a database back-end because I have to. It's part of my course and my project wouldn't acheive many marks if I just made a boring static site. I would prefer to do something other than a database as I won't have to use proper databases in the future when I am a teacher. However, my course currently requires it hence why I am forced into a corner.

[jespin]

To KKeith

So you're suggesting on my user registration page I make an upload script that allows the user to upload an image to a folder on the server. Then allow that script to assign the username entered as the filename whilst writing an image link into the database pointing to the specific file they have uploaded. This when applied to a variable to be used should recall the image related to that specific user. Is that what you mean?

The reason I said the database way is the 'standards' way is because that is the way all of my lecturers said it should be done. And considering they made all their money in the web world I was inclined to follow their so-called 'wise-words' even if I disagreed. This what you have described was the way I used to do it before going to uni.

[somedude3488]

To KKeith

So you're suggesting on my user registration page I make an upload script that allows the user to upload an image to a folder on the server. Then allow that script to assign the username entered as the filename whilst writing an image link into the database pointing to the specific file they have uploaded. This when applied to a variable to be used should recall the image related to that specific user. Is that what you mean?

The reason I said the database way is the 'standards' way is because that is the way all of my lecturers said it should be done. And considering they made all their money in the web world I was inclined to follow their so-called 'wise-words' even if I disagreed. This what you have described was the way I used to do it before going to uni.

Pretty much, but I would change a few things.

When they upload a picture you save the image into a folder above the web root using a randomly generate name. You put it above the web root so no one can execute their own files. The randomly generated name is for extra security. After its moved to a folder, you save the filename into the database along with the content type supplied by the _FILES array (this makes it easier to output the file later).

Then in the forum script, you can access the avatar by <img src="avatar.php?user={user_name}" /> .

avatar.php would then get the filename and content type from the database based on the username in the url. You then read the contents of the file and output the data with the content type.

[diafol]

I understand your point now that you've explained it, but I still don't agree.

...I am creating a website with a database back-end because I have to. It's part of my course and my project wouldn't acheive many marks if I just made a boring static site. I would prefer to do something other than a database as I won't have to use proper databases in the future when I am a teacher. However, my course currently requires it hence why I am forced into a corner.

I hope you're not going to be an IT teacher, otherwise, you'll have to get your hands dirty with DBs. I'm a chemistry teacher (high school; UK), but help out with our computing classes. MySQL and pHp (and Java) are being used by more and more students. Gone are the days of VB6 and the ADO - it's generally the less able kids who have to stick with VB to pass their first module. The fact that some examination bodies are implementing e-portfolios will probably convince a number of schools to go down the pHp/mysql route. This will be a logical step (IMO) with the "DIDA" course for younger students having interesting "applied" features including web design.

[jespin]

I understand your point now that you've explained it, but I still don't agree.

I hope you're not going to be an IT teacher, otherwise, you'll have to get your hands dirty with DBs. I'm a chemistry teacher (high school; UK), but help out with our computing classes. MySQL and pHp (and Java) are being used by more and more students. Gone are the days of VB6 and the ADO - it's generally the less able kids who have to stick with VB to pass their first module. The fact that some examination bodies are implementing e-portfolios will probably convince a number of schools to go down the pHp/mysql route. This will be a logical step (IMO) with the "DIDA" course for younger students having interesting "applied" features including web design.

That is exactly what I want to be. And strange, because after visiting my old school recently they are still doing the same as we did when I was back at school. No java, no PHP and no MySQL. IN fact, all they do is Access, Excel etc and creating websites in Dreamweaver. This is the same as all of the other schools I have visited in the area as well as in Wales where I go to uni. And even if I were to encounter kids who wanted to do PHP and MySQL the requirements of the curriculum would mean they wouldn't be doing anything advanced meaning I can help them. I may seem like a noob on here but unless I am doing stuff constantly I forget it. That is why I am using this forum as a refresher to get me back into PHP as I used to be very competent in it. I'm sure after a couple more days it'll all have come back to me and I won't have to ask such noob questions however with all the other languages I am trying to remember at the same time it's is very easy to get slightly confused with syntax as I have been doing here.

[diafol]

I can't claim to speak for ALL schools in Wales, but our Yr.9 students are getting involved with HTML/CSS and working their way into pHp/MySQL. Some of our students have taken the plunge and have gone headfirst into Java (yuk!).

If the schools in your area are still using VB6/Access as good practice, then they're lacking vision. It's like telling students to bone up on CP/M - you never know it may come back! The problem with many IT depts is that they have staff who were network specialists or specialized in fields other than programming. I'm not saying that this is bad, but it just ensures stagnation with regard to the programming aspect of the post-16 schemes of work. They use tried and tested models (e.g. build a video hire shop app in Access). Oh Christ, beam me up Scotty. If that's the best that they can come up with...

Although we introduce students to VBA as a simple intro to programming, they see the web as the home of apps and want to create online apps/sites. This has the added bonus of learning SQL. You can create complicated Access apps without ever having seen a line of SQL in your life.

As a lumbering jobber, I've encouraged them to leave the confines of the OS behind. Perhaps I'm wrong, only time will tell.

And even if I were to encounter kids who wanted to do PHP and MySQL the requirements of the curriculum would mean they wouldn't be doing anything advanced meaning I can help them.

BTW - that's an awfully smug attitude that'll ensure that you look like a complete idiot when a kid asks for advice. As you haven't had any real experience of teaching yet, you'll be surprised or even horrified at what kids can do. Some of them will be able to code as well if not better than you.

And not helping them to reach their potential because it's not required by the curriculum is lame.

[jespin]

I can't claim to speak for ALL schools in Wales, but our Yr.9 students are getting involved with HTML/CSS and working their way into pHp/MySQL. Some of our students have taken the plunge and have gone headfirst into Java (yuk!).

If the schools in your area are still using VB6/Access as good practice, then they're lacking vision. It's like telling students to bone up on CP/M - you never know it may come back! The problem with many IT depts is that they have staff who were network specialists or specialized in fields other than programming. I'm not saying that this is bad, but it just ensures stagnation with regard to the programming aspect of the post-16 schemes of work. They use tried and tested models (e.g. build a video hire shop app in Access). Oh Christ, beam me up Scotty. If that's the best that they can come up with...

Although we introduce students to VBA as a simple intro to programming, they see the web as the home of apps and want to create online apps/sites. This has the added bonus of learning SQL. You can create complicated Access apps without ever having seen a line of SQL in your life.

As a lumbering jobber, I've encouraged them to leave the confines of the OS behind. Perhaps I'm wrong, only time will tell.

BTW - that's an awfully smug attitude that'll ensure that you look like a complete idiot when a kid asks for advice. As you haven't had any real experience of teaching yet, you'll be surprised or even horrified at what kids can do. Some of them will be able to code as well if not better than you.

And not helping them to reach their potential because it's not required by the curriculum is lame.

I don't like the tone of your last post. I am going on the experience of what I know. None of the people in my year group including me had encoutered any programming before we went to university and this is still largely what most IT and Applied IT students will have to face when going to uni. I know I absolutely hated programming because of this and still do hence why i'm more geared towards web apps. And maybe in your area that's what kids are like but so far, working as a teaching assistant in my spare time to help with my teaching applications I have encountered no such kids. They are very bright and in no way incapable of better things however from what I know of them from first hand experience they just want to get the job done. Granted I did learn html and css at school however never have I encoutered any other programming languages. We were taught to use the applications as applications without being introduced to code. Hell, I wish we were so that I'd be able to understand most of it however, it's not my fault that we weren't. But despite this, doesn't stop me wanting to be a teacher. I'll help in what way I can.

[diafol]

Like it or not, that's the way I see it. Good luck with the TT, over and out.