I have a problem with one of my BDD. I have 4 tables linked together:
1: Artists
id
name
first_name
pseudo
mail
...
2: works
id
Artist (value Artists.id)
title
format
3: styles
id
name
(example 1: paint; 2: Photo 3: Textile ,...)
4: categories (table connecting to works - styles)
work
style
(a work which may belong to different styles like painting + photo)
List of different styles with names of artists with at least one work in this style:
Pottery
Smith, John
Painting
Smith, John
Chappuis, Martin
Photography
Dubois, Claude
Niepce, Nicephore
Code:
// create a SQL query for the list of categories
$query = "SELECT * FROM categories ORDER BY name";
$result = mysql_query ($query) or die ("Problem of the request. Error:".mysql_error());
while ($line = mysql_fetch_array ($ result)) {
echo "\n<p>$line[name]</p>";
// create a SQL query for the list of artists
$ query2 = "SELECT DISTINCT a.id, a.name, a.pseudo FROM ((works o LEFT JOIN l_works_cat l ON o.id=l.loc_work) LEFT JOIN artists ON o.artist = a.id) WHERE l.loc_cat = $line [id] ";
$ result2 = mysql_query ($ query2) or die ( "Problem of the request. Error:". mysql_error ());
while ($ line2 = mysql_fetch_array ($ result2)){
echo "\n <a <p> href='artists.php?id=$line2[id]'> $ line2 [name], $ line2 [firstname] </ a> </ p>";
}
}
My problem:
I would like for the names of artists, if a username is defined, the name displayed and not the name. That's easy. The concern is to sort alphabetically by artist name or pseudo. I guess going through a UNION query but I do not see how to integrate. Help me?
Bookmarks