Pulling records from another table.
15 posts by 3 authors in: Forums > CMS Builder
Last Post: October 25, 2010 (RSS)
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
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.
Chris
Re: [chris] Pulling records from another table.
Re: [chris] Pulling records from another table.
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.
Notice: Undefined variable: suitesRecord in /home/davgol20/goldcastleholdings.com/property.php on line 23 Notice: Undefined offset: 0 in /home/davgol20/goldcastleholdings.com/property.php on line 26
Code at the top of my page:
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/home/davgol20/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 = ?', $suitesRecord['agent']),
'limit' => 1,
));
$agentsRecord = $agentsRecords[0];
?>
The code in the body:
<?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 ?>
Re: [Dan Maitland] Pulling records from another table.
By Jason - October 22, 2010
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
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
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
Re: [Dan Maitland] Pulling records from another table.
By Jason - October 22, 2010
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/