8 posts by 3 authors in: Forums > CMS Builder
Last Post: February 29, 2016   (RSS)

My website is for a live shows venue. I have a section editor named 'Shows' which contains lots of fields about the show.

One of those fields is for overnight hotel accommodation. I originally created a dropdown list for the name of the hotel but I want to go beyond that now and create a link to the hotel website and also list the telephone number for the hotel. I achieved this using the method below but what I would like to know is if there is another way to achieve this in order to eliminate human error in making each selection.

The method I am using:

  1. I created a multi-record section editor named Hotel with the fields: "hotel_name", "hotel_website", "hotel_phone"
  2. In the Shows section editor I created three list fields with similar names. In the Field Options in the Field Editor, I selected the appropriate list value and list label for each from the multi-record section editor named Hotel
  3. So for new Shows I have three dropdown lists for the hotel name, website and phone - but because I am listing several hotels, there could be human error in selecting the wrong URL or phone number to match the hotel

Is there a way to join up the records so that when I select the hotel name, I can somehow pull over the corresponding information for the website and telephone number?

Thanks

Terry

By ross - February 24, 2016

Hi Terry

Thanks for posting.

I assume that the extra hotel data that you want only needs to appear on the front of the website.

If I'm on track with that, would you be ok with still just selecting the hotel name from a drop down in your "Shows" section and then on the front of the website still be able to display the extra hotel fields?

I'd like to see if we can get this going without duplicating your data.

Thanks!

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

Hi Ross,

Yes you're pretty much bang on. Just select a hotel name (in CMS Builder when I am entering the show record) and from a dropdown box and the full record is displayed. The display is on a separate page with the details of the event. The main listing is barebones stuff (www.lakesidesurrey.co.uk) and the details stuff is available when you click on a show.

Thanks

Terry

By ross - February 25, 2016

Hi Terry


Great. Could you post a copy of your front end page so I can review your code?  

Or are you already familiar with looking up the hotel record?

Let me know. Thanks!

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

Hi Ross,

Here is the relevant part of the code for the display page:

<?php

list($showsRecords, $showsMetaData) = getRecords(array(
    'tableName'   => 'shows',
    'where'       => whereRecordNumberInUrl(1),
    'limit'       => '1',
  ));
  $showsRecord = @$showsRecords[0]; // get first record
?>

<?php if ($showsRecord['hotel']): ?>
<b>Hotel accommodation available at</b>:
    <a href="<?php echo $showsRecord['hotel_website'] ?>" title="<?php echo $showsRecord['hotel'] ?>" target="_blank"><?php echo $showsRecord['hotel'] ?></a>
    <?php if ($showsRecord['hotel_rooms_cost']): ?> @ <?php echo $showsRecord['hotel_rooms_cost'] ?><?php endif ?>.
    Telephone <?php echo $showsRecord['hotel_phone'] ?>.<br/><br/>
<?php endif ?>

So I've used three dropdown boxes in the hotel record to show 'hotel_website', 'hotel', and 'hotel_phone'.

Is this the code you mean?

Thanks

Terry

By Chris - February 26, 2016

Hi Terry,

I recommend replacing your three list fields with one list field. Call it "hotel", and in the Field Options in the Field Editor, select "num" for the List Value field and "name" (or whatever you like) for the List Label field.

Basically, if your Hotels table has three records (e.g. #1 Astoria, #2 Biltmore, #3 Caberet), the pulldown will show you the names (e.g. Astoria, Biltmore, Caberet), but you'll be storing just the record number (e.g. 3) in the Show records. Then in the front end, you use the number to look up the entire Hotel record. An easy way to do this is with mysql_get().

Here's an example:

<?php

list($showsRecords, $showsMetaData) = getRecords(array(
  'tableName' => 'shows',
  'where'     => whereRecordNumberInUrl(1),
  'limit'     => '1',
));
$showsRecord = @$showsRecords[0]; // get first record
?>


<?php if ($showsRecord['hotel']): ?>

  <?php $hotelRecord = mysql_get('hotel', $showsRecord['hotel']); ?>

  <?php if ($hotelRecord): ?>

    <b>Hotel accommodation available at</b>:
    <a href="<?php echo $hotelRecord['website'] ?>" title="<?php echo $hotelRecord['name'] ?>" target="_blank"><?php echo $hotelRecord['name'] ?></a>
    <?php if ($hotelRecord['rooms_cost']): ?> @ <?php echo $hotelRecord['rooms_cost'] ?><?php endif ?>.
    Telephone <?php echo $hotelRecord['phone'] ?>.<br/><br/>

  <?php endif ?>
<?php endif ?>

In my example, I'm guessing that your Hotel section name is 'hotel', but if it's 'hotels' or something else, you'll need to change the mysql_get line:

mysql_get('hotels', $showsRecord['hotel']);

The first "hotels" is the section name to load your hotel record from. The second "hotel" is the name of the (one) List Field in your shows section.

I'm also guessing at the field names in your hotel section, which you may need to change in my example code:

  • $hotelRecord['name']
  • $hotelRecord['website']
  • $hotelRecord['rooms_cost']
  • $hotelRecord['phone']

The extra "if" block in there is just for safety: in case you delete a Hotel but there are still Shows which had it selected, it protects you from showing an error. After trying to load hotel number 4, it'll make sure that we actually loaded a record before showing any Hotel fields.

If you can't get this working, please post the viewer code for your Hotel section, so we can see what the fields are in that section too.

Hope this helps!

All the best,
Chris

By Chris - February 29, 2016

Hi Terry,

That's great! I'm glad you got it working.

mysql_get() is just a shorter way to write your getRecords() call, but it doesn't have all the features that getRecords() has, so it's probably better that you're using getRecords().

All the best,
Chris