Linked tables

2 posts by 2 authors in: Forums > CMS Builder
Last Post: September 11, 2012   (RSS)

By mbareara - September 11, 2012

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

By gregThomas - September 11, 2012

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:

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