Using html table to order a sql query

Question

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

generally ordering is done on the database itself. E.g. select id from user order by id DESC; — Rash
– Rash, Commented Mar 29, 2015 at 23:31

void · Accepted Answer · 2015-03-29 23:34:28Z

1

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.

answered Mar 29, 2015 at 23:34

void

7,9403 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using html table to order a sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related