CODEWITHSUNDEEP
phpmysqlpdo
will
will

Reputation: 1511

PDO insert successful but not?

I am trying to insert into a MySQL table with PDO. Code:

public function sendMail($to, $body) {
    /* this all works
     *
    error_reporting(E_ALL);
    ini_set('display_errors', '1');
    $stmt = $this->database->prepare("SELECT * FROM users");
    $stmt->execute();
    $fetch = $stmt->fetchAll();
    print_r($fetch);
     */

    $stmt = $this->database->prepare("INSERT INTO 
        inbox_unread (date, body, to, from) VALUES (:date, :body, :to, :from)");
    echo $stmt->bindParam(":date", date("Y-m-d H:i:s"));
    echo $stmt->bindParam(":body", $body);
    echo $stmt->bindParam(":to", $to);
    echo $stmt->bindParam(":from", $_SESSION['username']);
    if ($stmt->execute()) {
        echo "yes";
    } else {
        print_r($this->database->errorInfo());
    }
}

This is the output I get:

1111Array ( [0] => 00000 [1] => [2] => )

Database:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `sandbox`
--

-- --------------------------------------------------------

--
-- Table structure for table `inbox_unread`
--

CREATE TABLE IF NOT EXISTS `inbox_unread` (
  `mail_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `body` text NOT NULL,
  `to` varchar(255) NOT NULL,
  `from` varchar(255) NOT NULL,
  PRIMARY KEY (`mail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

'00000' apparently means successful, but it shouldn't be printing if it is successful. Nothing is inserted to the database. I'm sure it's a typo but I can't find it.

SQL statement:

INSERT INTO inbox_unread (date, body, to, from) VALUES ('2013-01-04 23:40:58', 'test', 'will', 'will')

Upvotes: 2

Views: 1914

Answers (4)

Shoe
Shoe

Reputation: 76300

Replace this line:

echo $stmt->bindParam(":date", date("Y-m-d H:i:s"));

with these:

$date = date("Y-m-d H:i:s");
echo $stmt->bindParam(":date", $date);

because bindParam() bind variable by reference not by value.

Upvotes: 1

mario
mario

Reputation: 145512

You have a reserved word in your query:

   $stmt = $this->database->prepare("INSERT INTO 
    inbox_unread (date, body, to, `from`) VALUES (:date, :body, :to, :from)");

The Stackoverflow syntax highlighter actually made it obvious. With generic column names like that, it might be advisable to put them all in backticks perhaps.

Upvotes: 3

inhan
inhan

Reputation: 7470

Assuming your values are all non-empty and valid (that you've already used var_dump() to make sure their values are gathered as expected)…

First, you might wanna use $stmt->errorInfo() instead of $this->database->errorInfo() since you're using a prepared statement. The documentation, in the final paragraph of the "Return Values" section reads:

PDO::errorInfo() only retrieves error information for operations performed directly on the database handle. If you create a PDOStatement object through PDO::prepare() or PDO::query() and invoke an error on the statement handle, PDO::errorInfo() will not reflect the error from the statement handle. You must call PDOStatement::errorInfo() to return the error information for an operation performed on a particular statement handle.

Another thing you can do is, var_dump the $stmt->execute() call, so that you will have an exact idea about what it returns.

Lastly, mario is right about turning the error reportings on. Take a look at PDO Errors and error handling in documentation, if you like more detailed information about these.

Upvotes: 2

Daryl Gill
Daryl Gill

Reputation: 5524

Although I'm not that experienced with the Binds, The manual for your binds go against what you have done.

http://php.net/manual/en/pdostatement.bindparam.php

Try removing your echo so your script looks like so: 

   $stmt->bindParam(":date", date("Y-m-d H:i:s"));
   $stmt->bindParam(":body", $body);
   $stmt->bindParam(":to", $to);
   $stmt->bindParam(":from", $_SESSION['username']);

Upvotes: 0

Related Questions