Help w/ Adding and Averaging of Fields WITHIN Row?

[msmith29063]

Here's what I'm currently doing with PHP/MySQL. I have a query "inside" another query to generate the results table that I need:

SELECT a.albumTitle, a.albumArtist, g.genreName,
a.albumSong1, a.albumSong2, a.albumSong3, a.albumSong4,
a.albumSong5 FROM albums AS a
LEFT JOIN genres AS g ON g.genreID=a.albumGenre
ORDER BY a.albumTitle

while($row = mysql_fetch_array($result)) {

$albumSong1 = $row['albumSong1']; <-- SOME OF THESE MAY BE NULL
$albumSong2 = $row['albumSong2'];
$albumSong3 = $row['albumSong3'];
$albumSong4 = $row['albumSong4'];
$albumSong5 = $row['albumSong5'];

SELECT songTime, songGrade FROM songs WHERE songID=$albumSong1
OR songID=$albumSong2 OR songID=$albumSong3
OR songID=$albumSong4 OR songID=$albumSong5

while($row = mysql_fetch_array($result)) {
songTime++
songGrade++
count++
}

albumTotalTime = songTime <-- DISPLAY TOTAL TIME OF SONGS
albumAverageGrade = songGrade / count <-- DISPLAY AVERAGE GRADE OF SONGS
}
This process works right now. The issue is that I need to add results table column sorting for albumTotalTime and albumAverageGrade.

Is it possible to combine all of this into one query? So that I can add the sorting?

Or any other recommendations?

Note that the songTime fields are in time format.

And note that any of the album song fields could be null. All five fields may not be used.

Thank you so much for your help!

[ssystems]

If you're just starting this application and you have an option to modify the table structures I might say you might be better of making a linking table for the album-album songs relation.


AlbumSongs
-- AlbumSongId
-- AlbumId
-- SongId


If not, I suggest reading on UNION.

[msmith29063]

Actually it's a live application. That why I had to ask this question. It's becoming obvious that I'll need to do some normalization. Once I rework the databases -- I'll revisit the issue. Thanks for your reply.