Limiting selection made by whereRecordNumberInUrl(1)

8 posts by 3 authors in: Forums > CMS Builder
Last Post: September 2, 2009   (RSS)

By InHouse - August 31, 2009

On a given page I have both a list view and a single detail view - both drawn from the same table. Clicking on a link from the list view loads the details into that same page. Working well.

The first time the page loads without a record number in the URL, it defaults to the sort order. Fine.

Now the catch: The records have a 'type' field which the user can set to A, B, or C.

I need the whereRecordNumberInUrl(1) function in the detail viewer to work with the Where clause and limit the initial page load to only pick from the table where type="B".

Got the list to use the where clause, but having problems with the first detail page load (without the record number in the URL).

Any ideas out there?

J.

Re: [InHouse] Limiting selection made by whereRecordNumberInUrl(1)

By Chris - September 1, 2009

Hi InHouse,

It sounds like you're going to need to "pass through" query strings from request to request. Can you please post the PHP source code for your page so I can make sure the solution I'm thinking of will work for you? :)
All the best,
Chris

Re: [chris] Limiting selection made by whereRecordNumberInUrl(1)

By InHouse - September 1, 2009 - edited: September 2, 2009

THanks Chris,

The record selections are made from included external files as they are to be used in several places. They look like this:

<?php # Load research article details
require_once "/home/health17/public_html/cmsAdmin/lib/viewer_functions.php";

list($imResearchRecord, $imResearchRecordMetaData) = getRecords(array(
'tableName' => 'tips_inmotion',
# 'where' => "type = \"Research Tip\" and whereRecordNumberInUrl(1)",
// This is the sticking point. Obviously this syntax doesn't work but this is what I want to do.
# 'where' => whereRecordNumberInUrl(0),
'orderBy' => 'type DESC, publishDate, title',
'limit' => '1',
));
$imResearchRecord = @$imResearchRecord[0]; // get first record

$emailThisPage = "http://" . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] . "?" . $imResearchRecord['num'];
?>

and

<?php // Load list of research articles for sidebar.
$articleLimit = ((isset($articleLimitList)) ? $articleLimitList : '');
// echo $articleLimit ." Articles"; // Debug Code.
(((isset($pullRandom)) && ($pullRandom == "TRUE") ) ? $newOrder = 'RAND()' : $newOrder = 'publishDate DESC, num DESC');

list($imReasearchRecords, $mReasearchRecordsMetaData) = getRecords(array(
'tableName' => 'tips_inmotion',
'orderBy' => "$newOrder",
'loadUploads' => '0',
'allowSearch' => '0',
'where' => "type = \"Research Tip\"", // Do not include the general articles.
'limit' => "$articleLimit",
)); ?>

The main detail display on the output page looks like:
<h1><?php echo $imResearchRecord['title'] ?></h1>
<?php if ($imResearchRecord['subtitle']): ?>
<h2 class="subtitle"><?php echo $imResearchRecord['subtitle']; ?></h2>
<?php endif; ?>
<?php echo $imResearchRecord['content'] ?>

<div class="downloadBlock">
<?php foreach ($imResearchRecord['download'] as $upload): ?>
<!-- bunch of download stuff here -->

<?php endforeach ?>
<!-- /Display Uploads -->
</div><!-- /downloadBlock -->

<?php // Reviewed Date Block
if (date("Y", strtotime($imResearchRecord['last_reviewed'])) > '2000'):
// Hide review dates earlier than 2000. ?>
<div id="lastReviewdBlock">
<?php echo "Last&nbsp;Reviewed&nbsp;"
.date("M Y", strtotime($imResearchRecord['last_reviewed']));?>
</div>
<?php endif; ?>

and the display page sidebar shows the list of articles to be viewed:

<h2>Research Archive</h2>
<ul>
<?php foreach ($imReasearchRecords as $record): ?>
<li><a href="<?php
echo "in-motion-research.php?"
. str_replace(' ','-',$record['title'])."-".$record['num'] ; ?>">
<?php echo $record['title']; ?></a>
</li>
<?php endforeach; ?>
</ul>
</div>
<br class="clearFloat" />

If you can sort this out I'd be very happy.

BTW Chris, you did a bang-up job with that bulk data import from the old Drupal site we took over. Hiring you guys for this was a real lifesaver!

J.

Re: [InHouse] Limiting selection made by whereRecordNumberInUrl(1)

By Chris - September 1, 2009 - edited: September 1, 2009

Hi InHouse,


# 'where' => "type = \"Research Tip\" and whereRecordNumberInUrl(1)", // This is the sticking point. Obviously this syntax doesn't work but this is what I want to do.


whereRecordNumberInUrl(1) is actually quite trivial: basically it just returns:

"num = '" . getNumberFromEndOfUrl() . "'"

Therefore, you can call that function yourself and construct your own custom WHERE clause:

$urlNum = getNumberFromEndOfUrl();
list($imResearchRecord, $imResearchRecordMetaData) = getRecords(array(
'tableName' => 'tips_inmotion',
'where' => "type = \"Research Tip\" and num = '{$urlNum}'",
'limit' => '1',
));
$imResearchRecord = @$imResearchRecord[0]; // get first record

All this will do is prevent visitors from viewing records which don't have type="Research Tip". Is that what you wanted?

Hope this helps! Please let us know if you have any more questions. :)

BTW Chris, you did a bang-up job with that bulk data import from the old Drupal site we took over. Hiring you guys for this was a real lifesaver!


Thanks! Someone on your end did all the hard work of figuring out which fields in which tables had all the relevant data, so my job was pretty easy. I'm glad that project worked out for you. :D
All the best,
Chris

Re: [chris] Limiting selection made by whereRecordNumberInUrl(1)

By InHouse - September 1, 2009

Got it! It's easy when you know how.

Just in case anyone else runs into this issue, this is the workaround that I eventually used based on Chris' response above:

$urlNum = getNumberFromEndOfUrl();
if ($urlNum >=1):
$whereClause = "type = \"Research Tip\" and num = '{$urlNum}'";
else:
$whereClause = "type = \"Research Tip\" ";
endif;

list($imResearchRecord, $imResearchRecordMetaData) = getRecords(array(
'tableName' => 'tips_inmotion',
'where' => $whereClause,
'limit' => '1',
));
$imResearchRecord = @$imResearchRecord[0]; // get first record

This allows me to pull a subset of the articles by type and handle the conditions where there may or may not be a number at the end of the URL.

Thanks again Chris! Mind you, all this talk about whereClauses makes me think that Santa has been infected with lycanthropy.

Cheers,
Jayme

Re: [InHouse] Limiting selection made by whereRecordNumberInUrl(1)

By Dave - September 2, 2009

>all this talk about whereClauses makes me think that
>Santa has been infected with lycanthropy.

ROTFL! Ok... Seriously, that was a great one. :)
Dave Edis - Senior Developer
interactivetools.com

Re: [InHouse] Limiting selection made by whereRecordNumberInUrl(1)

By Chris - September 2, 2009

Hehehe, took me a good thirty seconds to align the "pun" and "d&d" parts of my brain in order to get that. :)
All the best,
Chris