Simple PDO MySQL insert

[rouse]

Apparently I don’t know how to insert a row into a MySQL data base using PDP and I would like to know what I am missing
This PHP PDO script does not insert data into a database. I would like to know why. It does not error out but the data base is not populated and the return value is apparently undefined.

Below is the table:

CREATE TABLE animals (
  Id INT(11) NOT NULL AUTO_INCREMENT,
  animal_type VARCHAR(32) DEFAULT NULL,
  animal_name VARCHAR(32) DEFAULT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

Below is the PHP script:

<?php

/* mysql hostname */
$hostname = 'localhost';
/* mysql username */
$username = 'root';
/* mysql password */
$password = 'secret';
/* mysql database name */
$dbname = 'site';

try {
    $dbh = new PDO("mysql:host=$hostname;$dbname", $username, $password);
    /* echo a message saying we have connected */
    echo ('Connected to database <br />');
} catch (PDOException $e) {
    echo $e->getMessage();
    echo ('<br />');
}

echo ('<br />');
try {
    /* INSERT data */
    $count = 0;
    $count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi' 'roy'");

    /* echo the number of affected rows */
    echo ("Number of rows inserted = " . $count . '<br />');

    /* close the database connection */
    $dbh = null;
} catch (PDOException $e) {
    echo $e->getMessage();
    echo ('<br />');
}

Your insight would be appreciated.

[cereal]

Hi, you're missing the closing parenthesis in the VALUES() statement and the commas to separate the data:

$count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi' 'roy'");

Should be:

$count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi', 'roy')");

[rouse]

Cereal:
When I replace your statement with mine, the behavior is still the same, no value for $count and the data is not inserted into the database. Have you tried this script and looked at the database?

[cereal]

Have you tried this script and looked at the database?

No, I don't need to test this.

Fix the database connection string:

$dbh = new PDO("mysql:host=$hostname;$dbname", $username, $password);

It's missing the dbname= attribute, It should be:

$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);

Also, to raise the exceptions you must set the attribute right after you define $dbh:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The default behaviour is PDO::ERRMODE_SILENT:

• http://php.net/manual/en/pdo.error-handling.php

[rouse]

cereal:
Thanks so very much. I appreciate you assistance.
WBR

[cereal]

You're welcome!