从owner_id的表中获取用户

Question

I'm writing a snippet in PHP that get all images from a table "pic" with 3 columns :

1. id
2. owner_id
3. name

So I wrote this :

try
{
    $db = new PDO($DB_DSN, $DB_USER, $DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // to get an exception when caught an error :)
} catch (PDOException $e) {
    print "Erreur !: " . $e->getMessage() . "<br/>";
    die();
}

$find_pic = $db->prepare("SELECT * FROM camagru_jgengo.pic ORDER BY id DESC");
$find_pic->execute();
$pics = $find_pic->fetchAll();

And I made a while to show all images from my database. But I've got a problem, under the image I would like to show the username of the guy who sent the image.

And I've a table 'user' with columns : id and login.

I would like my script to make an array with key (id) => (login). But I tried a lots of things, I don't succeed to build it. Or if you have a better idea I'm all open :)

Thank you

Answer 1

Use a join and you can get all information you want with one query. Roughly:

SELECT p.*, u.name 
FROM camagru_jgengo.pic as p
join users as u on u.id = p.ownerid
ORDER BY p.id DESC

You'll need to modify the columns to match your actual column/table names. If a picture can be uploaded without an owner you might need to make this a left join.