Select from a select query

Question

I am using this query for a page similar to "instagram" (no I am not trying to make an instagram)

I am using this code for it

<?php
    $query = mysql_query('SELECT * FROM camera_web ORDER BY url ASC LIMIT 50');
    while($row = mysql_fetch_array($query)){
        echo '<img src="'.$row['url'].'" valign="middle" />'.$row['username'].' <br><hr>  <b>'.$row['user_id'].'</b>  <i>'.$row['description'].'</i><br />';
    }
?>

It shows pictures from the table "camera_web" but also the user_id from the table.

When someone publishes a photo I see the photo but not the username, instead it is the user_id, how can I do to make the query select the select "username" from my "users" table where ID = (user_id).

I can't explain any better, if you do not quite understand I'll advance.

You need to JOIN your users table to the query. Example (SELECT camera_web.url, camera_web.description, users.username FROM camera_web LEFT JOIN users ON camera_web.user_id = users.user_id ORDER BY camera_web.url ASC LIMIT 50 — Lucas Krupinski
– Lucas Krupinski, Commented Apr 3, 2017 at 13:49
However, you need to stop using mysql_ methods - they were deprecated in later versions of PHP5 and removed in PHP7 - look into mysqli_ or pdo (which you can also use with MySQL. — Lucas Krupinski
– Lucas Krupinski, Commented Apr 3, 2017 at 13:50
Possible duplicate of How can I join multiple SQL tables using the IDs? — Masivuye Cokile
– Masivuye Cokile, Commented Apr 3, 2017 at 13:52

Alex · Accepted Answer · 2017-04-03 13:52:38Z

2

you join both table using foreign key

select `user_table_name`.`user`, `camera_web`.`url` 
from `camera_web` 
left join `user_table_name` 
on `user_table_name`.`id` = `camera_web`.`user_id` 
order by `camera_web`.`url` asc limit 50

edited Apr 3, 2017 at 13:52

Alex

17.3k1 gold badge31 silver badges52 bronze badges

answered Apr 3, 2017 at 13:51

Jenish

5334 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex · Accepted Answer · 2017-04-03 13:48:21Z

0

You just need to LEFT JOIN users table by user_id:

SELECT cw.* , u.username
FROM camera_web cw
LEFT JOIN users u
ON u.id = cw.user_id
ORDER BY cw.url ASC LIMIT 50

answered Apr 3, 2017 at 13:48

Alex

17.3k1 gold badge31 silver badges52 bronze badges

1 Comment

Omega Cebbo Over a year ago

cw stands for camera_web I guess?

Darshan Mehta · Accepted Answer · 2017-04-03 13:49:42Z

0

You need to use JOIN, e.g.:

SELECT c.*, u.username
FROM camera_web c JOIN users u ON c.user_id = u.id
ORDER BY c.url LIMIT 50;

This would still select both user_id and username, if you don't want user_id, you can replace c.* with comma separated list of fields (e.g. c.id, c.picture etc).

answered Apr 3, 2017 at 13:49

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Collectives™ on Stack Overflow

Select from a select query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related