filtering for duplicate database entries

[jmack159]

so in a portion of my current project, i need to collect information from the end user about the unit they belong to. i want to screen the information to make sure that i don't get duplicate entries for the same unit. the info i collect is:
unit_type
unit_number
unit_locale //city/town
unit_council
so i should compare all these attributes to the ones in the db, right? but here's the problem: there can be two of the same unit type/number combinations within the council, and to make it worse the unit locals' could potentially have different values for one unit. for example:
unit1: Troop 33, Central New Jersey Council, could be referred to as: west trenton,

Ewing, or Trenton
unit2: Troop 33, Central New Jersey Council, could be referred to as: Milltown, North Brunswick
they are two separate units, but both have multiple possible locale values, and are in the same council
so what would be the best way to filter out if the unit currently registering is already in the db? if every property is an exact match, obviously just use that; but what if the town is different? i've been messing around with a script which returns an array of possible matches, that the user can choose between the matches and their own info. but it is clumsy, and cumbersome, and still if the user picks their own info, i will still have unit duplicates in the database!:glare: i need to keep out duplicates b/c i need to track donations from the units, and a count of how many times they've used our services. i need a more elegant solution here. any ideas here? any thoughts are greatly appreciated! thanks!
running:
php 5.2.0
mysql 5.0.27
apache 2.2.3

--Josh

[bpat1434]

Well, what are you tracking? The word troop leads me to the Boy Scouts of America groups, which means that they have to be chartered with their regional council (i.e. Baltimore Area Council for mine).

Now, if that's true, then what you should do is limit the input that can be allowed. Instead of leaving a free text-area for city/town, ask for a list of charters from the council so you can create this. Then just list the cities that troops are chartered in in a drop-down box. This way, you can say okay, Troop 33 from Elizabeth is also the same Troop 33 from Brick is also the same Troop 33 from New Brunswick (forgive my terrible NJ geography).

That's one thing. Now, on the other hand, you could also manually enter these troops and just have the end users select the one they're a part of. And if the city/town can vary so much, just limit it to duplicates in the council, unit #, and unit type.

For example (we're going to use Baltimore Area Council BSA info for now) there are about 4 different "types" of units in the BAC: Cub Scouts, Boy Scouts, Venture Crew, Explorers. Now, for each of these 4 types, there is usually 1 troop number across them (mine was 733). So there is Pack 733, Troop 733, Crew 733, and Post 733 respectively.

Now, These units are all in the same Council (BAC). There might be another Council (National Area (Washington, D.C.)) that has another 4 items with unit numbers 733. The only difference is council. The towns / cities don't matter.

I'd say try and limit what you're comparing, or limit the user input. Obviously, limiting input can be risky; however, it can work to your advantage in that you'll always know what info is coming in.

If I had more information, I could probably help you more, but I think I hit some good points. Hopefully I helped you a bit.

[jmack159]

thanks for the feedback! your correct with pretty much all your assumptions posted above, so i think you pretty well understand the problem. i was in scouts too, so i know the structure tree of the BSA very well. this is what i'm working with now for this problem:

//inside of a processing function for the form in question:
//this comes after error checking the fields
//check if the unit info is in the DB already, if so let the user 
		//choose which info is correct
		if(($result = $database->unitInDB($u_type, $u_num, $locale, $council)) 
			&& !($form->num_errors > 0)){
			$i = 0;
			while($unit = $result->fetch_assoc()){
				if($unit[$i]['location'] == $locale){
					//if it is an exact match, just use the db version
					continue 2;
				}
				$_SESSION['possUnit'][] = $unit;
				$i++;
			}
			$_SESSION['possUnit'] = array_unique($_SESSION['possUnit']);
			$form->setError('uniq_unit', " ");
		}

//this is $database->unitInDB();
function unitInDB($type, $num, $locale, $council){
		$q = "SELECT * FROM ".TBL_UNITS
			." WHERE unit_type = '$type' && unit_num = '$num' && council = '$council'";

	$result = $this->mysqli->query($q) or die(mysql_error());



	if($result->num_rows > 0){//there is a unit in the db that matches the criterion
		return $result;
	}else{
		return false;
	}
}

i'm having trouble with this because it doesn't 'continue' like it should the first snippett, and its feels kind-of clumsy moving back and forth between my processing pages and the page with the form. i was thinking of maybe implementing an ajax auto-complete field for city that narrows down based on type/num/council combos, but i haven't really tackled the beast of ajax yet, and some of my tinkering around with ajax in the past hasen't been to successful. plus, that wouldn't solve the problem anyway, b/c the user could still enter what ever they like. getting a charter roster and limiting that way could work, but i don't like the idea of how much possible editing of that data there could be, and that seems like an aweful lot of 'dead' data that would just sit around, never get used, and just plain take up space. plus, the form could be used by other types of scouting groups, not just the BSA, so the problem with that one is multiplied. so, i think the best way to would be to check against previously registered units. any suggestions?

[bpat1434]

With the continue, I'm guessing that there's a second while or for or switch statement above the shown while() loop?

If you really want to tinker with AJAX, but don't want to develop your own library, look at jQuery or Prototype. I prefer jQuery, but Prototype is just as good. Their AJAX libraries are pretty complete, and easy to use.

Now, how to deal with this, I guess you could do it much the same way as email systems or IM systems do with new registrants. Show a page of possible matches (same unit numbers, different locals) and let them choose, or let them fill in a new one. You could go one step further and maybe keep some Scoutmaster or Troop info in the database to present to the user. Something like:

Troop 733
Scoutmaster: Brice Hart
Founded In: 1951
Website: www.bsatroop733.org

Troop 460
Scoutmaster: Jim Morton
Founded In: -Unknown-
Website: www.bsatroop460.org

That way, they can say: Oh, Bryce is my Scoutmaster!! That's my troop (or pack or post, or crew, or whatever). And they'll know kind of right there rather than inputting a new unit. You could also add that as part of the search criteria. Have them give their leaders name (or theirs if they are the leader).

Hope that helps!!

[jmack159]

sorry i haven't replied in a while, been busy with school and work.... you know how it goes. but i did manage to solve my problem. i actually got in a code writing storm and rewrote a lot of the back end of the app to store all the info in a complex object, whereas before I was using a messy $_SESSION system to store the values as the user went through the multi-page form, but it got confusing fast! this simplified a lot, and i was able to solve my problem!😃 thanks for the help though!

                                         ~RESOLVED~

[bpat1434]

Please mark this thread as resolved then 🙂 No problem!! Ever need any other help, just come on back.