Reputation: 1511
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
Reputation: 76280
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
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
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 throughPDO::prepare()
orPDO::query()
and invoke an error on the statement handle,PDO::errorInfo()
will not reflect the error from the statement handle. You must callPDOStatement::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
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