My DB Is skipping a few entries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Ok... Here's my register.php

Code: Select all

<?php
include "conf_global.php";
require_once('ipbsdk/ipbsdk_class.inc.php');
$SDK =& new IPBSDK();

if(isset($_REQUEST['confirmteam'])) {
	//We have a few emails and usernames. Check in the database, if the emails already exist. If the email exists. Ask the user to enter a different email, if not proceed.
	$teamleaderemail = $_REQUEST['teamleaderemail'];
	$teamname = $_REQUEST['teamname'];
	$teamleadername = $_REQUEST['teamleadername'];
	$teamleadernumber = $_REQUEST['teamleadernumber'];
	$college = $_REQUEST['college'];
	$city = $_REQUEST['city'];
	$bryr = $_REQUEST['by'];
	$state = $_REQUEST['state'];
	$events_selected = $_REQUEST['events_selected'];
	$extra = $_REQUEST['extra'];
	
	for($i=1;$i<=$_REQUEST['count'];$i++) {
		$member[$i] = $_REQUEST['member'.$i];
		$email[$i] = $_REQUEST['email'.$i];
		$college_other[$i] = $_REQUEST['college'.$i];
	}
	//The code below gets executed when there is a duplicate username or email or when the member is already a member of our project.
	$leaderflag = 0;
	if(!($teamleadername == "")) {
		if(!($id[0] = $SDK->create_account(str_replace(" ","",$teamleadername).'2007','roboveda',$teamleaderemail))) {
			//User exists because emails match. So, we'll do one thing. We'll get his username and then reset his password
			$id[0] = $SDK->email2id($teamleaderemail);
			$teamleaderuid = $SDK->id2name($id[0]);
			$SDK->update_password("roboveda",$id[0]);
			$leaderflag = 1;
		}
	}
	
	foreach($email as $key=>$value) {
		$teamflag[$key] = 0;
		if(!($value == "") && !($member[$key] == "")) {
			if(!($id[$key] = $SDK->create_account(str_replace(" ","",$member[$key]).'2007',"roboveda",$value))) {
				//User exists because emails match. So, we'll do one thing. We'll get his username and then reset his password
				$id[$key] = $SDK->email2id($value);
				$memberuid[$key] = $SDK->id2name($id[$key]);
				$SDK->update_password("roboveda",$id[$key]);
				$teamflag[$key] = 1;
			}	
		}
	}
	
	//At this stage, we have solved the problem of people having the same email. If they have the same email, we are getting his username and then resetting his password. If they don't have the same email, then an account is being create in the format abcdr, r being the suffix to every account create by a Roboveda user.
	if(!($teamleadername == "")) {
		if($leaderflag) {
			$username = $teamleaderuid;
		}
		else $username = str_replace(" ","",$teamleadername).'r';
		$sql_leader = "INSERT INTO ".$table_members."(member_id,username,password,email,single) VALUES('$id[0]','$username','roboveda','$teamleaderemail','0')";
		//echo $sql_leader;
		$result_leader = mysql_query($sql_leader) or die(mysql_error());
	}
		
	foreach($email as $key=>$value) {
		if(!($value == "")) {
			if($teamflag[$key]) {
				$username = $memberuid[$key];
			}
			else $username = str_replace(" ","",$member[$key]).'r';
			$sql_team = "INSERT INTO ".$table_members."(member_id,username,password,email,single) VALUES('$id[$key]','$username','roboveda','$value','0')";
			//echo $sql_team;
		$result_team = mysql_query($sql_team) or die(mysql_error());
		}
	}
	
	//We're done updating the small table. Its time for the super table update
	foreach($_REQUEST['events_selected'] as $value) {
		$events .= $value.", ";
	}
	
	$sql_robo = "REPLACE INTO ".$table_main."(team_name,events,team_leader_name,team_leader_email,member_id, team_leader_number,college,bryr,city,state,member_1,email_1,member_id_1, member_2,email_2,member_id_2,member_3,email_3,member_id_3, member_4,email_4,member_id_4,comments,col_1,col_2,col_3,col_4) VALUES('$teamname','$events','$teamleadername','$teamleaderemail', '$id[0]','$teamleadernumber','$college','$bryr','$city','$state', '$member[1]','$email[1]','$id[1]','$member[2]','$email[2]','$id[2]', '$member[3]','$email[3]','$id[3]','$member[4]','$email[4]','$id[4]','$extra', '$college_other[1]','$college_other[2]','$college_other[3]','$college_other[4]')";
	
	if($result_robo = mysql_query($sql_robo) or die(mysql_error())) {
		
		$sql_id = "SELECT team_id FROM ".$table_main." WHERE team_leader_name='$teamleadername'";
		$result_id = mysql_query($sql_id);
		$row_id = mysql_fetch_assoc($result_id);
		//echo "DONE!!";
		//echo $row_id['team_id'];
		header('Location: confirm.php?team_id='.$row_id['team_id']);
	}
	exit;
	
	
}


?>
And as for the database structure:

Code: Select all

Field  	Type   	Null  	Default
team_id 	mediumint(9) 	No  	  	 <--- This is an autoincrement
team_name 	varchar(150) 	No  	  	 
events 	varchar(150) 	No  	  	 
team_leader_name 	varchar(50) 	No  	  	 
member_id 	mediumint(9) 	No  	0  	 
team_leader_email 	varchar(50) 	No  	  	 
team_leader_number 	varchar(20) 	No  	  	 
college 	varchar(150) 	No  	  	 
bryr 	varchar(20) 	No  	  	 
city 	varchar(20) 	No  	  	 
state 	varchar(20) 	No  	  	 
member_1 	varchar(50) 	No  	  	 
email_1 	varchar(50) 	No  	  	 
member_id_1 	mediumint(9) 	No  	0  	 
member_2 	varchar(50) 	No  	  	 
email_2 	varchar(50) 	No  	  	 
member_id_2 	mediumint(9) 	No  	0  	 
member_3 	varchar(50) 	No  	  	 
email_3 	varchar(50) 	No  	  	 
member_id_3 	mediumint(9) 	No  	0  	 
member_4 	varchar(50) 	No  	  	 
email_4 	varchar(50) 	No  	  	 
member_id_4 	mediumint(9) 	No  	0  	 
comments 	text 	No  	  	 
col_1 	varchar(80) 	No  	  	 
col_2 	varchar(80) 	No  	  	 
col_3 	varchar(80) 	No  	  	 
col_4 	varchar(80) 	No  	  	 
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

8O
You should really avoid the need for wordwrap in your scripts.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

I will do that from now... :)
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

No one? :(
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

You only showed us the structure of one table--which table is it, table_members or table_main?? And which records seem to be disappearing? That is, from which table? We need to know exactly which INSERT or REPLACE statement is creating the records that you think may be disappearing.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Oh thats the table_main. The records are not there in the table_main table.
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

http://dev.mysql.com/doc/refman/4.1/en/replace.html

The REPLACE INTO statement, when executed to it's original use of replacing rows with duplicate keys/uniques, DELETES the row first, and then INSERTS a new row. This will cause unintended gaps in auto-incrementing fields because it will not use the original value of that field as the new value.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Yeah, I would suspect that REPLACE INTO statement. Frankly, I've never used it and I'm not really familiar with what it does, although I'm sure that what TheMoose said is correct. If it changes the primary key value and you have related records in the other table, you may THINK that records are disappearing when they are really still there, but with a different primary key, so if you are looking at a table Join, it may not appear. You could verify that by examing records at the end of the table. I don't have the time to try to fully understand your script, but if you are just trying to change certain values in a record, you should be using UPDATE, not REPLACE INTO.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Hmm... Yeah, the Team Leader field was supposed to be unique but now that you say, I think there would've been a few overlaps. :( I should'nt have used REPLACE in the first place at all...

But I still don't understand how it can skip records just like that... Can you have a look at this once:

Code: Select all

http://www.enigmaportal.com/forum/ipbsdk/display.php
Thats the display of the total registrations and as you can see on the left, there's a total random mash up...
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

REPLACE INTO shouldn't skip entries. It uses the primary key or unique indexes. The only way for it to skip an entry during a REPLACE INTO query is if you have problems with unique columns (which should have been dealt with separately!). Do you?
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

I'm afraid i didn't understand that :( Sorry...
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

superdezign wrote:REPLACE INTO shouldn't skip entries. It uses the primary key or unique indexes. The only way for it to skip an entry during a REPLACE INTO query is if you have problems with unique columns (which should have been dealt with separately!). Do you?
The wording from the MySQL manual says otherwise though. It tries to insert, and if an error arises from a primary/unique duplication, it will delete the duplicate row(s) and try the insert again. Since the query he's using has no ID number set (since it's an auto_increment field), the new INSERT command takes the default value of that column, which becomes auto_increment+1

I just ran a test of the following:

Code: Select all

test_table (3 columns)
id        int(11)          primary    auto_increment
name      varchar(50)      unique
email     varchar(50)

INSERT INTO test_table (name,email) VALUES ("TheMoose", "myemail@email.com");
REPLACE INTO test_table (name,email) VALUES ("TheMoose", "myotheremail@email.com");
SELECT * FROM test_table (name,email);
You get the following:

Code: Select all

id   name        email
2    TheMoose    myotheremail@email.com
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Hmm.. Yeah I understand there's a problem with that REPLACE command but what I don't understand is that though the team_name is a unique field, why did I end up getting two entries like this:

Code: Select all

Team ID	   Team Leader Name	  Team Name	Events Opted

399	      B Sheshank	           MRECians	    YODDHA Max Participants: 3.......................

400	       K Mohan Krishna	         MRECians	  GATI Max Participants: 4...................
But Team Name is a unique field...
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

Make sure they are exactly the same, there might be extra "hidden" characters (not displayed in HTML rendering), such as newlines, linebreaks, tabs, whitespace, etc.

"MRECians" is not the same as " MRECians" or "MRECians ", but many times will display the same on a page.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Well, I've tried going into the database and tried editing the fields but I see both of them the same... Is there any other way I could check?
Post Reply