MySQL/PHP - inserting an extra row on INSERT statement

Question

I am working on a small PHP + MySQL application. I have run into an issue where one of my insert statements seems to cause MySQL to add an additional somewhat blank row after each insert. Here's my setup:

I have a function that does the insert:

function addFacCertification($facultyID,$month,$year,$completed,$certificatesent,$confirmationtodean,$comments)
    {
        //echo "$facultyID<br>$month<br>$year<br>$completed<br>$certificatesent<br>$confirmationtodean<br>$comments";
        $today = getdate(); 
        $month = $today["mon"];
        $date = $today["mday"];
        $year = $today["year"]; 
        $curdate = "$year" ."-". "$month" . "-" . "$date";
        $sql = mysql_query("INSERT INTO facultycertification (facultyID,cmonth, cyear, completed, certificatesent, confirmationtodean, comments, certifiedon)
                            VALUES ('$facultyID', '$month', '$year', '$completed', '$certificatesent', '$confirmationtodean','$comments', '$curdate')");
        return $sql;
    }

Function call:

$sqlresults = addFacCertification($_POST["facultyID"], $_POST["month"], $_POST["year"], $_POST["completed"], $_POST["csent"],
                                                $_POST["ctodean"], $_POST["comments"]);

Problem is, every time this is run - I get an extra row in my table: (See second row below) Image is here:

Any ideas why? Here is the table structure:

 id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    facultyID INT NOT NULL,
    cmonth INT NOT NULL,
    cyear INT NOT NULL,
    completed INT NOT NULL,
    certificatesent INT NOT NULL,
    confirmationtodean INT NOT NULL,
    comments TEXT,
    certifiedon DATE,
    FOREIGN KEY (facultyID) REFERENCES faculty(id)

PHP and MySQL won't magically duplicate rows, so something is executing your function twice. Either your script runs twice, or something's doubling up on the function calls. You may want to put a call to print_r(debug_backtrace()) in there to see what the execution stack looks like each time. — Marc B
– Marc B, Commented May 18, 2011 at 17:49
I would suggest using the SET notation rather than the VALUES notation. Also, escape your strings, for goodness' sakes! — Luke Sneeringer
– Luke Sneeringer, Commented May 18, 2011 at 17:50
I don't see anything in the code you posted, perhaps there is something offensive later on? Notice how, in the database, only the month,year and certifideon columns are receiving non-garbage data. Are you sure you're not calling it a second time with no arguments somewhere? Also, ESCAPE ESCAPE ESCAPE your inputs! — Dereleased
– Dereleased, Commented May 18, 2011 at 17:53
As several other stated on my behalf, the reason I suggested SET notation is because if you know you're only inserting one row, it's easier to read. Also, because his values are unescaped (which I also noted), it's even remotely possible that what he thinks is inserting as one row is really two. I expect that this is not really the problem, which is why I made a comment rather than an answer, and that the real issue is that his function is getting called twice (as others have noted). Similarly, I understand that VALUES notation does have value (har har) in some cases. — Luke Sneeringer
– Luke Sneeringer, Commented May 18, 2011 at 18:41

James C · Accepted Answer · 2011-05-18 17:51:34Z

2

I would imagine that your code is somehow being executed twice. I'd suggest the following (in this order) to debug the issue:

Uncomment that echo inside the function and see if it echos out twice (i.e. you're calling the function twice)
If you're running this via a web request tail the web server log to see if the request is being made twice
Enable general logging or enable profiling on the MySQL server to see what SQL queries are actually run against the server

You could have something like a MySQL trigger on the db doing this if you didn't set it up yourself but I think this is unlikely.

answered May 18, 2011 at 17:51

James C

14.2k1 gold badge37 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL/PHP - inserting an extra row on INSERT statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related