CODEWITHSUNDEEP
phpmysql
jgengo
jgengo

Reputation: 113

get a User from a table with owner_id

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

Upvotes: 0

Views: 79

Answers (2)

K. Tromp
K. Tromp

Reputation: 360

I think what need is a JOIN statement, try google for left join, outer join, inner join, right join, otherwise you could do a while loop for images and inside that a select query to fetch the user data.

Upvotes: 0

chris85
chris85

Reputation: 23892

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.

Upvotes: 1

Related Questions