Best way to use a custom table in a relationship (in ExpressionEngine)?

Question

So, I’m a bit on how to use a separate table in a relationship, or something like that…

I have a table with around 5000 hotels called exp_h_hotels.
On my website, I use the pages module to create a static subpage for each part of the country. I want to list all hotels that belong to a specific region.

I have understood that I can’t do something like this (using ExpressionEngine tags with the query module):

{exp:query sql="SELECT * FROM exp_h_hotels WHERE h_regionname ='{regionname}'"}
       {hotel_name}
{/exp:query} 

Anyone knows the best way to go forward with this?

I have looked into using the ExpressionEngine API to insert the data into a channel – however, I get the feeling that it wouldn’t be optimal to flood the channel entries table with 5000 posts with 14-20 fields with data each.

Answer 1

There's no reason why this shouldn't work as you expect, so long as your exp:query tag is inside your channel:entries tag:

{exp:channel:entries channel="pages" limit="1"}
<h1>Hotels in {regionname}</h1>
<ul>    
{exp:query sql="SELECT * FROM exp_h_hotels WHERE h_regionname ='{regionname}'"}
   <li>{hotel_name}</li>
{/exp:query}
</ul>
{/exp:channel:entries}

However, for the long-term, importing your hotels into a new channel in EE is a much better plan. You could export from your database to CSV (using phpMyAdmin perhaps) and then import into EE using DataGrab. Nothing wrong with adding 5000 new entries to EE.