Show next record (that matches the criteria)

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

By benedict - September 3, 2009

Hi,

I have a site that has 300 images in it, and for each image about 10 description fields that describe what tiles or grout are being used in the image.

I am using your search engine tool in the URL, so if a user wants to look at all the images of kitchens, they can (using this URL - http://www.myurl.com.au/detail.php?room_type=2). I am using a join between my tile images table and room type table with num as the value and room_type as the label

My question is this:

I think it might be called an 'array' but when the user elects to look at all the kitchens, what I want it to do is show the first record with the first record of data (image and descriptions) and when the user clicks the next arrow, it shows the next record (matching criteria "kitchens") both the image AND the data.

Any ideas?

Re: [benedict] Show next record (that matches the criteria)

By Chris - September 3, 2009

Hi benedict,

I think the simplest solution would be to generate a List viewer which displays only one record with prev & next page links, and use that as your detail page.

Admin > Code Generator
Viewer Type: List Page
How Many: Show [1] records per page with prev & next page links

If you call it with ?room_type=2, any prev & next links will retain that filter.

Please let us know if that doesn't do what you want, or if you have any questions or comments.
All the best,
Chris

Re: [chris] Show next record (that matches the criteria)

By benedict - September 3, 2009

D'oh - sometimes the easiest solutions are staring you in the face. That works great.

While I have you, the only other issue I have is that all the fields that are sending their values into the 'tile_images' table from other tables (e.g. room_type table, tile_sku table) are showing the value, not the label (I'm using num as value and tile_sku as the label).

You can see what I am talking about at http://www.glimpse.net.au/national/detail.php?room_type=6

Any ideas there?

Re: [benedict] Show next record (that matches the criteria)

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

Hi benedict,

That's definitely some advanced functionality there. :)

Attached is an example of how I set this up. You'll need to change my field and section names to get that code working on your end. Alternately, post the PHP source code for your page as an attachment and I'll show you what changes need to be made.

I hope this helps! Let me know if you have any questions. :)

P.S. I'm hoping to make some changes to CMSb in a future release which would make these kinds of lookups much easier.

P.P.S. Oh, and mine's just an ordinary detail page, instead of a List page with prev & next links, but the concept is the same.
All the best,
Chris
Attachments:

tile_images.php 3K

Re: [benedict] Show next record (that matches the criteria)

By Chris - September 4, 2009

Hi benedict,

Did you forget to attach your attachment? :)

I'll probably be on the forum a few times over the weekend, so hopefully I can help you get this figured out before Monday.
All the best,
Chris

Re: [chris] Show next record (that matches the criteria)

By benedict - September 4, 2009

Sorry here it is.
Attachments:

detail_004.php 13K

Re: [benedict] Show next record (that matches the criteria)

By Chris - September 4, 2009

Hi benedict,

Okay, please add these lines in red:

list($tile_imagesRecords, $tile_imagesMetaData) = getRecords(array(
'tableName' => 'tile_images',
'perPage' => '1',
));
$tile_imagesRecord = @$tile_imagesRecords[0]; // get first record
// show error message if no matching record is found
if (!$tile_imagesRecord) {
print "Record not found!";
exit;
}

That's necessary because I was using a Detail page and you're using a List page.


And finally, you'll need to use the name of the other section you're joining to (mine was named "tile", after re-reading your post, I think yours is named "tile_sku"?) Replace this code:

// fetch referenced tiles and index by num
list($tile_imagesRecords, $tile_imagesMetaData) = getRecords(array(
'tableName' => 'tile_images',
'where' => "num IN ($tile_nums)",
));
function collectNum($record) { return $record['num']; }
$tile_imagesRecordsByNum = array_combine(array_map('collectNum', $tile_imagesRecords), $tile_imagesRecords);

// inject tile records into tile_images record's fields
foreach ( $tile_fields as $field ) {
$tile_imagesRecord[$field] =& $tileRecordsByNum[ $tile_imagesRecord[$field] ];
}

With this:

// fetch referenced tiles and index by num
list($tile_skuRecords, $tile_skuMetaData) = getRecords(array(
'tableName' => 'tile_sku',
'where' => "num IN ($tile_nums)",
));
function collectNum($record) { return $record['num']; }
if ( sizeof($tile_skuRecords) > 0 ) {
$tile_skuRecordsByNum = array_combine(array_map('collectNum', $tile_skuRecords), $tile_skuRecords);
}

// inject tile_sku records into tile_images record's fields
foreach ( $tile_fields as $field ) {
$tile_imagesRecord[$field] =& $tile_skuRecordsByNum[ $tile_imagesRecord[$field] ];
}

(That'll also fix the array_combine() bug you ran into -- I forgot to test the case where a tile_image has zero referenced tiles.)
All the best,
Chris

Re: [chris] Show next record (that matches the criteria)

By benedict - September 4, 2009

OK, the good news is that there are no errors, the bad news is that it is still just using the value numbers, not the labels.

Any other ideas?

Re: [benedict] Show next record (that matches the criteria)

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

This line:

$tile_fields = array('floor_tile', 'floor_grout', 'shower_wall_1');

lists the fields that you want looked up in tile_sku. Those fields in your tile_images record will be replaced by records from tile_sku. You'll want to add all your other fields you want looked up too.

Now you can change this:

<?php if ($record['floor_tile']): ?>
<p><strong>Floor Tile: </strong><?php echo $record['floor_tile'] ?></p>
<?php endif ?>

to this:

<?php if ($record['floor_tile']): ?>
<p><strong>Floor Tile: </strong><?php echo $record['floor_tile']['tile_sku'] ?></p>
<?php endif ?>

for each of the fields you listed.

This assumes that 'tile_sku' (in red, above) is the name of the label you want from your tile_sku section.
All the best,
Chris