Getting SQL Erros with relatedlookp code, see thread, Pulling hair out, challenged with viewing data from multiple sections on single viewer page
14 posts by 4 authors in: Forums > CMS Builder
Last Post: March 31, 2010 (RSS)
By Codee - March 27, 2010 - edited: March 29, 2010
Chris (and Dave? or whoever can help!),
Here's my challenge. I have a client's site where she is a golf instructor and the students have individual golf lessons. We have the membership plugin activated, fyi.
There is a lessons section. Lessons are created for each individual student and have their own title but the lesson number is pulled from a list inside the lessons section editor.
When the instructor crafts a lesson for each student, she selects the student's name from a dropdown list (inside the lessons section editor). This way we use the
membership functionality to only show lessons to students that are logged in and each student can only see their individual lessons. The list of lessons (viewer) for each student is on golflessons.php and the individual lesson details are on golflessonDetails.php.
Now, each lesson has a section for suggested homework or drills to do for practice. Since all the drills come from the same list and don't change we created a section called
"drills" and it has a unique numbering and title system (as well as detailed description , photos, etc. for each drill in the list). In the lessons section, when a lesson is created,
the instructor chooses drills that are listed in a dropdown (that comes from the drills section obviously).
When we view the individual lessons we see the names of the drills appear in the lesson and in the correct order...but what we need to do is pull all the drill data (for each selected drill) from the drills section and display it within the viewer for the lesson details.
So, student#1 might have a lesson with practice drills called gripdrill-1, swingdrill-5 and strengthendrill3A...while student#2 might have a lesson with practice drills swingdrill-2, swingdrill-3 and puttingdrill-12. So we want all the drill data for gripdrill-1, swingdrill-5 and strengthendrill3A to appear in student#1's lesson viewable at golflessonDetails.php. And of course have all the drill data for swingdrill-2, swingdrill-3 and puttingdrill-12 appear in student#2's lesson. Does that make sense?
How do we accomplish this?
Thanks!
Here's my challenge. I have a client's site where she is a golf instructor and the students have individual golf lessons. We have the membership plugin activated, fyi.
There is a lessons section. Lessons are created for each individual student and have their own title but the lesson number is pulled from a list inside the lessons section editor.
When the instructor crafts a lesson for each student, she selects the student's name from a dropdown list (inside the lessons section editor). This way we use the
membership functionality to only show lessons to students that are logged in and each student can only see their individual lessons. The list of lessons (viewer) for each student is on golflessons.php and the individual lesson details are on golflessonDetails.php.
Now, each lesson has a section for suggested homework or drills to do for practice. Since all the drills come from the same list and don't change we created a section called
"drills" and it has a unique numbering and title system (as well as detailed description , photos, etc. for each drill in the list). In the lessons section, when a lesson is created,
the instructor chooses drills that are listed in a dropdown (that comes from the drills section obviously).
When we view the individual lessons we see the names of the drills appear in the lesson and in the correct order...but what we need to do is pull all the drill data (for each selected drill) from the drills section and display it within the viewer for the lesson details.
So, student#1 might have a lesson with practice drills called gripdrill-1, swingdrill-5 and strengthendrill3A...while student#2 might have a lesson with practice drills swingdrill-2, swingdrill-3 and puttingdrill-12. So we want all the drill data for gripdrill-1, swingdrill-5 and strengthendrill3A to appear in student#1's lesson viewable at golflessonDetails.php. And of course have all the drill data for swingdrill-2, swingdrill-3 and puttingdrill-12 appear in student#2's lesson. Does that make sense?
How do we accomplish this?
Thanks!
Re: [equinox69] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
By gkornbluth - March 27, 2010 - edited: March 28, 2010
Hi Terry,
Have you played with the free relatedrecordslookup plugin at all?
Here's an excerpt from the next update of my CMSB Cookbook http://www.thecmsbcookbook.com that may help in this situation:
POPULATING PAGES FROM A MASTER “ADDRESS BOOK” OR “PRODUCT LIST”
Let’s say you have a number of address book entries that contain things like name, address, phone, travel directions, map URL, etc. Or you have a number of products with detailed information about them.
Instead of retyping all of your information each time you want to display it on a detail page, you can pull that information directly from your master information list. Chris from Interactive Tools created a “relatedRecordLookupFunctions” plugin to add the required functionality this and suggested the following approach, I modified the table and field names for this “Address Book” example:
MULTI-VALUE LIST FIELDS (THE CODE REQUIRED FOR SINGLE VALUE LIST FIELDS FOLLOWS)
Step 1: Set up your address book section. for this example call it “Venue Address Book”.
This is a standard multi-record section with the necessary information fields. Since this example is an address book of venues for an events listing, I’ve set up fields for Venue Name, Venue Address, Venue Phone, Venue Contact E-mail, Venue URL, Venue URL link text (I like to keep these separate to add flexibility for my clients but I set the default text to WEB SITE or CLICK HERE FOR WEB SITE), Venue Travel Directions, and Venue Map URL (either from Mapquest or Google maps).
Step 2: In the section where you want this information to appear you’ll add a list type field called “Venue” with the following parameters.
Field Label: Venue
Field Name: venue
Field Type: list
Field Options:
Display As: pulldown or checkbox (multi-value)
List Options: Get options from database (advanced)
Section Tablename: venue_address_book
Use this field for option values: num
Use this field for option labels: venue_name
Step 3: Install the Related Record Lookup Functions Plugin which you can download from:
www.thecmsbcookbook.com/downloads/relatedRecordLookupFunctions.zip
Upload the file to your server in the /cmsAdmin/plugins directory, then log into CMSB, go to Admin > Plugins, and click Activate on it.
Step 3: To set up your viewer to display the fields required you'll need to add some code to the top of your page. You probably already have most the code in black in your PHP source code. Just add the code in red:
Step 4: Then in the body, where you want to display your address book fields:
You can display the fields separately but you’ll need a foreach loop for each field or set of fields that you want to display.
SINGLE VALUE LIST FIELDS
According to Chris, “The process works with single value lists too, but the interface is slightly different. The plugin replaces the field with the associated record, instead of a list of records. For this reason, you wouldn't use foreach to loop over the records.”
Here’s how. First, define the variable $venue before it's first use:
Then where you want to echo the fields in your viewer:
And if you wanted to include an “if” statement the code would look like this:
Or you could leave out the define variable step and just use:
Of course, as always, styling is up to you.
Maybe this will help...
Best,
Jerry Kornbluth
_____________________________________
Have you played with the free relatedrecordslookup plugin at all?
Here's an excerpt from the next update of my CMSB Cookbook http://www.thecmsbcookbook.com that may help in this situation:
POPULATING PAGES FROM A MASTER “ADDRESS BOOK” OR “PRODUCT LIST”
Let’s say you have a number of address book entries that contain things like name, address, phone, travel directions, map URL, etc. Or you have a number of products with detailed information about them.
Instead of retyping all of your information each time you want to display it on a detail page, you can pull that information directly from your master information list. Chris from Interactive Tools created a “relatedRecordLookupFunctions” plugin to add the required functionality this and suggested the following approach, I modified the table and field names for this “Address Book” example:
MULTI-VALUE LIST FIELDS (THE CODE REQUIRED FOR SINGLE VALUE LIST FIELDS FOLLOWS)
Step 1: Set up your address book section. for this example call it “Venue Address Book”.
This is a standard multi-record section with the necessary information fields. Since this example is an address book of venues for an events listing, I’ve set up fields for Venue Name, Venue Address, Venue Phone, Venue Contact E-mail, Venue URL, Venue URL link text (I like to keep these separate to add flexibility for my clients but I set the default text to WEB SITE or CLICK HERE FOR WEB SITE), Venue Travel Directions, and Venue Map URL (either from Mapquest or Google maps).
Step 2: In the section where you want this information to appear you’ll add a list type field called “Venue” with the following parameters.
Field Label: Venue
Field Name: venue
Field Type: list
Field Options:
Display As: pulldown or checkbox (multi-value)
List Options: Get options from database (advanced)
Section Tablename: venue_address_book
Use this field for option values: num
Use this field for option labels: venue_name
Step 3: Install the Related Record Lookup Functions Plugin which you can download from:
www.thecmsbcookbook.com/downloads/relatedRecordLookupFunctions.zip
Upload the file to your server in the /cmsAdmin/plugins directory, then log into CMSB, go to Admin > Plugins, and click Activate on it.
Step 3: To set up your viewer to display the fields required you'll need to add some code to the top of your page. You probably already have most the code in black in your PHP source code. Just add the code in red:
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
require_once "/your_path_to/cmsAdmin/lib/viewer_functions.php";
list($e_blast_events_noticeRecords, $e_blast_events_noticeMetaData) = getRecords(array(
'tableName' => 'e_blast_events_notice',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
beta_lookupRelatedFields(array(
'table' => 'e_blast_events_notice',
'recordList' => &$e_blast_events_noticeRecords,
'fieldList' => array( 'venue' )
));
$e_blast_events_noticeRecord = @$e_blast_events_noticeRecords[0]; // get first record
// show error message if no matching record is found
if (!$e_blast_events_noticeRecord) {
header("HTTP/1.0 404 Not Found");
print "Record not found!";
exit;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">Step 4: Then in the body, where you want to display your address book fields:
<?php if (empty($e_blast_events_noticeRecord['venue'])): ?>
No Venue Information is available.
<?php else: ?>
<div align="left"> Venue Information: <br />
<?php foreach ($e_blast_events_noticeRecord['venue'] as $venue): ?>
<?php echo $venue['venue_name'] ?><br />
<?php echo $venue['venue_address'] ?><br />
<?php echo $venue['venue_contact_e_mail'] ?> <br />
<?php echo $venue['venue_phone'] ?><br />
Directions: <br /><?php echo $venue['venue_travel_directions'] ?> <br />
<a href="<?php echo $venue['venue_map'] ?>">CLICK HERE FOR A MAP</a> <br />
<a href="<?php echo $venue['venue_url'] ?>"><?php echo $venue(venue_url_link_text); ?></a><br/>
<?php endforeach ?>
</div>
<?php endif ?>You can display the fields separately but you’ll need a foreach loop for each field or set of fields that you want to display.
SINGLE VALUE LIST FIELDS
According to Chris, “The process works with single value lists too, but the interface is slightly different. The plugin replaces the field with the associated record, instead of a list of records. For this reason, you wouldn't use foreach to loop over the records.”
Here’s how. First, define the variable $venue before it's first use:
<?php $venue = $e_blast_events_noticeRecord['venue']; ?>Then where you want to echo the fields in your viewer:
<?php echo $venue['venue_name']; ?>And if you wanted to include an “if” statement the code would look like this:
<?php if ($venue['venue_name']): ?>
<?php echo $venue['venue_name']; ?>
<?php endif; ?> Or you could leave out the define variable step and just use:
<?php echo $e_blast_events_noticeRecord['venue']['venue_name']; ?>Of course, as always, styling is up to you.
Maybe this will help...
Best,
Jerry Kornbluth
_____________________________________
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
I followed this tutorial to the T and yes it does display the contents from the other table but it always chooses the same record despite what I choose in the list menu. Am I missing something?
Re: [equinox69] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
By gkornbluth - March 28, 2010
Hi Terry,
I mistakenly posted the plugin without zipping it first.
The download link works now.
Sorry
Jerry
I mistakenly posted the plugin without zipping it first.
The download link works now.
Sorry
Jerry
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
By Codee - March 28, 2010
Hi Jerry! Great idea to try that. I actually paid to have the plugin built before it was released months ago and had it uploaded to that system already. However, I'm not real good at the MySQL programming gig...complete neophyte in fact. But it's a good idea and since the IT offices are closed for the weekend I'll give it a try and post my results here. Thanks for the idea to use what's in front of me!
GETTING ERRORS...conflict with membership plugin?Re: [gkornbluth] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
By Codee - March 29, 2010
IT, Jerry and Gang,
I tried to follow the steps as outlined. I'm getting the following SQL error in my browser:
"MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOOT DRILL, GAP--GRIP- ALIGNMENT- POSTURE) AND drills.createdByUserNum IN (SELEC' at line 3 "
I suspect the viewer code for utilizing the membership plugin to only show certain content to the logged in user is conflicting with Chris's RelatedLookup plugin?
My code snippets (with some names changed for security reasons):
on the lesson listings page at the top:
<?php require_once "pathtodomain/public_html/lifgAdmin/lib/viewer_functions.php"; ?>
<?php if (!$CURRENT_USER) { websiteLogin_redirectToLogin(); } ?>
<?php include_once "pathtodomain/public_html/spambot-email-protector.php" ?>
<?php
require_once "pathfordomain/lifgAdmin/lib/viewer_functions.php";
list($lessonsRecords, $lessonsMetaData) = getRecords(array(
'tableName' => 'lessons',
'orderBy' => 'num ASC, title',
'where' => " (ownerUserNum = '{$CURRENT_USER['num']}') OR '{$CURRENT_USER['isAdmin']}' ",
));
$lessonsRecord = @$lessonsRecords[0]; // get first record
// load referenced records for our fields
beta_lookupRelatedFields(array(
'table' => 'lessons',
'recordList' => &$lessonsRecords,
'fieldList' => array( 'drill' )
));
?>
Then on the lessons detail page the following -
viewer header:
<?php require_once "pathtodomain/lifgAdmin/lib/viewer_functions.php"; ?>
<?php if (!$CURRENT_USER) { websiteLogin_redirectToLogin(); } ?>
<?php include_once "pathtodomain/public_html/spambot-email-protector.php" ?>
<?php
require_once "pathtodomain/public_html/lifgAdmin/lib/viewer_functions.php";
list($lessonsRecords, $lessonsMetaData) = getRecords(array(
'tableName' => 'lessons',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$lessonsRecord = @$lessonsRecords[0]; // get first record
// load referenced records for our fields
beta_lookupRelatedFields(array(
'table' => 'lessons',
'recordList' => &$lessonsRecords,
'fieldList' => array( 'drill' )
));
// show error message if no matching record is found
if (!$lessonsRecord) {
header("HTTP/1.0 404 Not Found");
print "That lesson is not currently available. Please click BACK in your browser.";
exit;
}
?>
and then in the body of the viewer page:
<!--begin drill display-->
<?php if (empty($lessonsRecord['drill'])): ?>
No Drills are currently available.
<?php else: ?>
Drill1: <br />
<?php foreach ($lessonsRecord['drill'] as $drills): ?>
<?php echo $drills['title'] ?><br />
<?php echo $drills['description'] ?><br />
<a href="<?php echo $drills['website'] ?>" target="_blank">Click here for a helpful website</a> <br />
<a href="<?php echo $drills['video_link'] ?>" target="_blank"><?php echo $drills(video_link); ?></a><br/>
<?php endforeach ?>
<?php endif ?>
<!--end drill display-->
I tried to follow the steps as outlined. I'm getting the following SQL error in my browser:
"MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOOT DRILL, GAP--GRIP- ALIGNMENT- POSTURE) AND drills.createdByUserNum IN (SELEC' at line 3 "
I suspect the viewer code for utilizing the membership plugin to only show certain content to the logged in user is conflicting with Chris's RelatedLookup plugin?
My code snippets (with some names changed for security reasons):
on the lesson listings page at the top:
<?php require_once "pathtodomain/public_html/lifgAdmin/lib/viewer_functions.php"; ?>
<?php if (!$CURRENT_USER) { websiteLogin_redirectToLogin(); } ?>
<?php include_once "pathtodomain/public_html/spambot-email-protector.php" ?>
<?php
require_once "pathfordomain/lifgAdmin/lib/viewer_functions.php";
list($lessonsRecords, $lessonsMetaData) = getRecords(array(
'tableName' => 'lessons',
'orderBy' => 'num ASC, title',
'where' => " (ownerUserNum = '{$CURRENT_USER['num']}') OR '{$CURRENT_USER['isAdmin']}' ",
));
$lessonsRecord = @$lessonsRecords[0]; // get first record
// load referenced records for our fields
beta_lookupRelatedFields(array(
'table' => 'lessons',
'recordList' => &$lessonsRecords,
'fieldList' => array( 'drill' )
));
?>
Then on the lessons detail page the following -
viewer header:
<?php require_once "pathtodomain/lifgAdmin/lib/viewer_functions.php"; ?>
<?php if (!$CURRENT_USER) { websiteLogin_redirectToLogin(); } ?>
<?php include_once "pathtodomain/public_html/spambot-email-protector.php" ?>
<?php
require_once "pathtodomain/public_html/lifgAdmin/lib/viewer_functions.php";
list($lessonsRecords, $lessonsMetaData) = getRecords(array(
'tableName' => 'lessons',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$lessonsRecord = @$lessonsRecords[0]; // get first record
// load referenced records for our fields
beta_lookupRelatedFields(array(
'table' => 'lessons',
'recordList' => &$lessonsRecords,
'fieldList' => array( 'drill' )
));
// show error message if no matching record is found
if (!$lessonsRecord) {
header("HTTP/1.0 404 Not Found");
print "That lesson is not currently available. Please click BACK in your browser.";
exit;
}
?>
and then in the body of the viewer page:
<!--begin drill display-->
<?php if (empty($lessonsRecord['drill'])): ?>
No Drills are currently available.
<?php else: ?>
Drill1: <br />
<?php foreach ($lessonsRecord['drill'] as $drills): ?>
<?php echo $drills['title'] ?><br />
<?php echo $drills['description'] ?><br />
<a href="<?php echo $drills['website'] ?>" target="_blank">Click here for a helpful website</a> <br />
<a href="<?php echo $drills['video_link'] ?>" target="_blank"><?php echo $drills(video_link); ?></a><br/>
<?php endforeach ?>
<?php endif ?>
<!--end drill display-->
Re: [equinox69] GETTING ERRORS...conflict with membership plugin?Re: [gkornbluth] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
By Chris - March 29, 2010
Hi Terry,
I'll take a closer look when I get into the office tomorrow, but I have a hunch that might help:
Is your drill list field (in your Lessons section) configured to store "num"s as values? If not, you'll need to set that up, and also edit all your existing lesson records, reselecting the drills, and saving the records.
I hope this makes sense! I'll give this a closer look in the morning.
I'll take a closer look when I get into the office tomorrow, but I have a hunch that might help:
Is your drill list field (in your Lessons section) configured to store "num"s as values? If not, you'll need to set that up, and also edit all your existing lesson records, reselecting the drills, and saving the records.
I hope this makes sense! I'll give this a closer look in the morning.
All the best,
Chris
Chris
Re: [chris] GETTING ERRORS...conflict with membership plugin?Re: [gkornbluth] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
By Codee - March 30, 2010
Hi Chris, thanks in advance for your help.
Yes, the values are set for "num" and the labels are set for "title". I realized that the foreach definition error was because I am using single item record for each drill...so I modified the viewer page code to
<?php $drill = $lessonsRecord['drill']; ?>
<?php if ($drill['title']): ?>
<?php echo $drill['title']; ?>
<?php endif; ?>
<?php if ($drill['description']): ?>
<?php echo $drill['description']; ?>
<?php endif; ?>
HOWEVER, no matter what method I try (with my limited knowledge) I can only get the viewer page to display the 'num' field for any of the fields in the drills section. So if the title for drill num 2 is "GAP" we see a "2". If the description with 200 characters for drill num 2 we only see the "2". How do we get the correct field data to show for each label?
Thanks,
Terry
Yes, the values are set for "num" and the labels are set for "title". I realized that the foreach definition error was because I am using single item record for each drill...so I modified the viewer page code to
<?php $drill = $lessonsRecord['drill']; ?>
<?php if ($drill['title']): ?>
<?php echo $drill['title']; ?>
<?php endif; ?>
<?php if ($drill['description']): ?>
<?php echo $drill['description']; ?>
<?php endif; ?>
HOWEVER, no matter what method I try (with my limited knowledge) I can only get the viewer page to display the 'num' field for any of the fields in the drills section. So if the title for drill num 2 is "GAP" we see a "2". If the description with 200 characters for drill num 2 we only see the "2". How do we get the correct field data to show for each label?
Thanks,
Terry
Re: [equinox69] GETTING ERRORS...conflict with membership plugin?Re: [gkornbluth] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
By Chris - March 30, 2010
Hi Terry,
A ha! Your golflessonDetails.php page was assigning the $lessonsRecord before running beta_lookupRelatedFields:
The problem here is that $lessonsRecord gets a copy of the first record before the lookup is preformed. The solution is to move the "get first record" line after beta_lookupRelatedFields:
I made this change to your live page to verify that was the problem, thanks for PMing FTP info. :)
Please let me know if you have any other issues or questions.
A ha! Your golflessonDetails.php page was assigning the $lessonsRecord before running beta_lookupRelatedFields:
list($lessonsRecords, $lessonsMetaData) = getRecords(array(
'tableName' => 'lessons',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$lessonsRecord = @$lessonsRecords[0]; // get first record
// load referenced records for our fields
beta_lookupRelatedFields(array(
'table' => 'lessons',
'recordList' => &$lessonsRecords,
'fieldList' => array( 'drill' )
));The problem here is that $lessonsRecord gets a copy of the first record before the lookup is preformed. The solution is to move the "get first record" line after beta_lookupRelatedFields:
list($lessonsRecords, $lessonsMetaData) = getRecords(array(
'tableName' => 'lessons',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
// load referenced records for our fields
beta_lookupRelatedFields(array(
'table' => 'lessons',
'recordList' => &$lessonsRecords,
'fieldList' => array( 'drill' )
));
$lessonsRecord = @$lessonsRecords[0]; // get first recordI made this change to your live page to verify that was the problem, thanks for PMing FTP info. :)
Please let me know if you have any other issues or questions.
All the best,
Chris
Chris
Re: [chris] GETTING ERRORS...conflict with membership plugin?Re: [gkornbluth] Pulling hair out, challenged with viewing data from multiple sections on single viewer page
By Codee - March 30, 2010
A ha! Your golflessonDetails.php page was assigning the $lessonsRecord before running beta_lookupRelatedFields:
oh...is that a bad thing? ;-)
you know...I moved that, too...based on some questionable research on another site...thanks for quickly and easily correcting me!
ONE IMPORTANT followup question:
What do I have to do ensure images pull from the drills database onto the viewer pages for golflessons?
thank you SOOO MUCH DUDE!!!!!
Terry