Now that I have the relational database working , I'm down to what I hope is the last mile.

I apologize if I've missed this from other posts ... (I did search - honest)

Anyway, what I need to do now is to get two different searches working from a static web page outside the database.
The problem is to get results that span both databases. Profile (the one) and Playlist (the many)

One of the searches, I was hoping would be a simple keyword search that would return results from both databases. If I have to, I can offer 2 search options for the keyword search, which I have temporarily set up to work, but it would definitely be tidier if I could figure out how to do it with one.

The other search is to be based on 2 criteria. Genre and Featured. Now Genre is one of the items that I had set up in the profile (the one) database since it would be the same no matter how many playlists a DJ is listing. But the information that needs to be returned is now set up for the playlist (the many). When I realized this, I figured that I needed to add the genre field into the playlist database as well. So far I haven't succeeded at this tactic either. Am I on the right track, and if so, any pointers?

Here's what I've tried so far ...
I added the Genre field to the db.cgi (sub switch_to_playlist)
$cols = 'DJ,PlayListID,Genre,DateAdded,Playlist,Favorite';
Then I inserted Genre to playlist.cfg
Genre' => [ 2, 'alpha', 20, 255, 1, '', ''],
Note: in profile.cfg Genre is a drop down list. Since it would have already been selected when the user registered, I don't want to offer the same option here, but to have the database automatically fill it in.
As a point of reference, this is how it looks in profile.cfg
'Genre' => [ 2, 'alpha', 0, 10, 1, 'High Seas', ''],
and then further down
%db_select_fields = (
'Genre' => 'Calm,High Seas,Hurricane,Fantasy'
);

In play_html.pl I tried
within sub html_record_form
<input type="hidden" NAME="Genre" VALUE="$rec{'Genre'}">
This is right after a similar hidden field for DJ which is the UserId field

Then in sub html_record after the following line
%rec2 = &get_record($rec{'DJ'});

I tried adding
%rec2 = &get_record($rec{'Genre'});
... This felt pretty shaky, I'm not sure which line should actually be telling it to get the info and this seemed promising. Well anyway, the end result of this was that when I tried to add a record in the playlists, I get a message which says that the field was not filled out. Actually it's hidden just as the DJ field is.
Let me know what other information I need to provide for consideration.

But now, the other part of that question ... I'll gladly wait until the Genre part is resolved, but I'm stating it here in case this suggests a different tactic entirely. There is one other field in the Playlists (multi) database that is a field that only the administrator can see. It is a checkbox field. The purpose for this field is so that I can select one playlist from each DJ to feature in a Genre search. In other words, this search form would show a drop down menu with the genres. A visitor would select one genre from the list, and when they click on the button, they would see a list with one playlist from each registered DJ in that particular genre.

Hopefully it's not as complicated as I probably just made it sound.

Thanks

Rich Newman

You want to have the search form on the static page, or the results on the static page, through SSI?

You can't have

%rec2 = &get_record($rec{'DJ'});

and

%rec2 = &get_record($rec{'Genre'});

&get_record only works on the key field of the database.

With the line %rec2 = &get_record($rec{'DJ'});, the entire record which is associated with that DJ is now in the %rec2 hash. What I would do is, first delete

%rec2 = &get_record($rec{'Genre'});

In its place, add

$rec{'Genre'} = $rec2{'Genre'};

With your other question, you can add a hidden field to the search form that has the value of whatever the checkbox field is when it's been checked.

IOW, if the field is named "Preferred" and the value that is added to the database when the checkbox is checked is "Yes," then add the following to your search form:

<input type="hidden" name="Preferred" value="Yes">



JPD
http://www.jpdeni.com/dbman/

Focusing now on one issue.
Thanks, but I guess there's still something that I'm missing. I'm still getting the "Genre (Can not be left blank)" message.
As you've suggested, I've added
$rec{'Genre'} = $rec2{'Genre'};
But that didn't work. Where I have it now is between the &switch_to lines

It occurred to me to alternatively try it after the switch back to the original database, but that didn't work either. If you need to look at the .pl file, I've put it at
http://www.tagyerit.com/...man/play_html_pl.txt
sub html_record_form starts at line 326
and
sub html_record starts at line 384

Thanks

Rich

I'm really confused as to what you're doing.

Don't you want the DJ to select the genre of the playlist? Or do you want the genre to come from the DJ's record?

If the former, you need to have a field where the DJ can enter the genre.

If the latter, you need to search the DJ's record before you print out the hidden field. You have the search in sub html_record, but not in sub html_record_form.

JPD
http://www.jpdeni.com/dbman/

Well as usual, it's really me that's confused ... I want the genre to come from the DJ's record (profile - the one). In other words, I have the DJ fill the genre out when he registers, and then it's one less thing for him to have to do when he adds new playlists. It also will help me maintain some consistency
Just so I'm clear, what you are saying is that I should have the following routine in sub html_record_form as well as in sub html_record ... or instead of having it in sub html_record or are you referring to something else entirely.

&switch_to_profile;
# Get the associated record
%rec2 = &get_record($rec{'DJ'});
$rec{'Genre'} = $rec2{'Genre'};
# Tell DBMan to go back to the original database
&switch_to_playlist;
($db_auto_generate and print &build_html_record(%rec) and return);

Or were you referring to something else?

Thanks
Rich

You should have the routine in sub html_record_form instead of having it in sub html_record.

Think for a minute about what you're doing. You are trying to pull in a value from the DJ's record to put into the playlist record. The only way you can put something into the playlist record is to enter it through the form. The only way to get something from the DJ's record is to "&get_record."



JPD
http://www.jpdeni.com/dbman/

I think I understand the logic about what you're saying, but moving that routine didn't change my results.
I'm still getting the "Genre can not be left blank message"
What I don't understand is that the program has no problem identifying the DJ and filling it in from the profile, but for some reason, it's not getting the genre information. Before I started to try to add the Genre field into the Playlist database, it was working fine when the &switch_to routines were in the
sub html_record.

Thanks
Rich

Can you post your sub html_record_form here so I can see what's going on?

JPD
http://www.jpdeni.com/dbman/

 
sub html_record_form {

my (%rec) = @_;

# &switch_to routine moved here from sub html_record 7/31
# Tell DBMan which database to look in
&switch_to_profile;
# Get the associated record
%rec2 = &get_record($rec{'DJ'});
$rec{'Genre'} = $rec2{'Genre'};
# Tell DBMan to go back to the original database
&switch_to_playlist;

($db_auto_generate and print &build_html_record_form(%rec) and return);

my $font_color = 'Font face="Verdana, Arial, Helvetica" Size=2 Color=#003399';

print qq|
<TABLE WIDTH="450" CELLPADDING=0 CELLSPACING=0 BORDER=1 BGCOLOR="#FFFFCC">

|;
if ($in{'signup_form'}) {
print qq|
<tr><td><$font><b>Password:</b></FONT></td>
<td><input type="PASSWORD" name="pw" value="$in{'pw'}"></td></tr>
|;
}
print qq|
<input type="hidden" NAME="PlayListID" VALUE="$rec{'PlayListID'}">

<input type="hidden" NAME="DJ" VALUE="$rec{'DJ'}">
<input type="hidden" NAME="Genre" VALUE="$rec{'Genre'}">
<TR><TD ALIGN="Right" VALIGN="TOP" WIDTH="90"><$font_color>DateAdded:</FONT></TD>
<TD VALIGN="TOP" WIDTH="360"> <INPUT TYPE="TEXT" NAME="DateAdded" SIZE="20" VALUE="$rec{'DateAdded'}" MAXLENGTH="255"></TD></TR>
<TR><TD ALIGN="Right" VALIGN="TOP" WIDTH="90"><$font_color>Playlist:</FONT></TD>
<TD VALIGN="TOP" WIDTH="360"> <TEXTAREA NAME="Playlist" ROWS="14" COLS="40" WRAP="VIRTUAL" MAXLENGTH="6000">$rec{'Playlist'}</TEXTAREA></TD></TR>
|;
if ($per_admin) {
print qq|
<TR><TD ALIGN="Right" VALIGN="TOP" WIDTH="90"><$font_color>Favorite:</FONT></TD>
<TD VALIGN="TOP" WIDTH="360"> |; print &build_checkbox_field("Favorite",$rec{'Favorite'}); print qq|</TD></TR>
|;
}
else {
print qq|
<input type="hidden" NAME="Favorite" VALUE="$rec{'Favorite'}">
|;
}
print qq|
</TABLE>
|;
}

Okay. Let's see if it's getting a record at all.

After

$rec{'Genre'} = $rec2{'Genre'};

add

(Don't worry. This won't stay. This is just for debugging purposes.)

You should get a listing of the fields in the profile database, plus all of the values for the current DJ.

JPD
http://www.jpdeni.com/dbman/

Here's the results: All of the fields, including Genre have the correct info, but the error message remains.
This is the information that appears before the input table ...

Error: Unable to Add Record

There were problems with the following fields:

Genre (Can not be left blank)

Please fix any errors and submit the record again.

DJ -- rich
Date_Posted -- 29-Jul-2000
Genre -- Fantasy
Station_ID -- Wabbit Wadio
Broadcast_Schedule -- in my mind
URL -- http://www.tagyerit.com
DJ_Profile -- My info's here
Email -- rich@tagyerit.com

This makes no sense at all -- unless.....

In sub switch_to_profile, do you have a space either before or after "Genre"? All of the field names must be separated only by commas. So you should have

DJ,Date_Posted,Genre,Station_ID,Broadcast_Schedule,URL,DJ_Profile,Email

There should be no spaces at all in this line.

JPD
http://www.jpdeni.com/dbman/

I finally figured it out (sorta - maybe). While you didn't hit it, you got me to thinking ... since the record was obviously being read then the problem was that the form was looking for something to be filled out that just wasn't there. I went over to the playlist.cfg (the many) and changed the Genre not_null field to 0. Presto. it works. Since the field is already required and filled out in the profile.cfg (the one), there was no need to make the routine look for it. I don't understand why the same isn't true for the DJ field (except that the DJ is the key field), but I'm not complaining.
Contrary to our previous discussion, I had to move search back to sub html_record instead of having it in sub html_record_form in order to get it to fill out the fields.

So now, I'm back to the other half of my original question in this series ...
One of the searches, I was hoping would be a simple keyword search that would return results from both databases. If I have to, I can offer 2 search options for the keyword search, which I have temporarily set up to work, but it would definitely be tidier if I could figure out how to do it with one.

To which you replied
The search form will be on the static page. (I'm not quite ready for SSI, although I hear it will be a good tool to learn ... Soon)

Really Thanks
Rich

That just doesn't make any sense to me. If you're filling out the fields on a form, you have to get the values to put into the fields while you're in the subroutine that prints out the form.

Putting the code in sub htm_record will display the value from the other database, but it won't put it into the playlist database. If you want to search for Genre in the playlist database, you won't be able to, because the field will be empty.

You can search on one database at a time. You can display the related record(s) from the results of a search, but you can't search both databases at the same time.

Can you go over in detail what you want your search to do?

JPD
http://www.jpdeni.com/dbman/

I agree it makes no sense.
I think I need to back up a bit to an earlier thread. At the time, I was trying to get the DJ field automatically filled in after they've signed in and ready to add another playlist (the many).
You suggested the following
I had trouble with that. At first I set $auth_user_field=1 in both cfg files. I kept getting The DJ field cannot be left blank message there too. I guess I thought I had snuck around it by making $auth_user_field=0 in both cfg and leaving the DJ field hidden.
But now I realise that what I have allows me to display all of the information, the way I'd expect it, but the search does not include the DJ field (or now the Genre field either). Of course since that was 3 weeks ago, it's tough to recall other variations that I tried.

Something tells me that if I resolve at the $auth_user_field stage, some of my problems will clear up.

Rich

The userid field should always be a required field.

The only thing I can think of that might be causing a problem is spaces between the field names in sub switch_to_profile.

That, and the fact that you need to assign the variable --

$rec{'DJ'}=$rec2{'DJ'};


JPD
http://www.jpdeni.com/dbman/

SIGH! This is all getting murkier. Now that I've changed back to $auth_user_field=1, another problem seems to have materialized. Now I am unable to sign up new accounts. I'm gettingthe Invalid userid message. "Must only contain only letters and be less than 12 and greater than 3 characters." which is not the problem."
Since I've only made this one change, I wonder if the problem that's messing me up is a carry-over from some earlier unresolved item that I had previously missed (or since changed)?
What's the best way to trouble shoot here. I believe this problem now exists without any relational stuff involved.

Well, the thing to do is to look at the line that generates the message. I'm not sure what other mods you may have added, so I'm not sure what it will be. There will be a variable in the line -- something that probably starts with $in{. Then back up and look at the form that brought you to the subroutine with that line. Make sure there is a field that has the same name as the variable.

For example, if your variable is $in{'userid'}, you must have a field in your form called userid.

This is as much as I can tell you without knowing what sort of mods you have added.

JPD
http://www.jpdeni.com/dbman/

Well, I've got everything under control using saved back copies of files, reviewing notes etc. Also abandoned and/or simplified a few things. Even added the "modify multiple records" mod (very cool)

Thank You Thank You Thank You

For what it's worth, my skills at trouble-shooting, saving backups of every step, and commenting my changes - improved immensely over the past 3 months. You wouldn't believe how many stupid questions I caught before hitting send.
This has been a great experience for me.
And if you haven't got the picture already, I really can't thank you enough.

Rich

I'm glad I could help. And even "gladder" to know that you've really learned along the way. That's what I really like to see.

Thanks for letting me know that the multiple modify mod works. I hadn't tried it and the person I wrote it for hasn't said anything.



JPD
http://www.jpdeni.com/dbman/