what i'm trying to accomplish is byartist.php. a page that displays an alphabetical list of artists in my mysql database (db.artists) and underneath each artist, and displays the albums that i've reviewed (db.reviews) underneath the names of their respective artists. The albumname is a link to the respective albumreview via url parameter. e.g., the blah band blahs greatest hits (links to albumkey=1) blah blah (links to albumkey=4) etc. heres my db setup relative to this particular issue: db.reviews: albumkey (pk) artistkey (fk) albumtitle db.artists: artistkey (pk) artistname my recordset (per some help from a php/sql newsgroup) is pulled from the following syntax: SELECT artists.artistkey, artists.artistname, reviews.albumkey, reviews.albumtitle FROM artists JOIN reviews ON artists.artistkey = reviews.artistkey PHP: any help is greatly appreciated. also if you have any comments on my db setup please feel free to offer any suggestions, this is my first time working with mysql and php and im still (obviously) learning about organization and normalization. the guy who gave the advice on the mysql query on the newsgroup mentioned something about having the albumname on the reviews table, saying that it should go on an albums table. but he hasn't come around yet to explain why. i told him because in this case its not a one to many relationship in that one album is only going to have one review, so i keep it tied to the reviews table, whereas one artist can have many albums that have reviews so they have their own table. if there's a huge flaw though i'd appreciate a heads up
Do you have any code at all? Post or link a html template that shows how you want the results. Also show your full table scheme (a real scheme with column types) so I see what I am working with. Then also tell me your MySQL Version (full version) 4.?.?! After you have done that I will write you a quick example that you can build on. jb
Sure. http://sippin.net/test/ Test page, Album Reviews listed by Artist On the real site of course this page isnt the index, I just named it that for simplicitys sake here, this would be byartist.php. The rest is self explanatory, left side nav, right side pulling the last 3-5 reviews entered into mysql db, header and footer. SELECT artists.artistid, artists.artistname, reviews.albumid, reviews.atitle FROM artists JOIN reviews ON artists.artistkey = reviews.artistkey PHP: Theres the recordset query I was advised to use and heres my scheme. albumreviews.areviews: albumid (int 3) primary key (unsigned auto-inc) atitle (varchar 100) agenre (varchar 40) areview (longtext) alitereview (text) alabelname (varchar 100) alabelsite (varchar 255) allow null aimg (varchar 40) artistid (int 3) foreignkey (unsigned) yearid (int 4) foreignkey (unsigned) albumreviews.artists: artistid (int 3) primary key (unsigned auto-inc) artistname (varchar 255) artistsite (varchar 255) allow null I also have a table for tracklists and one for yearids (list of years from 1900 to 2005 so that i can pull reviews by year as well), but these are the two that are pertinent to this issue. The MySQL version on my test server (laptop) is 4.1.14, and my rubbish host has their pages so cluttered with crap that I can't find out what version they use on their servers.