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.

Recommended Answers

All 5 Replies

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')");

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?

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:

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

You're welcome!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.