Related Record Lookup Functions

By Toledoh - October 10, 2010 - edited: October 10, 2010

Hi Guys.

Is the BETA version 0.2 still the most current version of this "Related Record Lookup Functions" plugin? And is it the most effective way of including details from other tables?

I have 2 instances that I need to use it in.

1. On a details page, I have the field; <?php echo $invoicesRecord['customer'] ?> which is a look-up from another table that identitfies the clients name. On that details page, I also want to include the clients address, phone, email etc.

2. On a details page I have the field <?php echo $invoicesRecord['line_item_1'] ?> which is a lookup from another table that identifies the product, but I also want to include the products price on the detail page.

Would I be best use related records for the customer details, and psuedo fields for the product?
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] Related Record Lookup Functions

By Chris - October 11, 2010

Hi Tim,

I believe that's the latest version, yes.

Using the beta related records plugin isn't any more efficient than the following code:

list($invoicesRecords, $invoicesMetaData) = getRecords(array(
'tableName' => 'invoices',
'where' => whereRecordNumberInUrl(0),
));
$invoicesRecord = @$invoicesRecords[0]; // get first record

// show error message if no matching record is found
if (!$invoicesRecord) {
header("HTTP/1.0 404 Not Found");
print "Record not found!";
exit;
}

list($customerRecords, ) = getRecords(array(
'tableName' => 'customer',
'where' => mysql_escapef('num = ?', $invoicesRecord['customer']),
));
$customerRecord = @$customerRecords[0]; // get first record

list($productRecords, ) = getRecords(array(
'tableName' => 'product',
'where' => mysql_escapef('num = ?', $invoicesRecord['line_item_1']),
));
$productRecord = @$productRecords[0]; // get first record


The beta plugin only really shines on list pages. We're still looking into providing this kind of functionality in a much easier-to-use way.

I hope this helps! Please let me know if you have any questions.
All the best,
Chris

Re: [chris] Related Record Lookup Functions

By Toledoh - October 12, 2010

Thanks Chris!
Cheers,

Tim (toledoh.com.au)

Re: [chris] Related Record Lookup Functions

By Toledoh - October 12, 2010

Hi Chris,

I've placed the code, played with the feild names so no errors come up, but where I place
<?php echo $client_listRecord['address'] ?><br />
<?php echo $client_listRecord['suburb'] ?><br />
<?php echo $client_listRecord['state'] ?><br />


nothing shows.

Can you explain 'where' => mysql_escapef('num = ?', $invoicesRecord['customer']), as I think this is my issue.
Cheers,

Tim (toledoh.com.au)

Re: [chris] Related Record Lookup Functions

By Toledoh - October 12, 2010

Hi Chris.

That's helped me a bit... bit still not there.

I've placed the following:
echo '<h3>where clause</h3>';
showme(mysql_escapef('num = ?', $invoicesRecord['customer']));
list($client_listRecords, ) = getRecords(array(
'tableName' => 'client_list',
'where' => mysql_escapef('num = ?', $invoicesRecord['customer']),
'debugSql' => true,
));
$client_listRecord = @$client_listRecords[0]; // get first record

echo '<h3>where clause2</h3>';
showme(mysql_escapef('title = ?', $invoicesRecord['line_item_1']));
list($line_itemsRecords, ) = getRecords(array(
'tableName' => 'line_items',
'where' => mysql_escapef('title = ?', $invoicesRecord['line_item_1']),
));
$line_item_1Record = @$line_item_1Records[0]; // get first record


and get the result:
where clause
num = '1'SELECT SQL_CALC_FOUND_ROWS `client_list`.* FROM `cms_client_list` as `client_list` WHERE (num = '1' AND (`num` = '6'))
where clause2
title = 'Murray\'s Sassy Blonde'


You can see the complete page here: http://www.murraysbrewingco.com.au/Orders/ordersDetail.php?num=6
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] Related Record Lookup Functions

By Jason - October 13, 2010

Hi Tim,

What seems to be happening is that you have num=6 in your query string since the num field can't be 6 and 1, you will always return an empty record set. Try turning off the search option like this:
list($client_listRecords, ) = getRecords(array(
'tableName' => 'client_list',
'where' => mysql_escapef('num = ?', $invoicesRecord['customer']),
'allowSearch' => false,
'debugSql' => true,
));


Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Related Record Lookup Functions

By Toledoh - October 13, 2010

Brilliant! Thank you!
Cheers,

Tim (toledoh.com.au)