JOIN question... I think

28 posts by 6 authors in: Forums > CMS Builder
Last Post: July 19, 2011   (RSS)

By zip222 - April 16, 2009

I have two tables:
- 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

Hi jdancisin,

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.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] JOIN question... I think

By zip222 - April 16, 2009

Almost, but I am seeing the following...

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 ambiguous


Its 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

Try replacing your where like with this:

'where' => 'news.num = ' . getNumberFromEndOfUrl(),

Let me know if that works for you.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] JOIN question... I think

By zip222 - April 16, 2009

I am not getting the error message now, but the connected record from the other table is not displaying. Here is the viewer code I am using...

<?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

Instead of <?php echo $record['contacts.name'] ?>

Try <?php echo $newsRecord['contacts.name'] ?>
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] JOIN question... I think

By zip222 - April 16, 2009

Nice!

that's going to be a very helpful thing to know.

Re: [willbegood] JOIN question... I think

By Dave - June 1, 2009

Hi willbegood,

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.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] JOIN question... I think

By willbegood - June 1, 2009

wowww, it works!!!
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?