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

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)
Attachments:

relatedrecordlookupfunctions_004.php 10K

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: [Toledoh] Related Record Lookup Functions

By Chris - October 12, 2010

Hi Tim,

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 record

The 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.
All the best,
Chris

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/