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
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
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.
interactivetools.com
Re: [Dave] cross information
By jjem - May 19, 2008
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
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.
interactivetools.com
Re: [Dave] cross information
By jjem - May 19, 2008
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
list($publishersRecords) = getRecords(array(Also, which version are you running (check under: Admin > General)?
interactivetools.com
Re: [Dave] cross information
By jjem - May 19, 2008
Fatal error: Call to undefined function getrecords() inrunning 1.14 version
Re: [oams] cross information
By Dave - May 19, 2008
<?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.
interactivetools.com
Re: [Dave] cross information
By jjem - May 19, 2008
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
Let me know if you need anything else.
interactivetools.com