on INSERT an empty row BEFORE the data is inserted

[Umm_Hasan]

Hi all,

I have an html form that is processed with a php page which in turn inserts the user input to the mySQL db.

All data is inserting correctly BUT, an empty row is inserted *before* the data row. I had this problem before and it was due to my html form (I had accidently replaced a text statement with a semi colon - i dont know how - maybe my 2yr old pressed the key or somthing when it was highligheted) anyway, once I fixed that probelem all went well but my date was still not showing up.

To get the date to show in mySQL DATETIME field, I added a now() function to the Date field in my INSERT query.

Once I did this, all data including the date entered the DB properly BUT I started getting an extra empty row.

In addition to this, b4 adding the now() function and after adding it, I've been getting a mysql error like this:
Error: Duplicate entry '4' for key 1

I did look up that error and found that it seems to be a myphpadmin bug and there is a fix for it BUT, I have no idea how to apply that fix or patch to myphpadmin.

Any suggestions for both problems?

Here's the PHP:

$sql="INSERT INTO Registration2011 (Level, LastName, FirstName, Grade, DOB, eMail, Phone, Address, City, State, Zip, ParentsFirstName, ParentsLastName, Location, Date)
VALUES
('$level','$lname','$fname','$grade','$dob','$email','$ph','$addy','$city','$st','$zip','$ptFname','$ptLname','$location',now())";
mysql_query($sql);

$pid=mysql_insert_id();

$sql="INSERT INTO Partners2011 (ID, pLevel, PartnerLastName, PartnerFirstName, PartnerGrade, PartnerDOB, PartnerEmail, PartnerPhone, PartnerAddress, PartnerCity, PartnerState, PartnerZip, PartnerParentFirstName, PartnerParentLastName, Date)
VALUES
('$pid','$level','$pLname','$pFname','$pGrade','$pDob','$pEmail','$pPh','$pAddy','$pCity','$pSt','$pZip','$ppFname','$ppLname',now())";
mysql_query($sql);

if (!mysql_query($sql, $con))
 {
 die('Error: ' . mysql_error());
 }
 

Here are the tables:

CREATE TABLE `Registration2011` (
 `ID` mediumint(10) NOT NULL auto_increment,
 `Level` char(50) NOT NULL,
 `LastName` char(50) NOT NULL,
 `FirstName` char(50) NOT NULL,
 `Grade` char(2) NOT NULL,
 `DOB` char(10) NOT NULL,
 `eMail` char(50) NOT NULL,
 `Phone` char(15) NOT NULL,
 `Address` char(100) NOT NULL,
 `City` char(50) NOT NULL,
 `State` char(10) NOT NULL,
 `Zip` tinyint(5) NOT NULL,
 `ParentsFirstName` char(50) NOT NULL,
 `ParentsLastName` char(50) NOT NULL,
 `Location` char(20) NOT NULL,
 `Date` datetime NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `Level` (`Level`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 

CREATE TABLE `Partners2011` (
 `ID` mediumint(10) NOT NULL,
 `pLevel` char(50) NOT NULL,
 `PartnerLastName` char(50) NOT NULL,
 `PartnerFirstName` char(50) NOT NULL,
 `PartnerGrade` char(2) NOT NULL,
 `PartnerDOB` char(20) NOT NULL,
 `PartnerEmail` char(50) NOT NULL,
 `PartnerPhone` char(15) NOT NULL,
 `PartnerAddress` char(100) NOT NULL,
 `PartnerCity` char(20) NOT NULL,
 `PartnerState` char(10) NOT NULL,
 `PartnerZip` tinyint(5) NOT NULL,
 `PartnerParentFirstName` char(50) NOT NULL,
 `PartnerParentLastName` char(50) NOT NULL,
 `Date` datetime NOT NULL,
 PRIMARY KEY (`ID`),
 CONSTRAINT `Partners2011_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `Registration2011` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

[makue10]

See the reply of that Post :

[webmasterworld.com ]

I think that you missing that part
if (!empty($something)) {

// and after that to $_POST your variables... and run your queries...

}

hope that help you

[Umm_Hasan]

makue........thanks for the reply. I did read throught that post you directed me to; however, that is not the case in my problem. This would be the case if the field is empy (not selected) but in my case, every field including my select box is filled out and/or selected/and or checked. Actually, all of the fields in my form are required and I am the one completing the form since its not live yet. I am in the testing phase before the form goes live. And I have done at least 20 runs of the form and every time, being SURE all Post values are completed, I have the same problem - the extra empty row BEFORE the complete data row.

As for the error i'm getting, it seems that it may be related to the fact that I am writing the value taken from the last querty to the forign key field via mysql_insert_id();
but, I am not sure thats the problem. I will comment that out and see if the error goes away.........but I still need to solve the extra empty row problem even if that gets rid of the error. Why? Because this database is assigning registration numbers (the ID fields) to participants in a competition and their numbers will be printed on thier name tags during competition testing and the finalist competition day and we dont want to have several numbers skipped out on because of these extra rows or we will get multiple blank lables once the info is exported to a csv file and used to print the lables.

Any more help would be appreciated!

[rocknbil]

This is not a phpmyadmin bug. Look:

CREATE TABLE `Registration2011` (
`ID` mediumint(10) NOT NULL auto_increment,

then you do

$pid=mysql_insert_id();

$sql="INSERT INTO Partners2011 (ID,...

VALUES
('$pid'

You are probably trying to insert a value into an autoincrement field and that value already exists. Either remove autoincrement, or let it do it's job and don't insert into ID.

Don't know why it's doubling, can't see the programming logic . . .

[Umm_Hasan]

Thank rocknbil for your reply.

The field is NOT an auto_incremmented field; however, it is a forign key to Registration2011.ID but when I dont put the $pid as a value for the Partners2011 ID column it throws a forign key constraint error.

I'm considering just totally taking out the forign key and auto incrementing the Partners ID field and doing an INSERT INTO level_table (fname, lname) FROM Registrants2011 FirstName, LastName WHERE Level = level (whatever level I need it to insert into based on the users selection) and doing the same for the partners table.

I figured if I did this then for sure the two current tables (Registration2011 and Partners2011) will work just find with what I have taking out the $pid and let the partners ID auto increment BUT, the hesitation is that each Registrant (student) MUST have a Partner (another student) to compete as a team and it is a requirment for this competition.

The logic behind the table is to collect all user input for both the Registrant and their Partner keeping their ID (registration #) the SAME FOR BOTH the Registrant and their Partner and laster query out the selected Levels of participation.

Any ideas?

[timster]

It might be time to broaden your net a bit. An insert like that should insert just one row, that is pretty fundamental to the way SQL works. My suspicion is that your code is being executed more than once. For example, if your page submits to itself, it might be inserting an empty row when you view the form, and a full one when you submit it.

You might want to log messages to a file to get a better idea of what is going on. (Searching for PHP logging will pull up some good tutorials.)

[Umm_Hasan]

Thanks timster, i'll search for it and try it out. My form actually submits to a php page that ONLY holds the php (hence form action="registration_processor.php" method="post") and the form is on a XHTML only page (registration2011.html)

I'm am a newbie to php and mysql so i wasnt aware that it would only insert ONE row, I thought it would insert ALL rows that contain a Level field that has the value of whatever table its going into here's what I wrote up so you can see what I mean:

//build pk_kg table
$sql="INSERT INTO PK_KG (rFname, rLname)
 SELECT FirstName, LastName
 FROM Registration2011 WHERE Level = PKG";
mysql_query($sql);
$sql="INSERT INTO PK_KG (pFname, pLname)
 SELECT PartnerFirstName, PartnerLastName
 FROM Partners2011 WHERE pLevel = PKG";
mysql_query($sql);

//build 1to3 table
$sql="INSERT INTO 1to3 (rFname, rLname)
 SELECT FirstName, LastName
 FROM Registration2011 WHERE Level = 1to3";
mysql_query($sql);
$sql="INSERT INTO 1to3 (pFname, pLname)
 SELECT PartnerFirstName, PartnerLastName
 FROM Partners2011 WHERE pLevel = 1to3";
mysql_query($sql);

//build 4to6 table
$sql="INSERT INTO 4to6 (rFname, rLname)
 SELECT FirstName, LastName
 FROM Registration2011 WHERE Level = 4to6";
mysql_query($sql);
$sql="INSERT INTO 4to6 (pFname, pLname)
 SELECT PartnerFirstName, PartnerLastName
 FROM Partners2011 WHERE pLevel = 4to6";
mysql_query($sql);

//build 7to9 table
$sql="INSERT INTO 7to9 (rFname, rLname)
 SELECT FirstName, LastName
 FROM Registration2011 WHERE Level = 7to9";
mysql_query($sql);
$sql="INSERT INTO 7to9 (pFname, pLname)
 SELECT PartnerFirstName, PartnerLastName
 FROM Partners2011 WHERE pLevel = 7to9";
mysql_query($sql);

//build 10to12 table
$sql="INSERT INTO 10to12 (rFname, rLname)
 SELECT FirstName, LastName
 FROM Registration2011 WHERE Level = 10to12";
mysql_query($sql);
$sql="INSERT INTO 10to12 (pFname, pLname)
 SELECT PartnerFirstName, PartnerLastName
 FROM Partners2011 WHERE pLevel = 10to12";
mysql_query($sql);
/****************END LEVEL BUILD*****************/

Maybe, based on this its obvious i'm a novice but the idea for me is to figure out how to do what I need to do now since i'm on a deadline of this project which is supposed to have live registration in mid April.

Yes this gives me a month BUT I have to get everything working correctly into the DB first, then write all my javascript for the client side to require all fields and verify data, then get some server side cleansing/verification going and working right, then I need to send the FULLY FUNCTIONAL version to all who are working on this (this is a volunteer ONLY project - everyone involved is volunteering thier services) and have them test the form and the leaders will then let me know what he/she likes/dislikes then I have to apply all changes if any, make sure those changes if any work, and have the registration process ready to be fully functional for USERS (many will be teens so it has to be full idiot proof) by mid april -- hence my need to post in forums and not just read, try, read try, read try...ya know.

Again, thanks all for your time and assistance!

[timster]

Ah rats, I should have read your code more closely. "Insert...select" will indeed insert multiple rows.

But there's one way to find out: log or echo

mysql_affected_rows()

after the queries in question. That will report the number of rows inserted by the individual queries.