displaying results from a join

stuartco

Hiya,

I'm joining some tables ->

tbl_Author
AuthorID AuthorName

1 Potsy Mcabre
2 Jack Mckeown
3 Ian Swift

tbl_Books
BookID BookName
1 Five Go Fishin in Florida
2 How I learned to stop worryin and love the bomb
3 You can't clean a window with a spade

tbl_Author_Books_Linker
AuthorID BookID
1 1
1 2
1 3

My SQL statement:

select a.authorname, b.bookname from tbl_Author a
inner join
tbl_Author_Books_Linker c on c.authorID = a.authorID
inner join
tbl_Author_Books b on b.BookID = c.BookID

This works and gives me three results rows eg:

Potsy Macbre Five Go Fishin in Florida
Potsy Macbre How I learned to stop worryin and love the bomb
Potsy Macbre You can't clean a window with a spade

When I come to display the results, I only want to display the authors name once, but list the books they have written eg

Potsy Macbre
Five Go Fishin in Florida
How I learned to stop worryin and love the bomb
You can't clean a window with a spade

As I've got lots of authors in my table, I'm using:

while($row = mysql_fetch_array($result)){
//write out author results
//write book results
}

but as I said it prints out three results sets, and I only really want one results set per author containing all the books they have written.

I've tried using DISTINCT and GROUP by, but it's not giving the desired effect. Any ideas?

Cheers,
Stuart😕

dotbob

there is one way but it is a bit of a hack.

firstly return all distinct authors in your authors table.

then cycle through this array and query the database to return all records pertaining to that authors ID by appending a WHERE clause to the end of your SQL.

stuartco

Cheers for replying dotbob, yer a good egg.

I'm doing what you suggested the now, and it's working fine - but you're right, it's a wee bit messy.

Is this solution the only way out of the quagmire? Have I designed my database in a strange way or do coders in general have this problem d'know?

Using your solution involves another query on the database - it's something I wanted to avoid just wanted to keep things tidy, but thanks for your reply.

Cheers,
Stuart😃

dotbob

Your welcome.

There probably is a better way around the prob but I've had far far far tooooo much coffee and pro plus (regular monday prescription) to think straight. Plus it's 4.30pm and time for me to leave for the day 😃

I'll have another look for you tomorrow morning when I'm more awake.

Cheers.

broc7

There's nothing wrong with the sql. The solution is in what you do with the results once they're returned. Here's how it could be done:

$prev_author = "";
while($row = mysql_fetch_array($result)){
$current_author=mysql_result(author);
$book = mysql_result(book);
if ($current_author != $prev_author) {
echo "<h1>$current_author</h1>";
}
echo "<p>$book</p>";
$prev_author=$current_author;
}

...or code to that effect.

This will print out every book but won't print an author's name if it was just printed. You do have to make sure that all of an author's records are consecutive in the resultset for this to work properly.

dotbob

exactly what I was going to say...

...honest 😉

stuartco

Cheers for replying broc7 and dotbob, this solution is perfect.

Ta,
Stuart