mysqli INSERT anomaly

Question

I have the following table:

ID: bigint autoinc
NAME: varchar(255)
DESCRIPTION: text
ENTRYDATE: date

I am trying to insert a row into the table. It executes without error but nothing gets inserted in database.

try {
    $query = "INSERT INTO mytable (NAME, DESCRIPTION, ENTRYDATE) VALUES(?,?,?)";
    $stmt = $conn->prepare($query);
    $name= 'something';
    $desc = 'something';
    $curdate = "CURDATE()";
    $stmt->bind_param("sss", $name, $desc, $curdate);
    $stmt->execute();
    $stmt->close();
    $conn->close();
    //redirect to success page
}
catch(Exception $e) {
    print $e;
}

It runs fine and redirects to success page but nothing can be found inside the table. Why isn't it working?

As a side note, you can have the SQL server perform the `NOW()` function instead of taking an extra step in your php. — Jonno_FTW, Mar 03 '11 at 10:50

score 2 · Answer 1 · edited May 23 '17 at 12:28

2

Are you sure there is no error? There seems to be a typo in your column name for example.

Note that PDO is extremely secretive about errors by default.

See How to squeeze error message out of PDO? on how to fix this.

edited May 23 '17 at 12:28

Community

1
1

answered Mar 03 '11 at 10:44

Pekka

442,112
142
972
1,088

The typo is here in this post only. My real query is fine. Sorry for that. I just corrected it. I am using **mysqli** not **PDO** – Deepak Shrestha Mar 03 '11 at 10:50
@Deepak ah, I see. Sorry. The point still stands though: An invalid query will not throw an exception – Pekka Mar 03 '11 at 10:51

Linus Kleen · Answer 2 · 2011-03-03T11:27:12.460

2

Try preparing this query instead:

"INSERT INTO mytable (NAME, DESCRIPTION, ENTRYDATE) VALUES(?,?,CUR_DATE())"

And check the results of $stmt->execute(). It would have given you a warning that "CUR_DATE()" (sic) is not a valid DATE.

You can check if a statement was correctly executed by checking the return value of execute() and querying the errorInfo() method:

if (!$stmt->execute()) {
   throw new Exception($stmt->errorInfo(), stmt->errorCode());
}

Be aware that upon failure, execute() does not throw an exception automagically. You'll have to check for successful operation and failure for yourself.

edited Mar 03 '11 at 11:27

answered Mar 03 '11 at 10:44

Linus Kleen

33,871
11
91
99

Just tried it. There seems to be error. It doesn't redirect but no error is shown. Isn't that my catch exception should spit out errors? Any idea? Thanks – Deepak Shrestha Mar 03 '11 at 11:06
I just check with this code and got 'object(mysqli_stmt)#2 (0) { }'. What is this? Seems like CURDATE() have something to do with this. Any thought? Thanks for your time. – Deepak Shrestha Mar 03 '11 at 11:56

Saul · Accepted Answer · 2011-03-03T11:42:45.340

What about replacing DESCTIPTION with DESCRIPTION inside the $query?

Edit

Just out of curiosity, I created a table called mytable and copy-pasted your code into a PHP script.

Here everything worked fine and rows got inserted, except that the binded parameter CURDATE() did not execute properly and the ENTRYDATE cell was assigned 0000-00-00.

Are you sure you are monitoring the same database and table your script is supposedly inserting to?

What happens when going with error_reporting(E_ALL); ?

Have you verified that the script actually completes the insertion?

The following appears to be working as expected:

error_reporting(E_ALL);

try {
    $query = "INSERT INTO mytable (NAME, DESCRIPTION, ENTRYDATE) VALUES (?, ?, CURDATE())";
    $stmt = $conn->prepare($query);
    $name= 'something';
    $desc = 'something';
    $stmt->bind_param("ss", $name, $desc);
    $stmt->execute();

     if ($conn->affected_rows < 1) {
        throw new Exception('Nothing was inserted!');
     }

    $stmt->close();
    $conn->close();
    //redirect to success page
}
catch(Exception $e) {
    print $e->getMessage();
}

yes I have a separate config.php file for that. Just recheked and its fine. error_reporting(E_ALL); doesn't show any error still. — Deepak Shrestha, Mar 03 '11 at 11:56
Thanks! this code works now. I tried the CURDATE() inside as suggested and just bound other parameters. It works now. Thanks Again. — Deepak Shrestha, Mar 03 '11 at 12:13

score 0 · Answer 4 · answered Mar 03 '11 at 10:46

0

Is it possible that autocommit is OFF?

If so then you have to commit your insert like so

/* commit transaction */
$conn->commit();

Regards

answered Mar 03 '11 at 10:46

Jeremy S.

6,423
13
48
67

tried it but it still show successful but no entry in table. Any thoughts? – Deepak Shrestha Mar 03 '11 at 11:02

mysqli INSERT anomaly

4 Answers4