Author Topic: Relational Queries - sort of!  (Read 13906 times)

0 Members and 1 Guest are viewing this topic.

Offline smc

  • Full Member
  • ***
  • Posts: 45
    • EternityWhere.com
Relational Queries - sort of!
« on: May 22, 2007, 03:59:11 PM »
Thanks to Jason for inspiring this tip as a follow-on from my post here;

http://www.ezscriptingforums.com/forum/index.php?topic=344.0

I had often wondered how to display related data from 2 separate databases on the same template output page.  I realise that others have done this using iframes but here is my solution using Javascript.

Scenario:  I have a database of MP3s file with lots of information for each file such as date, speaker, reference material etc etc.  I had always thought that in my main tempate output file for each MP3 it would be nice to display a brief biopic about each speaker.  Problem - there are currently over 90 speakers and over 300 MP3 records in the database.  Initailly I had though of adding the biopic to each record in the MP3 database... quickly banished that thought when I realised that if a speaker needed to update their biopic numerous records would have to be updated in the database.  Also - it would be cumbersome to add the info when adding a new record; certainly not EZ!

Solution; I created a second 'speaker' database with 3 main fields; 'firstname', 'surname' 'biopic'.  The first 2 fields 'firstname', 'surname' of course match fields in my main MP3 database.

In my template output page the MP3 database is queried to provide all the information about the MP3 audio (sermon) file.  Example here;

http://www.colerainefpc.org/cgi-bin/audio/audio.cgi?ID=200704221845&speaker_surname=Gibson&template=church

You will note a 'Speaker Information' box towards the bottom of this page.  The content of this box is pulled from the 'speaker' database using a javascript call; which if you dig into the code looks like this;

<script language="JavaScript" type="text/javascript" src="http://www.colerainefpc.org/cgi-bin/audio/speaker_biopic_javascript.cgi?surname=[[surname]]&firstname=[[firstname]]">  </script>

The script fills out the speaker name and good old JS displays the biopic from the other DB.

The template file for this is very simple; something akin to the one posted here;

http://www.ezscriptingforums.com/forum/index.php?topic=344.0

I have just set this up today and it runs ok.  I have the advantage of being the only admin on this site so I have control over js-unfriendly entries.  However, I feel that this opens up lots of possibilites for related queries on various DBs; in fact as many as you want?!

Hope that this is of use to someone and apologies if I have insulted anyone's intelligence!

Thanks again to Jason; only when I put \ at the end of the line did the text display properly!




« Last Edit: May 22, 2007, 04:02:12 PM by smc »

Offline Jason

  • Administrator
  • Hero Member
  • *****
  • Posts: 1018
    • slipdex
Re: Relational Queries - sort of!
« Reply #1 on: May 22, 2007, 04:36:55 PM »
Nice work!!

Also, for anyone out there trying to do similar things...  using this js solutions can be a bit easier for some than using the SSI statements.  Plus if you configure everything for the js then it can easily be placed on another domain on another server, in another country!  Thanks to smc for reminding all of us about the power of js!!

Jason
Founder of www.slipdex.com

Offline Joluco

  • Newbie
  • *
  • Posts: 2
Re: Relational Queries - sort of!
« Reply #2 on: October 24, 2007, 02:08:13 AM »
Hi all,

When I happened to find this topic, I thought all my problems were fixed, but I've tried many times to run the script and it doesn't work for me. I don't know exactly what I'm doing wrong, so any help will be appreciated.

I've got some doubts about smc's explanations:
1) OK, you create a new database, but do you set up a new script as well? I mean, when you make the call to "speaker_biopic_javascript.cgi", is this supposed to be a new script related to the new DB? In that case, which is the template name related to the DB in this script?

2) I made a javascript with the new template, as you explained, but now I don't know where to place this "template.js"...

Please, give me a hand. I've duplicated my work for a long time and now I found at last a good solution, but I'm not able to make it work.
Thanks in advance

Offline smc

  • Full Member
  • ***
  • Posts: 45
    • EternityWhere.com
Re: Relational Queries - sort of!
« Reply #3 on: October 24, 2007, 09:47:36 AM »
Joluco

I made a new database and to keep things simple (for me) a new instance of the script "speaker_biopic_javascript.cgi" which had only one template called "javascript_template.htm" or something like that.  The content of that file looked like this;

document.write('<template>include here all your normal template format stuff as you would like it appear on the other site</template>');

Does that help?

Offline Joluco

  • Newbie
  • *
  • Posts: 2
Re: Relational Queries - sort of!
« Reply #4 on: October 24, 2007, 02:26:50 PM »
OK, it's clear. Now I know what I did wrong. Thanks a lot.