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, May 18 '11 at 17:49
I would suggest using the SET notation rather than the VALUES notation. Also, escape your strings, for goodness' sakes! — Luke Sneeringer, May 18 '11 at 17:50
Remember to escape / clean any variables going into a query. — Aaria Carter-Weir, May 18 '11 at 17:53
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, May 18 '11 at 17:53
@Joe Phillips easier to read, and similar to the commonly used UPDATE notation. — Aaria Carter-Weir, May 18 '11 at 17:53
@Luke Can't disagree with that. However, if you insert multiple rows at once you can't use SET notation and I'm not sure if switching between the two is the best idea. Trade offs I suppose. — Joe Phillips, May 18 '11 at 17:55
Your function is getting called twice. I see no reason why this function would insert two rows when called once. Think about using a data-mapper pattern instead of something this.. at any rate.. something more OOP. — Aaria Carter-Weir, May 18 '11 at 17:59
@Joe Phillips, fair point. How often do you insert multiple rows though? I mostly don't, and if I do, I'm happy enough to run them through as separate queries. Perhaps a more OOP pattern would allow some clever differentiating between a single insert and multiple row insert. — Aaria Carter-Weir, May 18 '11 at 18:01
@Luke, what makes set better than values? [There seems to be no difference](http://stackoverflow.com/questions/861722/mysql-insert-into-table-values-vs-insert-into-table-set/861729#861729). — Majid Fouladpour, May 18 '11 at 18:01
@Majid: There are only two differences: Readability and a guarantee that `SET` notation will only ever insert 0 or 1 rows. — Dereleased, May 18 '11 at 18:10
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, May 18 '11 at 18:41
`SET` cannot be used out of MySQL though (if I am not wrong). `INSERT ... VALUES ...` and `INSERT ... SELECT ...` are standard SQL. — ypercubeᵀᴹ, Dec 04 '11 at 12:39

score 2 · Answer 1 · answered May 18 '11 at 17:51

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.

MySQL/PHP - inserting an extra row on INSERT statement

1 Answers1