There is such a request:

SELECT * FROM album, competition WHERE album.time_ >= competition.dateStart AND album.time_ <= competition.dateEnd AND competition.end_ = 0 ORDER BY album.rating DESC LIMIT 3

The album and competition table have the same id column. How to get the id the album table?

I tried this:

 $result['album']['id'];

So:

 $result[0]['id'];

And even so:

 $result['album.id'];

But it's not right. Tell me how?

  • choose not select *, but explicitly list all the required fields. including id, choose by explicitly assigning him another name (alias) select album.id as album_id from ... and in php it will be named album_id - Mike
  • Oh, thank you! :) - Amandi

1 answer 1

 try{ $db = new PDO('mysql:host=localhost;dbname=test;charset=utf8','root',''); $query = "SELECT a.time_, c.dateStart, c.dateEnd, c.end_, a.id AS album_id, c.id AS competition_id FROM album AS a, competition AS c WHERE a.time_ >= c.dateStart AND a.time_ <= c.dateEnd AND c.end_ = 0 ORDER BY a.rating DESC LIMIT 3"; $query=$db->prepare($query); $query->execute(); $result = $query->fetchAll(PDO::FETCH_ASSOC); print_r($result); }catch(PDOException $e ){ echo "Error: ".$e; }