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, Apr 03 '17 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, Apr 03 '17 at 13:50
Possible duplicate of [How can I join multiple SQL tables using the IDs?](http://stackoverflow.com/questions/9853586/how-can-i-join-multiple-sql-tables-using-the-ids) — Masivuye Cokile, Apr 03 '17 at 13:52

score 2 · Accepted Answer · edited Apr 03 '17 at 13:52

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 03 '17 at 13:52

Alex

16,739
1
28
51

answered Apr 03 '17 at 13:51

Jenish

535
4
16

score 0 · Answer 2 · answered Apr 03 '17 at 13:48

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 03 '17 at 13:48

Alex

16,739
1
28
51

cw stands for camera_web I guess? – Omega Cebbo Apr 03 '17 at 13:49

score 0 · Answer 3 · answered Apr 03 '17 at 13:49

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).

Select from a select query

3 Answers3