erikjan Posted December 21, 2006 Share Posted December 21, 2006 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 menuconsisting of the alfabet, so visitors can click on an initial letterto 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 artistals also ordered - this time by date.So, first the artists are ordered by surname, and then, within each individual artistI would like to order the works by year.Can someone help me how to do that? Link to comment https://forums.phpfreaks.com/topic/31502-select/ Share on other sites More sharing options...
craygo Posted December 21, 2006 Share Posted December 21, 2006 Here is something quick. Change values to suit your table structure[code]<?php/********** Connect to database here ***************//**********************************************/// Print Headersprint '<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 Totalif ($artist_surname != $lastname) {// Print Group Nameprint '<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 colorif ($bgcolor == "#E0E0E0"){ $bgcolor = "#FFFFFF";} else { $bgcolor = "#E0E0E0";}// Print Database Detailsprint '<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 messageprint '<table width=650 align=center>';print '<tr>';print '<td align=center><strong>NO ITEMS!!</strong></td>';print '</tr>';print '</table>';}?>[/code] Link to comment https://forums.phpfreaks.com/topic/31502-select/#findComment-145911 Share on other sites More sharing options...
erikjan Posted December 21, 2006 Author Share Posted December 21, 2006 Thank you so much, craygo.ORDER BY artist_surname, [u][b]date[/b][/u] The thing I was looking for!! Link to comment https://forums.phpfreaks.com/topic/31502-select/#findComment-145940 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.