Duplicates

[luiddog]

I have a mysql database of users. When someone signs up they put in their info, including email. But sometimes people sign up twice, with the same email. When they look up their password it sends them their first entry. What I'm try to do is find multiple entries so I can delete their first sign up so when they look for their password it will send the right one to them.

Is there a SELECT function that will show me any multiple entries for email? That is how do I find duplicate entries? Maybe a GROUP function that only shows groups >1.

[natman3]

my guess would be to write in some type of check within the function that you use to have them INSERT data into the table. By first using a SELECT statment to see if they have data already maybe something like:

function save_profile_date($ident = "", $lang = "", $address = "", $pc = "", $city = "", $state = "", $phone = "", $fax = "", $hp = "", $notes = "", $field1 = "", $field2 = "", $field3 = "", $field4 = "") {
		if ($_SESSION['is_rec']) {
			$sql = sprintf("INSERT INTO %s (id, users_id, language, address, postcode, city, state, phone, fax, homepage, notes, %s, %s, %s, %s, last_change) VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())",
				PROFILE_TABLE, TBL_USERFIELD_1, TBL_USERFIELD_2, TBL_USERFIELD_3, 
				TBL_USERFIELD_4, $_SESSION['user_id'], $this->ins_string($lang), $this->ins_string($address), 
				$this->ins_string($pc), $this->ins_string($city), $this->ins_string($state), $this->ins_string($phone), 
				$this->ins_string($fax), $this->ins_string($hp), $this->ins_string($notes), $this->ins_string($field1),
				$this->ins_string($field2), $this->ins_string($field3), $this->ins_string($field4));
		}	
		if (mysql_query($sql) or die (mysql_error())) {
			$this->profile_id = (!$_SESSION['is_rec']) ? mysql_insert_id() : $ident;
			$this->the_msg = $this->extra_text(2);
		} else {
			$this->the_msg = $this->extra_text(3);
		}
	}

this is built wihin a class but it might help in giving you an idea

here is the function that could do the checking and setting

function get_profile_data() {
		$this->get_user_info();
		$_SESSION['user_id'] = $this->id;
		$sql = sprintf("SELECT id, language, address, postcode, city, state, phone, fax, homepage, notes, %s AS field_one, %s AS field_two, %s AS field_three, %s AS field_four FROM %s WHERE users_id = %s", TBL_USERFIELD_1, TBL_USERFIELD_2, TBL_USERFIELD_3, TBL_USERFIELD_4, PROFILE_TABLE, $this->id);
		$result = mysql_query($sql) or die (mysql_error());
		if (mysql_num_rows($result) == 0) {
			$_SESSION['is_rec'] = false;			
			$this->the_msg = $this->extra_text(1);
		} else {
			$_SESSION['is_rec'] = true; // this session acts like an extra controle
			while ($obj = mysql_fetch_object($result)) {
				$this->profile_id = $obj->id;
				$this->language = $obj->language;
				$this->address = $obj->address;
				$this->postcode = $obj->postcode;
				$this->city = $obj->city;
				$this->state = $obj->state;
				$this->phone = $obj->phone;
				$this->fax = $obj->fax;
				$this->homepage = $obj->homepage;
				$this->notes = $obj->notes;

			// remember the constants in the db_config file
			$this->field_one = $obj->field_one; 
			$this->field_two = $obj->field_two;
			$this->field_three = $obj->field_three;
			$this->field_four = $obj->field_four;
		}
	}
}

[luiddog]

That's a good idea but I still need to clean up the database from people who have already signed up. But thanks for the idea I should have a check when they are signing up. Thanks.

[bradgrafelman]

Here's a quote from the MySQL Refernce Manual (link):

Posted by Count Henry De Havilland-Fortesque-Smedley on January 13 2004 6:41am

If you want to find duplicates on a field that hasn't been uniquely indexed, you can do this:

SELECT BookISBN, count(BookISBN) FROM Books GROUP BY BookISBN HAVING COUNT(BookISBN)>1;

[luiddog]

Thanks that's it. Thanks again.

[bradgrafelman]

No problem.

Don't forget to mark this thread RESOLVED using the link below.