leftJoin help needed
8 posts by 3 authors in: Forums > CMS Builder
Last Post: November 11, 2011 (RSS)
By Deborah - November 9, 2011
I 'm having trouble joining two tables. 'home_pg_slideshow' has a select list titled 'short_title' based on the field 'short_title' in the table 'furniture_categories'. I want to display the field 'short_title', and other fields from the 'furniture_categories' table. My code:
list($home_pg_slideshowRecords, $home_pg_slideshowMetaData) = getRecords(array(
'tableName' => 'home_pg_slideshow',
'where' => 'home_pg_slideshow.num = ' . getNumberFromEndOfUrl(),
'debugSql' => true,
'leftJoin' => array(
'furniture_categories' => 'short_titleNum',
),
));
$home_pg_slideshowRecord = @$home_pg_slideshowRecords[0]; // get first record
Note the MySQL error shown in the debug:
SELECT SQL_CALC_FOUND_ROWS `home_pg_slideshow`.*,
furniture_categories.`num` as `furniture_categories.num`,
furniture_categories.`title` as `furniture_categories.title`,
furniture_categories.`short_title` as `furniture_categories.short_title`,
furniture_categories.`abbreviation` as `furniture_categories.abbreviation`,
furniture_categories.`master_category_title` as `furniture_categories.master_category_title`,
furniture_categories.`master_landing_pg_url` as `furniture_categories.master_landing_pg_url`,
furniture_categories.`landing_pg_url` as `furniture_categories.landing_pg_url`,
furniture_categories.`category_page_heading` as `furniture_categories.category_page_heading`,
furniture_categories.`category_pg_description` as `furniture_categories.category_pg_description`
FROM `cms_home_pg_slideshow` as `home_pg_slideshow`
LEFT JOIN `cms_furniture_categories` AS `furniture_categories` ON home_pg_slideshow.`short_titleNum` = furniture_categories.num
WHERE (home_pg_slideshow.num = 0)
ORDER BY dragSortOrder DESC
MySQL Error: Unknown column 'home_pg_slideshow.short_titleNum' in 'on clause'
-------------------
In the table 'home_pg_slideshow' pulldown list for 'short_title' I'm using for get options from database (advanced):
tablename of furniture_categories
option values - num
option labels - short_title
------------------
I was working with this forum post:
http://www.interactivetools.com/iforum/Products_C2/CMS_Builder_F35/JOIN_question..._I_think_P71124/
I'm hoping someone can help me out. I seem to be very close to making this work.
~ Deborah
list($home_pg_slideshowRecords, $home_pg_slideshowMetaData) = getRecords(array(
'tableName' => 'home_pg_slideshow',
'where' => 'home_pg_slideshow.num = ' . getNumberFromEndOfUrl(),
'debugSql' => true,
'leftJoin' => array(
'furniture_categories' => 'short_titleNum',
),
));
$home_pg_slideshowRecord = @$home_pg_slideshowRecords[0]; // get first record
Note the MySQL error shown in the debug:
SELECT SQL_CALC_FOUND_ROWS `home_pg_slideshow`.*,
furniture_categories.`num` as `furniture_categories.num`,
furniture_categories.`title` as `furniture_categories.title`,
furniture_categories.`short_title` as `furniture_categories.short_title`,
furniture_categories.`abbreviation` as `furniture_categories.abbreviation`,
furniture_categories.`master_category_title` as `furniture_categories.master_category_title`,
furniture_categories.`master_landing_pg_url` as `furniture_categories.master_landing_pg_url`,
furniture_categories.`landing_pg_url` as `furniture_categories.landing_pg_url`,
furniture_categories.`category_page_heading` as `furniture_categories.category_page_heading`,
furniture_categories.`category_pg_description` as `furniture_categories.category_pg_description`
FROM `cms_home_pg_slideshow` as `home_pg_slideshow`
LEFT JOIN `cms_furniture_categories` AS `furniture_categories` ON home_pg_slideshow.`short_titleNum` = furniture_categories.num
WHERE (home_pg_slideshow.num = 0)
ORDER BY dragSortOrder DESC
MySQL Error: Unknown column 'home_pg_slideshow.short_titleNum' in 'on clause'
-------------------
In the table 'home_pg_slideshow' pulldown list for 'short_title' I'm using for get options from database (advanced):
tablename of furniture_categories
option values - num
option labels - short_title
------------------
I was working with this forum post:
http://www.interactivetools.com/iforum/Products_C2/CMS_Builder_F35/JOIN_question..._I_think_P71124/
I'm hoping someone can help me out. I seem to be very close to making this work.
~ Deborah
Re: [Deborah] leftJoin help needed
By Jason - November 9, 2011
Hi Deborah,
Try changing short_titleNum to short_title like this:
That should take care of that error.
Hope this helps
Try changing short_titleNum to short_title like this:
list($home_pg_slideshowRecords, $home_pg_slideshowMetaData) = getRecords(array(
'tableName' => 'home_pg_slideshow',
'where' => 'home_pg_slideshow.num = ' . getNumberFromEndOfUrl(),
'debugSql' => true,
'leftJoin' => array(
'furniture_categories' => 'short_title',
),
));
$home_pg_slideshowRecord = @$home_pg_slideshowRecords[0]; // get first recordThat should take care of that error.
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/
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] leftJoin help needed
By Deborah - November 9, 2011
Hi, Jason. Thank you. Yes, that change took away the error.
Now in attempting to display the listings, I think because I'm not searching (just displaying a full list) I need to adjust something relating to the following line;
'where' => 'home_pg_slideshow.num = ' . getNumberFromEndOfUrl(),
With the above line in place I get 'no records found'. (which makes sense)
Without that line I see all of my 'home_pg_slideshow' records, but for the fields joining from the other table the first selection is repeated for each record, instead of their unique values.
Any idea what I need to change? Thanks again.
~ Deborah
Now in attempting to display the listings, I think because I'm not searching (just displaying a full list) I need to adjust something relating to the following line;
'where' => 'home_pg_slideshow.num = ' . getNumberFromEndOfUrl(),
With the above line in place I get 'no records found'. (which makes sense)
Without that line I see all of my 'home_pg_slideshow' records, but for the fields joining from the other table the first selection is repeated for each record, instead of their unique values.
Any idea what I need to change? Thanks again.
~ Deborah
Re: [Deborah] leftJoin help needed
By Jason - November 10, 2011
Hi Deborah,
Another approach would be to not use a join, but do a separate query to the furniture category section like this (note, this assumes you're using version 2.08 or higher):
The mysql_get() code could also be put inside a foreach loop if you were displaying multiple homepage_pg_records
Hope this helps
Another approach would be to not use a join, but do a separate query to the furniture category section like this (note, this assumes you're using version 2.08 or higher):
list($home_pg_slideshowRecords, $home_pg_slideshowMetaData) = getRecords(array(
'tableName' => 'home_pg_slideshow',
'where' => 'home_pg_slideshow.num = ' . getNumberFromEndOfUrl(),
));
$home_pg_slideshowRecord = @$home_pg_slideshowRecords[0]; // get first record
$furnitureCategory = mysql_get('furniture_categories', null, "num = ".$home_pg_slideshowRecord['short_title']);The mysql_get() code could also be put inside a foreach loop if you were displaying multiple homepage_pg_records
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/
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] leftJoin help needed
By Deborah - November 10, 2011
Jason, this site is using 2.12.
I tried the new code, but see a MySQL error in debug:
SELECT SQL_CALC_FOUND_ROWS `home_pg_slideshow`.*
FROM `cms_home_pg_slideshow` as `home_pg_slideshow`
WHERE (home_pg_slideshow.num = 0)
ORDER BY dragSortOrder DESC
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 'LIMIT 1' at line 1
------------------
And yes, I do wish to display multiple homepage_pg_slideshow records.
Thanks for your help on this.
~ Deborah
I tried the new code, but see a MySQL error in debug:
SELECT SQL_CALC_FOUND_ROWS `home_pg_slideshow`.*
FROM `cms_home_pg_slideshow` as `home_pg_slideshow`
WHERE (home_pg_slideshow.num = 0)
ORDER BY dragSortOrder DESC
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 'LIMIT 1' at line 1
------------------
And yes, I do wish to display multiple homepage_pg_slideshow records.
Thanks for your help on this.
~ Deborah
Re: [Deborah] leftJoin help needed
By gkornbluth - November 11, 2011 - edited: November 11, 2011
Hi Deborah,
Don’t know if a modification of this would work for you, but I’ve used the free "related records" plugin in something similar to your application.
Here’s the basic recipe from my CMSB Cookbook http://www.thecmsbcookbook.com
USING "RELATED RECORDS" TO POPULATE 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 each of them.
Instead of retyping all of your information each time you want to display it on a list or detail page, you can pull that information directly from your master information list. Chris Waddell 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: pull-down or checkbox (multi-value for multi-value lists only)
List Options: Get options from database (advanced)
Section Table name: 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:
http://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.
DETAIL PAGES
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 of the required code in your PHP source code. Just add this code:
to your existing code, like this:
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.
There are a lot of other details for specific situations like single value list fields, displaying on list pages or using images in the Cookbook recipe, but this should get you started.
Best,
Jerry Kornbluth
Don’t know if a modification of this would work for you, but I’ve used the free "related records" plugin in something similar to your application.
Here’s the basic recipe from my CMSB Cookbook http://www.thecmsbcookbook.com
USING "RELATED RECORDS" TO POPULATE 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 each of them.
Instead of retyping all of your information each time you want to display it on a list or detail page, you can pull that information directly from your master information list. Chris Waddell 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: pull-down or checkbox (multi-value for multi-value lists only)
List Options: Get options from database (advanced)
Section Table name: 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:
http://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.
DETAIL PAGES
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 of the required code in your PHP source code. Just add this code:
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 recordto your existing code, like this:
<?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',
));
?>
<?php if ($e_blast_events_noticeRecords): ?>
<?php
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
?>
<?php endif ?>
<?php
// 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.
There are a lot of other details for specific situations like single value list fields, displaying on list pages or using images in the Cookbook recipe, but this should get you started.
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] leftJoin help needed
By Deborah - November 11, 2011
Jerry,
Thank you for your well-detailed post. I started with the instructions you posted here and then logged in to your CMSB Cookbook for further assistance...
... and I got my page working! [insert happy face here]
I appreciate your assistance very much!
~ Deborah
Thank you for your well-detailed post. I started with the instructions you posted here and then logged in to your CMSB Cookbook for further assistance...
... and I got my page working! [insert happy face here]
I appreciate your assistance very much!
~ Deborah
Re: [Deborah] leftJoin help needed
You're very welcome.
Glad it worked
Jerry
Glad it worked
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