Linked tables
2 posts by 2 authors in: Forums > CMS Builder
Last Post: September 11, 2012 (RSS)
Hi and thanks in advance for your help
I have 3 linked tables
1) Accounts with username and password
2) Agency with name photo address etc (linked to account with CreatedbyUserNum because each account create only one agency page)
3) Products (eache agency could publish some item)
I would create a product page in wich i would recall Agency name, agency address etc but how can i join table n.2 and 3?
I try
:-(
I have 3 linked tables
1) Accounts with username and password
2) Agency with name photo address etc (linked to account with CreatedbyUserNum because each account create only one agency page)
3) Products (eache agency could publish some item)
I would create a product page in wich i would recall Agency name, agency address etc but how can i join table n.2 and 3?
I try
// load records
list($productsRecords, $productsMetaData) = getRecords(array(
'tableName' => 'products',
'where' => 'products.num = ' . getNumberFromEndOfUrl(),
'leftJoin' => array(
// foreign table => local field (that matches num in foreign table)
'agency' => 'createdByUserNum', .... but it doesn't work...:-(
Re: [orazio] Linked tables
I think the easiest way would be to use the getRecords function twice instead of using something more complex that uses it once. Although I`m not sure exactly how your tables are set up, I would do something similar to this:
Although I am assuming that only one agency exists per user, and that the user created both the agency and product.
list($productsRecords, $productsMetaData) = getRecords(array(
'tableName' => 'products',
'where' => whereRecordNumberInUrl(0),
'allowSearch' => false,
'limit' => '1',
));
$productsRecord = @$productsRecords[0]; // get first record
if (!$productsRecord) {
dieWith404("Record not found!");
}
//Now that we have the user number which created the product we can find which agency they created.
list($agencyRecords, $agencyMetaData) = getRecords(array(
'tableName' => 'agency',
'where' => 'createdByUserNum = ' . $productsRecord['createdByUserNum'],
'allowSearch' => false,
'limit' => '1',
));
$agencyRecord = @$agencyRecords[0]; // get first record
if (!$agencyRecord){
dieWith404("Record not found!");
}Although I am assuming that only one agency exists per user, and that the user created both the agency and product.
Greg Thomas
PHP Programmer - interactivetools.com
PHP Programmer - interactivetools.com