Hi,

I have searched the forums, and have found some interesting stuff. But not quite what I need.

A friend asked me to help set up a database for his website. No Problem. DBMan installed like a dream. THEN he showed me his Flow Chart!!!

He wanted (I thought) to query one database and from the search query a second database which would then enable the user to click for the results of Second Search. Again. Found the solution. The Multiple "Relational" mod. Got that working. And very happy with it. But he kept referring to the fact that the First results page was not a box????

I have just realised that he means a FORM!!! IOW, the first query searches the db1, generates the results in a FORM using <SELECT> that then searches db2, All linked (I guess) with the db key field. I have found some "almost" solutions to do with order forms etc. But that seems more than what I need.

I am thinking that the answer may be in the Modify Record Subs as they search and then present the results in a FORM. But, please. Any help greatly appreciated.

Thanks - Susan

I'm really glad you were able to get the relational mod working and you're happy with it. That's the "fuzziest" of the mods and I'm never quite sure if it's understandable.

Now to your problem...

You're going to have to do some interesting things to sub html_view_success and in html.pl for db1. I was thinking first that you would have to alter sub html_record, but I don't think so now. I think you can do it all in sub html_view_success.

Basically, the beginning of sub html_view_success would be the top part of the search form for db2. Everything that needs to go before the select field. The next part would be the "for" loop, but instead of using html_record, you can just build the select field right there. Something like:


Then the rest of sub html_view_success would be the rest of the form.

That second field can be whatever you want, or even multiple fields. Whatever you want to have people see so they'll know they're getting the right record.

I didn't do a whole lot of code here, because it looks like you pretty much have a handle on how the whole thing works. If you need more, just holler and I'll be glad to help.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

AHA!!!!

That makes sense. So let's try it first

Also I have to be honest. I only followed your instructions in the Relational text. And it still took me a couple of hours to get it working. And that was because I didn't tell the db.cgi WHICH database to start with (db1) when I tried the Admin interface first. After I get this working i will have to clean up the Admin Section. Otherwise we will have problems with Users entering data in the wrong db.

Thanks.
Susan

Let me know if you have any problems or need any pointers on the relational thing, as well as this one. I welcome any questions so I know what I've left out of the mod.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

Wow!! I just sat down to do it and it worked like a charm. thank you thank you.

One tiny little item ... I am going to need a "Submit" button. I am going to try and replace the link ref in html_record and see if that works. let you know how it goes.

OK. I put it in with the snippet. Like this:

print qq|<SELECT NAME="Location">|;
for (0 .. $numhits - 1) {

%rec = array_to_hash($_, @hits);
print qq|<FORM><OPTION VALUE="$rec{'1'}">$rec{'Location'}|;
}
print qq|</SELECT><BR><INPUT TYPE="SUBMIT" NAME="view_records" VALUE="Search"></FORM>|;

which gets the SUBMIT button to show up. But it doesn't do anything. A quick look at the source shows that it is not passing the db_script_url and there are some weird things happening with the actual FORM

<SELECT NAME="Location" SIZE="4">
<FORM><OPTION VALUE="">Gloucestershire
<FORM><OPTION VALUE="">Avon/Sommerset
<FORM><OPTION VALUE="">Wiltshire
<FORM><OPTION VALUE="">Devon
<FORM><OPTION VALUE="">Cornwall
<FORM><OPTION VALUE="">West Midlands
<FORM><OPTION VALUE="">West Mercia</SELECT>
<INPUT TYPE="SUBMIT" NAME="view_records" VALUE="Search">
</FORM>

So I added the db_script_url as it is in the A HREF under the switch_user mod

<FORM ACTION="$db_script_url?db=careers&uid=$db_uid&Location=$rec2{'Location'}&view_records=1" METHOD="GET">

And I got this


<SELECT NAME="Location">
<FORM ACTION="http://albaconsec.com/cgi-bin/dbrel/db.cgi?db=careers&uid=default&Location=&view_records=1" METHOD="GET">
<OPTION VALUE="">Gloucestershire
<FORM ACTION="http://albaconsec.com/cgi-bin/dbrel/db.cgi?db=careers&uid=default&Location=&view_records=1" METHOD="GET">
<OPTION VALUE="">Avon/Sommerset

etc.

Obviously, somethings not quite there. But it's close

Any ideas? I will keep at it. I have some. But they are probably wrong. Like using $rec2{} instead of $rec{} when referring to db2

THX

The <form> tag needs to go above the loop. Basically, what you are doing is repeating sub html_view_search, with some tweaks.



"$rec{'1'}" can't be right. That needs to be the name of a field. It should be the field that the two dbs have in common. I am assuming that db2 is the "many" side of the "one-to-many" relationship between the two databases. Which means that while you have only one record that refers to Devon in db1, you have multiple records that refer to Devon in db2. Which means that what I told you earlier is wrong. It happens. :-)

Is the "Location" field the one that the two databases have in common? If it is, you can even delete the VALUE="$rec{'1'}" part of the code completely. So that line would look like this:



Sorry I mislead you. Until I was actually writing about the "one-to-many" relationship, I didn't have it as clear in my mind as I thought I did.

The only time you need to have different names for the records from the different databases is when values are being displayed from both in the same form. Even then, you might not have to worry about it, because there would only be one field name in common between the two and the value in a given record would be the same.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

That makes sense. I will try it later on tonight. And I don't know why I thought you said "number of field" when it is clearly name

Thx

Worked like a charm! Thank you so much for making it so easy.



Susan