CODEWITHSUNDEEP
phphtmlsql
Michael Devlin
Michael Devlin

Reputation: 13

Using html table to order a sql query

I have to organise a table of results by artist and album alphabetically, then track by track number. I have no idea how to go about this, any suggestions would be helpful. This is what i have so far:

$dbQuery=$db->prepare("select tracks.title, albums.title, artists.name, 

basket.id ".
                      "from basket,tracks,albums,artists ".
                      "where basket.userID=:userID ".
                      "and basket.paid='Y' ".
                      "and basket.trackID=tracks.id ".
                      "and albums.id=tracks.albumID ".
                      "and artists.id=tracks.artistID");


$dbParams = array('userID'=>$_SESSION["currentUserID"]);                      
$dbQuery->execute($dbParams);
$numTracks=$dbQuery->rowCount();

if ($numTracks==0)
   echo "<h3>You have not made any purchases</h3>";
else {

?>

   <h2>Your Purchase History</h2>

   <table style="margin-left:15px">
   <tr><th>Artist</th><th>Album</th><th>Track</th></tr>

<?php 

   while ($dbRow=$dbQuery->fetch(PDO::FETCH_NUM)) {

      echo "<tr><td>$dbRow[2]</td><td>$dbRow[1]</td><td>$dbRow[0]</td><td>$dbRow[3]</td></tr>";
   }
}
?>

</table>
</body>

I'm not sure how to do so many orders in the query before it outputs to the html table

EDIT: I couldn't get the order by to work, but turns out i was having syntax issues with my query the way it was appended this worked :

" order by...

This didn't "order by

Upvotes: 1

Views: 64

Answers (1)

void
void

Reputation: 7880

just add order by fields(ASC or DESC) you want, separated by comma statment to end of your query:

select tracks.title, albums.title, artists.name, basket.id
from table_name
....
order by tracks.title, albums.title, artists.name, basket.id

or

order by 1,2,3,4 -- or 2,1,3,4 or 4,1,3,2 or etc

you can also add asc (for ascending order which is default) and dsc (for descending order) in front of columns in order by clause.

Upvotes: 1

Related Questions