JOIN question... I think

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.

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.

Try replacing your where like with this:

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

Let me know if that works for you.

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

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

Try <?php echo $newsRecord['contacts.name'] ?>

Nice!

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

Hello Dave, i have the same problem.
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

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.

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?