JOIN question... I think
28 posts by 6 authors in: Forums > CMS Builder
Last Post: July 19, 2011 (RSS)
By zip222 - April 16, 2009
- news
- contacts
For each "news" record, the user selects a related contact from the "contacts" table. I have this setup as a dropdown list that is built by pulling the names from the "contacts" table.
On the public News pages I want to display additional information about the contact other than just the name. But I am not sure to go about doing this.
Can anyone help?
Re: [jdancisin] JOIN question... I think
By Dave - April 16, 2009
When you setup the related contact field, be sure to select "num" for the value field, and then title or name for the "label" field. Also, name your field contactNum.
Once you've done that, update a few records as they will have forgotten their old values.
Next, try this new leftJoin option in your viewer code that displays news:
// Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
'leftJoin' => array(
// foreign table => local field (that matches num in foreign table)
'contacts' => 'contactNum',
),This should load the contact fields as contacts.fieldname, eg: contacts.num, etc. Test this with this debug code to print out your $records array (which may be called newsRecords or something else:
<xmp><?php print_r($newsRecords); ?></xmp>
And then print out the values you want with something like this:
<?php echo $record['contacts.name'] ?>
Hope that made sense, let me know how it goes.
interactivetools.com
Re: [Dave] JOIN question... I think
By zip222 - April 16, 2009
SELECT SQL_CALC_FOUND_ROWS `news`.*,
management.`name` as `management.name`,
management.`short_name` as `management.short_name`,
management.`title` as `management.title`,
management.`email` as `management.email`,
management.`phone` as `management.phone`,
management.`short_bio` as `management.short_bio`
FROM `cms_news` as `news`
LEFT JOIN `cms_management` AS `management` ON news.`contactNum` = management.num
WHERE (num = '9')
ORDER BY date DESC
LIMIT 1
getRecords(news) MySQL Error: Column 'num' in where clause is ambiguousIts obviously almost working because I can see that its grabbing the fields from the other table. But note the bold part at the end.
FYI, the table is called "management" not "contacts" as I stated in the first post.
Re: [jdancisin] JOIN question... I think
By Dave - April 16, 2009
'where' => 'news.num = ' . getNumberFromEndOfUrl(),
Let me know if that works for you.
interactivetools.com
Re: [Dave] JOIN question... I think
By zip222 - April 16, 2009
<?php
require_once "/usr/www/users/mrock/cmsAdmin/lib/viewer_functions.php";
list($newsRecords, $newsMetaData) = getRecords(array(
'tableName' => 'news',
'where' => 'news.num = ' . getNumberFromEndOfUrl(),
'limit' => '1',
'debugSql' => true,
'leftJoin' => array(
'management' => 'contactNum',
),
));
$newsRecord = @$newsRecords[0]; // get first record
?>And here is the page url...
http://metissecure.com/news/article-test.php?9
Re: [jdancisin] JOIN question... I think
By Dave - April 16, 2009
Try <?php echo $newsRecord['contacts.name'] ?>
interactivetools.com
Re: [Dave] JOIN question... I think
By zip222 - April 16, 2009
that's going to be a very helpful thing to know.
Re: [Dave] JOIN question... I think
By willbegood - June 1, 2009
In my case news table = produits
and contacts = point_dinteret.
For my part, i use list field (checkbox) because i want to be able to associate several "entries of "point_dinteret" to a "produits" entry.
1/ Here is my top queries
<?php
require_once "/var/www/vhosts/..........viewer_functions.php";
list($produitsRecords, $produitsMetaData) = getRecords(array(
'tableName' => 'produits',
'leftJoin' => array('point_dinteret' => 'linkedPOI', )
));
?>
i tryed to do 'linkedPOINum" it doesn't work. No error with "linkedPOI"
"linkedPOI" is my field list (check box) in "produits" wich is linked to "point_dinteret" table.
2/ doing <xmp><?php print_r($produitsRecords); ?></xmp>
i can see very well all the field from "point_dinteret" calling,
but doing for exemple : <?php echo $record['point_dinteret.titre'] ?> .... no variable showing, it has no result
3/ As i say upper, i have many entries of "point_dinteret" associated with each entry of "produits", i cannot see the loop
see here : http://www.onsemobilisepourvous.com/client/caribbean_spirit_net/gmap3/test8.php
and see the attachment
Re: [willbegood] JOIN question... I think
By Dave - June 1, 2009
It looks like linkedPOI is a multi-select field. Generally the automatic left joins don't work with multi-select fields because the only "join" one record with another.
You could try this, which would return the same record multiple times. We've never done it like that before though we'd have to see how it worked.
list($produitsRecords, $produitsMetaData) = getRecords(array(
'tableName' => 'produits',
'leftJoin' => array(
'point_dinteret' => 'ON produits.linkedPOI LIKE CONCAT("%\t", point_dinteret.num, "\t%")',
),
));Let me know if that works for you.
interactivetools.com
Re: [Dave] JOIN question... I think
By willbegood - June 1, 2009
see : http://www.onsemobilisepourvous.com/client/caribbean_spirit_net/gmap3/test8.php
Only thing is that i have my record "produits" that repeat its self as many time as i have linkedPOI.
How to display only one time the entry for "produits" and just under all the linkedPOI wich are associated to it?