CODEWITHSUNDEEP
phpmysql
John
John

Reputation: 171

How to save id in database but to display username on the page

I have image upload system on the site where users can upload images. When user upload new image it's saved his ID in the table which hold images.

Now I want to make when other user click on the user who is uploaded image to load all the images from that user. Currently on the image is showed uploaded by: 1 where 1 is the id of the user. I want to show his username instead of ID.

uploaded by: username

table images -> id, name, image_author

table users -> id, username

This is the query that I use now to show: image, category and uploader

SELECT img.* FROM images img                                          
  JOIN image_category imgCat 
  ON img.img_category = imgCat.image_cat_id                                                                 
  WHERE img.image_active = 'Yes'
  ORDER BY randorder ASC LIMIT $start_from, 20

I've tried to add one more JOIN and to select username from users like this but I guess is wrong since it doesn't show any images..:

SELECT img.* FROM images img                                          
  JOIN image_category imgCat 
  ON img.img_category = imgCat.image_cat_id
  JOIN users usr
  ON img.image_author = usr.username                                                                    
  WHERE img.image_active = 'Yes'
  ORDER BY randorder ASC LIMIT $start_from, 20

UPDATE:

I know that must be usr.id and may be I didn't explain very good. When user add image in tables images under column image_author I store the id of the user. Now with this query when I display images it show Image_author: 1 because I select that column - Uploaded by: $row['image_author']. Question is how to show username instead of user id

Upvotes: 2

Views: 1911

Answers (4)

fpierrat
fpierrat

Reputation: 804

It seems you have your user ID stored in your IMG table, and you're trying to join on your user USERNAME, so MySQL can't join anything which results in a zero rows return...

Also add username in the requested values in your select clause.

Try: 

SELECT img.*, usr.username
FROM images img
JOIN image_category imgCat
    ON img.img_category = imgCat.image_cat_id
JOIN users usr
    ON img.image_author = usr.id
WHERE img.image_active = 'Yes'
ORDER BY randorder ASC LIMIT $start_from, 20

Edit after OP comment:

With preceding request, you can access your username in $row['username'] after fetching your result:
echo("Uploaded by: ".$row['username']);

Upvotes: 2

Mihkel Allorg
Mihkel Allorg

Reputation: 989

You had a typo, you joined the tables on img.image_author = usr.username, but you actually needed to join img.image_author = usr.id. Also you have to add the username in the selected items in the first row.

SELECT img.* , users.username FROM images img                                          
JOIN image_category imgCat 
ON img.img_category = imgCat.image_cat_id
JOIN users usr
ON img.image_author = usr.id                                                                    
WHERE img.image_active = 'Yes'
ORDER BY randorder ASC LIMIT $start_from, 20

Upvotes: 2

Chintan7027
Chintan7027

Reputation: 7605

Your solution may be like:

SELECT img.* FROM images img                                          
  LEFT JOIN image_category imgCat ON img.img_category = imgCat.image_cat_id
  LEFT JOIN users ON users.id= img.image_author
  WHERE img.image_active = 'Yes'
  ORDER BY randorder ASC LIMIT $start_from, 20

Upvotes: 2

Filali Belhadj Zakariae
Filali Belhadj Zakariae

Reputation: 76

when you join a table in a request you need to join it using the foreign key, witch I think in your case is usr.id, not usr.username, so the SQL would be something like this

SELECT img.*, usr.username FROM images img                                          
JOIN image_category imgCat 
ON img.img_category = imgCat.image_cat_id
JOIN users usr
ON img.image_author = usr.id                                                                    
WHERE img.image_active = 'Yes'
ORDER BY randorder ASC LIMIT $start_from, 20

Upvotes: 2

Related Questions