Pulling records from another table.

15 posts by 3 authors in: Forums > CMS Builder
Last Post: October 25, 2010   (RSS)

Ok I will try and make this as clear and to the point as possible.

I have a Category section called "Buildings" and I have a multi section called "Agents". when creating a new building I have a drop down list in the admin section that calls each of the agents into it from the agents table so I can associate an agent to a building. Now when you are on the front end and you are viewing the Building details page I want to be able to display the Agent's info that is associated to that building. I was able to display "Suites" associated to a building but I cant seem to get the agents to display. Here is my code:

--- This is at the Top of the page ---

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php


// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/xxx/xxx/xxx.com/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

list($buildingsRecords, $buildingsMetaData) = getRecords(array(
'tableName' => 'buildings',
'limit' => '0',
));
$buildingsRecord = $buildingsRecords[0];
list($suitesRecords, $suitesMetaData) = getRecords(array(
'tableName' => 'suites',
'where' => 'building = "' . mysql_escape($buildingsRecord['building_name']) . '"',
));

list($agentsRecords, $agentsMetaData) = getRecords(array(
'tableName' => 'agents',

));


?>

--- This is what I'm trying to display ---

<?php foreach ($agentsRecords as $record): ?>
<?php echo $record['name'] ?>
<br />
T: <?php echo $record['phone'] ?> | F: <?php echo $record['fax'] ?> | E: <a href="mailto:<?php echo $record['email'] ?>"><?php echo $record['email'] ?></a>
<?php endforeach ?></div>

Re: [Dan Maitland] Pulling records from another table.

By Chris - October 20, 2010

Hi Dan,

Assuming your 'buildings' section has an 'agent' List Field set up like this:

Display As: pulldown
List Options: Get options from database (advanced)
Section Tablename: agents
Use this field for option values: num
Use this field for option labels: name


... you should be able to get your building's agent like this:

list($agentsRecords, $agentsMetaData) = getRecords(array(
'tableName' => 'agents',
'where' => mysql_escapef('num = ?', $buildingsRecord['agent']),
'limit' => 1,
));
$agentsRecord = $agentsRecords[0];


Does that help? Please let me know if you have any questions.
All the best,
Chris

Re: [chris] Pulling records from another table.

Once again Chris you nailed it on the first shot. Thank you so much. I have to say that even though you guys charge $199 for the CMS Builder I think that the support that you guys give is worth that alone and more. Keep up the great work!

Re: [chris] Pulling records from another table.

Chris I did exactly like you said and it was working perfectly fine.... then just like that with out touching anything(I'm pretty sure) it just stopped working. The agents info stopped displaying and i get this message at the top of mu page.

Notice: Undefined offset: 0 in /xxx/xxx/goldcastleholdings.com/property.php on line 27

This is the code that I have in line 27:
$agentsRecord = $agentsRecords[0];

This is all the code i have sitting at the top of the page.

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php


// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/xxx/xxx/goldcastleholdings.com/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

list($buildingsRecords, $buildingsMetaData) = getRecords(array(
'tableName' => 'buildings',
'limit' => '0',
));
$buildingsRecord = $buildingsRecords[0];
list($suitesRecords, $suitesMetaData) = getRecords(array(
'tableName' => 'suites',
'where' => 'building = "' . mysql_escape($buildingsRecord['building_name']) . '"',
));

list($agentsRecords, $agentsMetaData) = getRecords(array(
'tableName' => 'agents',
'where' => mysql_escapef('num = ?', $buildingsRecord['agent']),
'limit' => 1,
));
$agentsRecord = $agentsRecords[0];
?>

Re: [Dan Maitland] Pulling records from another table.

By Jason - October 22, 2010

Hi Dan,

Your error is coming from this code (highlighted in red):

list($suitesRecords, $suitesMetaData) = getRecords(array(
'tableName' => 'suites',
'where' => 'building = "' . mysql_escape($buildingsRecord['building_name']) . '"',
));
list($agentsRecords, $agentsMetaData) = getRecords(array(
'tableName' => 'agents',
'where' => mysql_escapef('num = ?', $suitesRecord['agent']),
'limit' => 1,
));


At this point in the code, you do have a variable called $suitesRecords, but not one called $suitesRecord. If you just want to use the first record that you retrieved from the suites table, try changing your code to this (highlighted in blue)

list($suitesRecords, $suitesMetaData) = getRecords(array(
'tableName' => 'suites',
'where' => 'building = "' . mysql_escape($buildingsRecord['building_name']) . '"',
));

$suitesRecord = $suitesRecords[0];

list($agentsRecords, $agentsMetaData) = getRecords(array(
'tableName' => 'agents',
'where' => mysql_escapef('num = ?', $suitesRecord['agent']),
'limit' => 1,
));


Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Pulling records from another table.

By DanMaitland - October 22, 2010 - edited: October 22, 2010

Jason,
I'm not sure you understand what I am trying to do because the suites I am calling are working. It's the Agent records that are not displaying. I explained what was happening in my earlier posts.

I am trying to pull in the agent records that are associated with the building through a list menu that I created in the buildings section. I got it to work on the Suites page but for some reason the same code won't work on the building page.

Re: [Dan Maitland] Pulling records from another table.

By Jason - October 22, 2010

Hi,

I'm seeing a couple of things in your code. First, the query you're using to select your building is using 'limit' => 0. This means that it won't return anything. Since you're other queries are based on it, you won't be getting any results.

Next, your code seems to be set up to select only 1 agent. If your building query did return a result, you would select the first suite associated with that building and then the agent associated with that suite. Is that what you want to do? Are agents associated with only suites, or are they associated with buildings as well?

Let me know and we'll see what we can do.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Pulling records from another table.

By DanMaitland - October 22, 2010 - edited: October 22, 2010

They are associated with buildings as well.

Re: [Dan Maitland] Pulling records from another table.

By Jason - October 22, 2010

Okay, in that case you don't need to query the suites table at all. Try this code:

list($buildingsRecords, $buildingsMetaData) = getRecords(array(
'tableName' => 'buildings',
'limit' => '1',
));
$buildingsRecord = $buildingsRecords[0];

list($agentsRecords, $agentsMetaData) = getRecords(array(
'tableName' => 'agents',
'where' => mysql_escapef('num = ?', $buildingsRecord['agent']),
'limit' => 1,
));
$agentsRecord = $agentsRecords[0];


Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/