Has anyone used multiple fields to create a "composite" key. I realize that this requires a designer to build the key based upon information given and then insert it into the "key" field after validation, etc.

Anyone tried or doing this?

You could simply turn of $db_key_track by setting the variable to 0. And then change the value of the $db_key field when the record is about to be validated.

Regards,

Eliot Lee

Where's the "proper" place to change $db_key? Do you have an example?

default.cfg

Regards,

Eliot Lee

I understand that is where I set db_tracking to 0, but where is the best place to modify/update the key value?

Same file...look for $db_key.

Regards,

Eliot Lee

OK, I ready to admit the "DAAR" (dumb as a rock) mentality. I see both the $db_key and $db_key_track in the default.cfg. But I can't seem to get my noodle around taking two fields, call them "date" and "church", concatenate them to use as the key field. Can I merely code "$db_key = 'date' . 'church'"? Or do I have to get fancy.

No...the $db_key is the PRIMARY key for that database meaning that you can only use ONE field.

If you want to have a "relational" structure where you have a foreign key from one table in another, then you should consider using the Relational Mod.

Regards,

Eliot Lee

Your previous message was ...
<<No...the $db_key is the PRIMARY key for that database meaning that you can only use ONE field.

If you want to have a "relational" structure where you have a foreign key from one table in another, then you should consider using the Relational Mod.>>

I was afraid that this idea was going off on a tangent due to my explanation and terminology. Let's try again from the beginning.

I understand the idea of a "relational" data structure, but for my purposes I believe it to be too "elegant." What I need to be able to do is generate a unique key based upon several fields the user fills in. That is basically a "composite key."

A composite key is a single and unique key generated from several non-unique fields. For my current dilemma, I would use a 4-digit field (named 'Year') and an 40 character alphanumeric (named 'Church'). Neither field is unique by themselves, ie. many records with same value for 'Year' and a few records with the same value for 'Church.' Building a single key field which IS unique merely involves concatenating the two fields together and then inserting them into the $db_key field in DBMan.

A relational structure only adds to the difficulty of this solution, when really all I need is the ability to generate a unique key (similar to the internal record id number) from data passed from the user on the "add record" function.

Now for the question. Where is the best place to do the value modification? I can't seem to get all the pieces together in one location.

Sorry about all the details and length, but I figured that it was easier to start over.

Stan Lewis

Hello Stan, perhaps this FAQ may be of help to you. http://webmagic.hypermart.net/dbman/fields3.htm#9

I am not certain ... the + (plus sign) is only applicable for numerical entries, I believe it is && for alpha characters. Minor change on the code.

The examples you're using in the request leads me to have some concern on using this new, combined field as your db key field however. You've indicated several records could have the same entry "church" and several with the same entry on "year" - Could there then end up being two records with the same "churchyear" entry? If so, this would defeat the db key function which must be unique for all records.

I think this thead may also help you. In the FAQ noted below under the section "Syntax" there's a thread called " Figuring out how old files are auto-increment my strange hyphenated $db_key)"

Although this isn't exactly what you are looking for I believe it can be tweaked easily to give you what you want.

I use this in my classifieds database to have a unique prefix on my db_key depending on the category the ad is placed (although using relational mod).

But I think if you were able to figure out how to combine the function $prefix which would perhaps combine your year and church fields you might get the results you want.

Also check this thread also under the section "Syntax" and see if this would provide you with ideas: "Append number (key) to a series of letters"

Hope these references help you



Unoffical DBMan FAQ
http://webmagic.hypermart.net/dbman/

If all else fails:

Create an ID field (if you haven't already) and set $db_key to ID

eg: $db_key = 'ID';
(nothing fancy just yet)

Then, create your forms as usual, but make the ID key a hidden field, so people can't mess with it.

eg: <input type="hidden" name="ID" value="$rec{'ID'}">

Again, nothing to fancy yet, this is probobly how your database is already set up.

Now, open up db.cgi and in the sub-routine add_record, make the following red change to the green portion of code:

my ($output, $status, $counter);
# Set the userid to the logged in user.
($auth_user_field >= 0) and ($in{$db_cols[$auth_user_field]} = $db_userid);

$in{'ID'} = $in{'Year'} . $in{'Church'};
$in{'ID'} =~ s/ //g;

# First we validate the record to make sure the addition is ok.
$status = &validate_record;

What we have now done is taken the Church and Year field and joined them together to form an ID. This will only happen at the add stage, the joined ID will be pulled out of the record for modifying from then on. The second line of red code is merely throwing out any spaces that may turn up.

You will need to turn of key tracking, and you may want to concider also appending a random number to the ID. In the very off chance that an entry has the same Church/Year, they will be forced to enter one of the fields differently.

Good luck!

- Mark


Astro-Boy!!
http://www.zip.com.au/~astroboy/

<<I am not certain ... the + (plus sign) is only applicable for numerical entries, I believe it is && for alpha characters. Minor change on the code.

The examples you're using in the request leads me to have some concern on using this new, combined field as your db key field however. You've indicated several records could have the same entry "church" and several with the same entry on "year" - Could there then end up being two records with the same "churchyear" entry? If so, this would defeat the db key function which must be unique for all records.>>

Comment on first section. Yep, that's what I did, except to make sure that the "receiving" field was $in{$db_key} thereby receiving the composite value. The "operator" in my case was the "." concatenation operator.

As for the second part it is not a problem. While the year may not be unique nor the church, together they are unique or at least I want to enforce the combined key to be unique. I'm in the process of making it generic, ie. specified in the .cfg file.