Importing data from one table to another

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!

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

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!

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

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!

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().