Searching extended field values - Login

Hi,

Just developing a site with membership registration where the users have an existing membership number. Using Login I was going to create an extended field memberNum to store their membership number.

Eventually what I’d like to do is match up their registration request with their membership number.

The first intermediate step for this would be to search the user listing by membership number. I had a look at the forotpassword snippet to see how it searches by email address rather than user name. Which led me to the getUserByField function, which I can see how it’s searching fields for a value. So I can see how to get it to search one of the standard database fields, eg. zip code for a match.

What I’m struggling with is that all the extended field keys and values are in the "extended" field as a single JSON string. Is there an easy way to search this field or am I going to have to loop over each user, extracting each JSON string and looking for a match on the memberNum key?

Regards

Adrian

If it turns out you have to do the looping construct, it’s going to be pretty in-efficient.

What are your chances of a false positive if you just do a pattern match on the JSON string (I don’t know that format, I guess I’m going to have to learn it). But if, for example, each field value in the JSON encoding is delimited in some way, then couldn’t you just pre-pend and append the delimiter to the member number and then do a pattern-match instead of an equality test?

Quote from: jrg at Jan 19, 2011, 04:04 PM

If it turns out you have to do the looping construct, it’s going to be pretty in-efficient.

What are your chances of a false positive if you just do a pattern match on the JSON string (I don’t know that format, I guess I’m going to have to learn it). But if, for example, each field value in the JSON encoding is delimited in some way, then couldn’t you just pre-pend and append the delimiter to the member number and then do a pattern-match instead of an equality test?

Fortunately it’s not a big club so there won’t be that many records to go through but I agree it seems a bit brute force solution.

Fortunately the JSON format is pretty simple so I don’t think it would be that hard to pull out the right key. It’s just unfortunate that MySQL doesn’t seem to be JSON aware, unlike some others e.g. mongo

At the moment I’m veering towards dropping the membership number into the "Fax" field, which as a standard field I can search on quickly. In the group none of us use fax so it’s a bit redundant otherwise.

Adrian

Quote from: apcherry at Jan 19, 2011, 06:29 PM

Quote from: jrg at Jan 19, 2011, 04:04 PM

If it turns out you have to do the looping construct, it’s going to be pretty in-efficient.

What are your chances of a false positive if you just do a pattern match on the JSON string (I don’t know that format, I guess I’m going to have to learn it). But if, for example, each field value in the JSON encoding is delimited in some way, then couldn’t you just pre-pend and append the delimiter to the member number and then do a pattern-match instead of an equality test?

Fortunately it’s not a big club so there won’t be that many records to go through but I agree it seems a bit brute force solution.

Fortunately the JSON format is pretty simple so I don’t think it would be that hard to pull out the right key. It’s just unfortunate that MySQL doesn’t seem to be JSON aware, unlike some others e.g. mongo

At the moment I’m veering towards dropping the membership number into the "Fax" field, which as a standard field I can search on quickly. In the group none of us use fax so it’s a bit redundant otherwise.

Adrian

Yes, that was the other thing I wondered about (co-opting a standard field). I suspect that is the best solution. There’s probably some lexicon entry somewhere that can be changed from "Fax" (or whatever it says) to "Membership Number".