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 Chris,

I did not get the mysql_get() command to work but using the principle you outlined, this code worked:

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

<?php
    if ($showsRecord['hotel']): // if the field 'hotel' exists
    $hotelname = $showsRecord['hotellist']; // set up a variable with the field ID


    list($hotellistRecords, $hotellistMetaData) = getRecords(array(
    'tableName'   => 'hotellist',
    'where' => "num ='$hotelname'",  // select everything from the table hotellist where the 'num' is equal to the value in shws['hotellist']
    ));
    $hotellistRecord = @$hotellistRecords[0]; // get first record
?>

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

The formula:

1. Each of the options in the dropdown list in 'Shows' is the primary key from the table 'HotelList'
2. Then I set up the variable $hotelname to store the results from what was selected in the dropdown list which is used in the WHERE clause to import the hotel data
3. Then I call the three fields ['url'], ['name'], and ['tel'] which are nested in a conditional statement to be displayed only if the record shows['hotel'] exists.

Thanks for your guidance on this.

Regards

Terry