Getting record from second table

4 posts by 2 authors in: Forums > CMS Builder
Last Post: January 30, 2009   (RSS)

Hi Dave,

First thanks for the outstanding support! Makes it easy to recommend your programs [:)]

Have a probably simple problem that I've tried to find a solution for so much that I'm now really confused! I have a table with many "class" records. I have another with the registration dates for each of those classes. Each can be edited by the user assigned to do such. What I'm trying to do is "import" the cooresponding class dates to display for a particular class at the bottom of the detail form. I've created a variable (list_record) in the class table (in this case called "evoc") that the user adds after adding the list of dates in the "registration" table which is the actual record number of the registration record. I've tried listing both in the Detail record for the class with hopes of displaying the dates from the registration table in the bottom but can't seem to make the registration table go to the class variable "list_record" to display the information in that [registration] record.

Here's what I have at the top of the class detail file:

<?php

require_once "/home/content/a/l/a/alameda582/html/_rtc2009/lib/viewer_functions.php";

list($evocRecords, $evocMetaData) = getRecords(array(
'tableName' => 'evoc',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$evocRecord = @$evocRecords[0]; // get first record

list($registrationRecords, $registrationMetaData) = getRecords(array(
'tableName' => 'registration',
'limit' => '1',
));
$registrationRecord = @$registrationRecords[$evocRecord['list_record']]; // have the registration table go to the list_record variable (1, 2... x) ??

?>

Then in the display in the class (evoc) detail I have:

<?php echo $evocRecord['content'] ?><br/>


<?php foreach (range(1,50) as $num): ?>
<?php $stringA = $registrationRecord["list$num"] ?>
<?php $toFind = " " ?>
<?php if ([url "mailto:!@$registrationRecord["list$num"]!@$registrationRecord["list$num[/url]"]) { break; } ?>
<option><?php echo strrev( strchr(strrev($stringA),$toFind) ) ?><br/></option>
<?php endforeach ?>

<?php if (!$registrationRecord): ?>
(Pending)<br/><br/>
<?php endif ?>


All I get is the first record dates from the registration table regardless of what class record is displayed. I just can't figure out how to have the registration table go to the class variable "list_record" to list the dates within that record.

I hope I wasn't too confusing...

Eric

Re: [eduran582] Getting record from second table

By Dave - January 30, 2009

Hi Eric,

So it's returning all the records from 'registration' but you only want some right?

Which records do you want? As in, do you want record where the registration field 'classNum' equals the value in $evocRecord['num']? If so, there where for that would be as follows:

...
'tableName' => 'registration',
'where' => " classNum = '" .mysql_real_escape_string($evocRecord['num']). "' ",
...

Basically, the first step is to figure out the rule you want to filter on. For example "I want to show records from this table where field "x" equals the value of field "y" from this other record I've already loaded. Then write that as a where.

I hope that helps. Let me know how it goes.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Getting record from second table

Hi Dave,

Your example was great! I changed it a bit but the result was just what I wanted. It was all there in the 'where' syntax. In your example:

[font "Verdana"]'where' => " classNum = '" .mysql_real_escape_string($evocRecord['num']). "' ",

[font "Verdana"]I used:

[font "Verdana"]'where' => " num[/#800000] = '" .mysql_real_escape_string($evocRecord['list_record'[/#800000]]). "' ",

What I was trying to do, which I couldn't have figured out without your help, was to say "I want the record 'num' from table 'registration' (that has all the dates) that is indicated in the variable 'list_record' in the 'evoc' table for use in the record I'm displaying (to show the dates).

Thanks for again for the outstanding support! [;)]

Eric