Hello,
I'm trying to make a query that's really confusing me and despite going through and reading several examples, I still can't make a query that works very well for me.
By the way, I have four tables here:
I would like to select the latest downloaded music and have information about their corresponding authors (I know how to do that very well with joins).
The problem I have lies in the structure of the database that I have and that unfortunately I cannot change. Each piece of music has an author and may or may not have a co-author.
So, I would like to make a single request where I select the last 5 music downloaded and have the name and ID of the main author of the music but also the names and ID of the co-authors if there are any.
Before, I did this operation in two stages and it worked well; but this time I want to do it all at once. Is it possible ?
Here's what my query looks like so far:
$query = "
SELECT COUNT(dw.id_music) as occurenceValue,
ar.id as idArtist, ar.firstName, ar.secondName,
mu.title
FROM download dw
INNER JOIN music mu
ON mu.id = dw.id_music
INNER JOIN artist ar
ON ar.id = mu.artist_id
WHERE dw.d_date >= ?
GROUP BY dw.id_music
ORDER BY occurenceValue DESC
LIMIT 5
";
I would like to get a result like:
array (
array (
'id'=>1,
'title'=>'Stay with me',
'id_author'=>5,
'name_author'=>'Mike Five',
'co-authors'=>array('6'=>'Emmy Six', '4'=>'Jennifer Four'),
'downloads'=>3
),
array (
'id'=>4,
'title'=>'Astronomia 4k',
'id_author'=>3,
'name_author'=>'Sandra Three',
'co-authors'=>array('6'=>'Emmy Six', '2'=>'Peter Two'),
'downloads'=>2
),
array (
'id'=>5,
'title'=>'In the jungle',
'id_author'=>1,
'name_author'=>'John One',
'co-authors'=>array(),
'downloads'=>2
),
array (
'id'=>6,
'title'=>'Stay with me',
'id_author'=>1,
'name_author'=>'John One',
'co-authors'=>array(),
'downloads'=>2
),
array (
'id'=>2,
'title'=>'Stay with me',
'id_author'=>1,
'name_author'=>'John One',
'co-authors'=>array('3'=>'Sandra Three'),
'downloads'=>1
)
)
Would you help me please?