Related Record Lookup Functions
8 posts by 3 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: October 13, 2010 (RSS)
By Toledoh - October 10, 2010 - edited: October 10, 2010
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?
Tim (toledoh.com.au)
Re: [Toledoh] Related Record Lookup Functions
By Chris - October 11, 2010
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 recordThe 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.
Chris
Re: [chris] Related Record Lookup Functions
By Toledoh - October 12, 2010
Tim (toledoh.com.au)
Re: [chris] Related Record Lookup Functions
By Toledoh - October 12, 2010
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.Tim (toledoh.com.au)
Re: [Toledoh] Related Record Lookup Functions
By Chris - October 12, 2010
Try this to see what you're getting back from the database:
<?php
echo '<h1>$invoicesRecord</h1>';
showme($invoicesRecord);
echo '<h1>$customerRecord</h1>';
showme($customerRecord);
echo '<h1>$productRecord</h1>';
showme($productRecord);
?>That will show you all the fields you have available for each record.
The code you pasted is a WHERE clause for the getRecords() call. You can see what it's generating by calling showme outside of the getRecords() call. You can also use the 'debugSql' option to see what SQL is being generated.
echo '<h3>where clause</h3>';
showme(mysql_escapef('num = ?', $invoicesRecord['customer']));
list($customerRecords, ) = getRecords(array(
'tableName' => 'customer',
'where' => mysql_escapef('num = ?', $invoicesRecord['customer']),
'debugSql' => true,
));
$customerRecord = @$customerRecords[0]; // get first recordThe code is assuming that your $invoicesRecord's 'customer' field contains a 'num' identifying a record from the 'customer' section. That's correct, right?
I hope this helps shed some light on the issue. Let me know what you find.
Chris
Re: [chris] Related Record Lookup Functions
By Toledoh - October 12, 2010
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 recordand 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
Tim (toledoh.com.au)
Re: [Toledoh] Related Record Lookup Functions
By Jason - October 13, 2010
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
Tim (toledoh.com.au)