Home>
Prerequisites/Things to be realized

I want to INSERT into MySQL database using PDO connection with PHP7.4, but no data is added.
I tried to find out the cause, but I couldn't understand the cause without displaying any errors.

Applicable source code
$err = [PDO :: ATTR_ERRMODE =>PDO :: ERRMODE_EXCEPTION, PDO :: ATTR_EMULATE_PREPARES =>false];
// Error display setting: Display all except notification
error_reporting (E_ALL&~ E_NOTICE);
try {
    // Connect to DB
    $dbh = new PDO ('mysql:'. DB_NAME. ';'. DB_HOST, DB_USER, DB_PASSWORD, $err);
    if ($dbh == null) {
        print ('Connection failed.');
    }
    else {
        print ('Connected');
    }
    $a1 = 'abcdef';
    $a2 = 'ghijklm';
    $a3 = 'nopqrst';
    // Create SQL
    $sql = "INSERT INTO sample.data (one, two, three)
    VALUES ('". $A1."', '". $A2."', '". $A3."');";
    // SQL execution)
    $res = $dbh->query ($sql);
    print_r ($res);
}
catch (PDOException $e) {
    print ('ERROR:'. $e->getMessage ());
    exit;
}
Execution result (print_r output contents)
Connected. PDOStatement Object
(
    [queryString] =>INSERT INTO sample.data (one, two, three)
        VALUES ('abcdef', 'ghijklm', 'nopqrst');
)

First of all, I checked the if statement to see if an error occurred at the database connection stage. The message "Connected" was displayed, so I think that the connection was successful.
Next, the result obtained by displaying the contents of $dbh->query ($sql) (INSERT INTO sample.data (one, two, three)
VALUES ('abcdef', 'ghijklm', 'nopqrst');) was inserted when logging in mysql and copying.

Supplemental information

PHP7.4
MySQL8.0
FreeBSD12.1

Append

Assuming that the IP address on the side running PHP is 192.168.1.100 and the IP address of the connection destination (MySQL server to be connected) is 192.168.1.101.
echo 'mysql:'. DB_NAME. ';'. DB_HOST;results in
mysql: sample;It was 192.168.1.101.
As a result of checking again, there was a table named data in the database named sample at 192.168.1.100, but it seems that the INSERTs so far have been on this side. The database on 192.168.1.101 is not INSERTed as usual.

  • Answer # 1

    The cause seems to have been a mistake when creating an instance of PDO.

    mysql: '. DB_NAME.';'. DB_HOST

    mysql: dbname = testdb;host = localhost;
    Try adjusting so that the output looks like this.

    Reference:
    Knowledge necessary for MySQL connection with PHP (minimum version)

  • Answer # 2



    Does table sample.data really exist?
    The mysql system seems to enclose identifiers in back quotes, but try it.

    // Create SQL
        $sql = "INSERT INTO` sample.data` (`one`,` two`, `three`) VALUES (: one,: two,: three);";
        // SQL execution)
        $sth = $dbh->prepare ($sql);
        $res = $sth->execute ([
            ': one' =>$a1,
            ': two' =>$a2,
            ': three' =>$a3,
        ]);
        print_r ($res);
    Try it like

    .