cross information

18 posts by 5 authors in: Forums > CMS Builder
Last Post: June 1, 2009   (RSS)

By jjem - May 17, 2008 - edited: May 17, 2008

Hi,

just purchased and have following question:

I have a "publishers" table with Name field and URL field.

Then I have a "works" table.
In the "works" table there is a field (list with "get options from database") from where I can select a publisher by name.

However this only brings the publisher's Name into the work information. On the front end, I would like to display the publisher Name with an <a href="publisherUrl from the publisher's table" /> into the worksPage.php information

Thanks for helping

Re: [oams] cross information

By Dave - May 19, 2008

Hi oams,

There's no automatic way to do that (show fields from two tables that are associated). There's a few ways to do it though, the simplest is as follows:

Make sure your field stores the publisher num and not name, so we can use it to lookup the publisher record.

Next, inside the foreach tag load the publisher record like this (Note: your $variable names may be different):


<?php foreach ($worksRecords as $worksRecord): ?>

<?php
list($publishersRecords) = getRecords(array(
'tableName' => 'publishers',
'where' => "num = '{$worksRecord['publisherNum']}'",
'limit' => '1',
));
$publisherRecord = @$publishersRecords[0]; // get first record
?>

Publisher Name: <?php echo $publisherRecord['name']; ?>


...

<?php endforeach ?>


Basically we're just looking up the publisher record by num and making it available in the $publisherRecord variable to display.

Does that make sense? Let me know if that will work for you or if you need something else.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] cross information

By jjem - May 19, 2008

Hi Dave,

Thanks for the hint.

Make sure your field stores the publisher num and not name, so we can use it to lookup the publisher record.


this sound good to me. Not being an expert in programming, how do I store the num field from the publishers table?

I will not be able to remember which num which publisher has when entering a new work? So the list would have to show the publisher's name and store the publisher's num. Is that correct and how do I do this?

Thanks for helping,
Jeremy

Re: [oams] cross information

By Dave - May 19, 2008

Jeremy,

When you setup the list field use these options:

List Options: Get Options from Database
Use this field for option values: num
Use this field for option labels: name (or whatever your field is called)

The "label" is what is displayed in the pulldown, the "value" is what is stored. So you'll see publishers name but the num will be stored.

Hope that makes sense. Let me know if you need more help or details.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] cross information

By jjem - May 19, 2008

Dave,

after trying your solution I get this error message:


Page Viewer (publishers) errors
Unknown option 'limit' specified. Valid option names are: (tableName, recordNum, where, titleField, orderBy)

any idea!

Thanks

Re: [oams] cross information

By Dave - May 19, 2008

Make sure the code says getRecords not getRecord:

list($publishersRecords) = getRecords(array(

Also, which version are you running (check under: Admin > General)?
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] cross information

By jjem - May 19, 2008

I get this:


Fatal error: Call to undefined function getrecords() in

running 1.14 version

Re: [oams] cross information

By Dave - May 19, 2008

Sorry, that code was for the latest version. Here's some code that will work in 1.14:


<?php foreach ($worksRecords as $worksRecord): ?>

<?php
$options = array();
$options['tableName'] = 'publishers';
$options['recordNum'] = $worksRecord['publisherNum'];
$publisherRecord = getRecord($options);
?>

Publisher Name: <?php echo $publisherRecord['name']; ?>

...

<?php endforeach ?>


Let me know how it goes.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] cross information

By jjem - May 19, 2008

Dave,

Just upgraded to version 1.15. Used your code and everything just works fine! Thanks a lot!

By the way I wanted to compliment on the cmsBuilder. It is just a great tool, I 'll be using it more.

Best,
Jeremy

Re: [oams] cross information

By Dave - May 19, 2008

Thanks! And glad to hear it's working. :)

Let me know if you need anything else.
Dave Edis - Senior Developer
interactivetools.com