What's the best way to achieve this please?

Hi Willy

First of all - yes, two sections is the right way to do it. A good rule of thumb is to think about how much information you'd be duplicating, if you're not sure how to structure your site. If you've got three books with the same author, for example, keeping it all in one section would mean the same info would be duplicated in each book record, and that's not good if you've got to update that info. It makes a lot more sense to have a section where you store the author info, and then use a few extra queries to connect them when you need to. Here's a step by step.

• Make a section for the Author stuff, including name, biography and whatnot.
• Then go to the Book section, and add a List field called Author ID.
• Go to the Field options, and look at the List Options - choose 'Get options from database (advanced)'.
• For the three dropdown menus that appear, choose the Author table, then use 'num' for option values and the author name for option labels.
• Save it.
• Add the author records there, and use the Book section dropdown to set an author for each book.

What this does is give you access to the identifier for the Author on the book's detail page, and you can use this to get the Author's info on that page from the Authors section.

On the book detail page, just before where you insert the 'About the Author' info, add something like this:

<?php
list($authorsRecords, $authorsMetaData) = getRecords(array(
  'tableName' => 'authors',
  'where' => 'num = ' . mysql_escape($bookRecord['author_id']),
  ));
$authorRecord = @$authorsRecords[0];
?>

Now you'll have the author information in $authorRecord, and you can echo it however you need to on the page. (I'm assuming that the info for this book is called $bookRecord, so just change that to whatever it should be.)

The next thing you'll want to do, which is to get a list of books by that author, means using the getRecords function again. So insert this just before you want to add a list of books by the author:

<?php
list($relatedBooksRecords, $relatedBooksMetaData) = getRecords(array(
  'tableName' => 'books',
  'where' => 'author = ' . mysql_escape($authorRecord['num']),
  ));
?>

So this should give you an array of books written by the author whose info you just pulled using the first chunk of code, and they're stored in $relatedBooksRecords. And now you can use a regular foreach loop to list them under the author's info in whatever format you like.

<?php foreach ($relatedBooksRecords as $record) : ?>
    <h3><?php echo $record['title']; ?></h3>
    <p><?php echo $record['publish_date']; ?></p>

    ... insert other info here...
<?php endforeach; ?>

Does this make sense? The end result of all this is that authors only need to add their record once, and then when they add a new book, they just use a dropdown to select their name. It's got some limitations if you have a large number of authors, but it's a simple implementation.

If there's anything you're not sure about, especially things like variables or loops in PHP, just let me know. I can point you to some good resources.

Hi Claire,

Due to the time difference am not in the office at the moment but just had to take the time to say Thank you very much, that info is exactly what I was looking for but didnt dare hope to get such a complete answer.

I will run through everything tomorrow but it looks exactly what I was wanting. Not only will it solve this problem but I can already see it opening doors to so many other possibilities.

Thanks again for all your help.

No problem - let me know if you run into any issues.

Hi Claire,

I had a couple of issues which basically took me about 4 hours to diagnose which was down to my lack of knowledge more than anything elese but they were resolved by simply changing 

 'where' => 'author = ' . mysql_escape($authorRecord['num']),

to 

'where' => 'author_id = ' . mysql_escape($authorsRecord['num']),

but 4 hours or not I was pleased that I had managed to eventually track the issue down and I generated a better understanding of things in the process.

Thanks for all your help.

No problem. Those variable names are probably the only thing you'll need to watch out for.

If you want to get to grips with more of this in depth, check out the PHP tutorial on W3CSchools: http://www.w3schools.com/PHP/

Take a look at for loops, arrays and the if/else stuff. Everything in the basic section is probably worth reading but those are what you'll use more than anything else. Also take a look at the PHP alternative syntax (just another way of running loops etc, which is easier to read when it's mixed up in HTML): http://www.php.net/manual/en/control-structures.alternative-syntax.php CMSBuilder uses this syntax in the Code Generator, so it's good to understand it.

Hi Claire,

Thanks for that, they will be a great source of reference and will probably save hours of head scratching :-)