SELECT

[erikjan]

Can someone please give me a hand? Thanks in advance!

I have a database with artists and their artworks etc.

I have a website in which, among others, there is a menu
consisting of the alfabet, so visitors can click on an initial letter
to find for example artists whose surname starts with the initial letter A.
So when they click the letter A:

<?php
$sql="SELECT * FROM objects WHERE artist_surname LIKE [...etc...] 'a%' ORDER BY artist_surname ASC [...etc...]";
$result=mysql_query($sql);

while ($row=mysql_fetch_assoc($result)) {
  if ($row['artist_surname '] != $artist_surname ) {  // has artist name changed?
  $artist_surname = $row['artist_surname '];

  [...etc...]

So the artists are ordered asc by surname, and if an artist has more than one work,
his name  is not repeated.


Result, for example:

Artist no.1
title work no.1 by artist (year)
title work no.2 by artist (year)


Artist no.2
title work no.1 by artist (year)
title work no.2 by artist (year)


Artist no.3
title work no.1 by artist (year)


If they click the work, they get additional info etc.




What I would like is that the works displaced by each individual artist
als also ordered - this time by date.

So, first the artists are ordered by surname, and then, within each individual artist
I would like to order the works by year.


Can someone help me how to do that?

[craygo]

Here is something quick. Change values to suit your table structure
[code]<?php
/********** Connect to database here  ***************/

/**********************************************/
// Print Headers
print '<table width="500" align="center" cellspacing=0 cellpadding=0>';
print '<tr bgcolor="F98F5B">';
print '<td width=300 align=center>field1</td>';
print '<td width=100 align=center>field2</td>';
print '<td width=100 align=center>date</td>';
print '</tr>';
print '</table>';
// Set initial group values
$lastname = '';
// Query database
$sql = "SELECT * FROM objects WHERE artist_surname LIKE 'a%' ORDER BY artist_surname, date ASC";
  $res = mysql_query($sql);
  $num_rows = mysql_num_rows($res);
if($num_rows > 1){
// Set initial row color
$bgcolor = "FFFFFF";
    while ($rows = mysql_fetch_assoc($res)){
$artist_surname = $row['artist_surname'];
// Print Group Total
if ($artist_surname != $lastname) {
// Print Group Name
print '<table width="500" align="center" cellspacing=0 cellpadding=0>';
print '<tr bgcolor=83EA44>';
print '<td colspan=3 align=left><strong>'.$artist_surname.'</strong></td>';
print '</tr>';
print '</table>';
}
// Alternate row color
if ($bgcolor == "#E0E0E0"){
  $bgcolor = "#FFFFFF";
} else {
  $bgcolor = "#E0E0E0";
}
// Print Database Details
print '<table width=500 align=center cellspacing=0>';
print '<tr bgcolor='.$bgcolor.'>';
print '<td width=300 align=center>'.$rows['field1'].'</td>';
print '<td width=100 align=center>'.$rows['field2'].'</td>';
print '<td width=100 align=center>'.$rows['date'].'</td>';
print '</tr>';
// Reset group values
$lastname = $artist_surname;
}
} else {
// Print No data message
print '<table width=650 align=center>';
print '<tr>';
print '<td align=center><strong>NO ITEMS!!</strong></td>';
print '</tr>';
print '</table>';
}
?>[/code]

[erikjan]

Thank you so much, craygo.

ORDER BY artist_surname, [u][b]date[/b][/u]

The thing I was looking for!!