トップへ(mam-mam.net/)

Connecting to MariaDB with PHP PDO | How to Insert, Update, Delete, and Retrieve Data

Japanese

Connecting to MariaDB with PHP PDO | How to Insert, Update, Delete, and Retrieve Data

Using PDO is the standard and recommended way to connect to MariaDB (MySQL) and work with data in PHP.
This article explains how to establish a database connection with PDO and covers the essential operations—INSERT, UPDATE, DELETE, and SELECT—along with practical, ready‑to‑use code examples.

Table Structure

The following table structure will be used as the sample for this tutorial. 

CREATE TABLE t_test(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(40),
  birth DATE,
  tall DECIMAL(5,2),
  section INT,
  up_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  reg_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY idx_t_test_id(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

+----------+--------------+------+-----+---------------------+-------------------------------+
| Field    | Type         | Null | Key | Default             | Extra                         |
+----------+--------------+------+-----+---------------------+-------------------------------+
| id       | bigint(20)   | NO   | PRI | NULL                | auto_increment                |
| name     | varchar(40)  | YES  |     | NULL                |                               |
| birth    | date         | YES  |     | NULL                |                               |
| tall     | decimal(5,2) | YES  |     | NULL                |                               |
| section  | int(11)      | YES  |     | NULL                |                               |
| up_date  | datetime     | NO   |     | current_timestamp() | on update current_timestamp() |
| reg_date | datetime     | NO   |     | current_timestamp() |                               |
+----------+--------------+------+-----+---------------------+-------------------------------+

Inserting Records

Inserting Records by Passing Parameters to execute

You can insert records (INSERT INTO) by passing a parameter array to
$stmt->execute(parameter array); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "INSERT INTO t_test(name, birth, tall, section) VALUES(?, ?, ?, ?)"
);
$stmt->execute( ["Smith", "2001-01-01", 175.2, 1] );
$stmt->execute( ["James", "2002-02-02", 165, 2] );
$stmt->execute( ["David", "2003-03-03", 170.1, 3] );


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  1 | Smith  | 2001-01-01 | 175.20 |       1 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  2 | James  | 2002-02-02 | 165.00 |       2 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  3 | David  | 2003-03-03 | 170.10 |       3 | 2024-02-14 11:11:15 | 2024-02-14 11:11:15 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Inserting Records Using bindValue with Question-Mark Placeholders

You can insert records (INSERT INTO) by passing parameters using
$stmt->bindValue(parameter ID starting from 1, value, type);
followed by $stmt->execute(); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "INSERT INTO t_test(name, birth, tall, section) VALUES(?, ?, ?, ?)"
);
// Bind the first parameter as a string
$stmt->bindValue(1, "Tanaka", PDO::PARAM_STR);
// Bind the second parameter as a string
$stmt->bindValue(2, "2004-04-04", PDO::PARAM_STR);
// Bind the third parameter as a string
$stmt->bindValue(3, 160.20, PDO::PARAM_STR);
// Bind the fourth parameter as an integer
$stmt->bindValue(4, 2, PDO::PARAM_INT);
$stmt->execute();


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  1 | Smith  | 2001-01-01 | 175.20 |       1 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  2 | James  | 2002-02-02 | 165.00 |       2 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  3 | David  | 2003-03-03 | 170.10 |       3 | 2024-02-14 11:11:15 | 2024-02-14 11:11:15 |
|  4 | Tanaka | 2004-04-04 | 160.20 |       2 | 2024-02-14 11:11:56 | 2024-02-14 11:11:56 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Inserting Records Using bindValue with Named Placeholders

You can insert records (INSERT INTO) by passing parameters using
$stmt->bindValue(parameter name, value, type);
followed by $stmt->execute(); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "INSERT INTO t_test(name, birth, tall, section) VALUES(:name, :birth, :tall, :section)"
);
// Bind the "name" parameter as a string
$stmt->bindValue("name", "Ito", PDO::PARAM_STR);
// Bind the "birth" parameter as a string
$stmt->bindValue("birth", "2005-05-05", PDO::PARAM_STR);
// Bind the "tall" parameter as a string
$stmt->bindValue("tall", 181.505, PDO::PARAM_STR);
// Bind the "section" parameter as an integer
$stmt->bindValue("section", 3, PDO::PARAM_INT);
$stmt->execute();


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  1 | Smith  | 2001-01-01 | 175.20 |       1 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  2 | James  | 2002-02-02 | 165.00 |       2 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  3 | David  | 2003-03-03 | 170.10 |       3 | 2024-02-14 11:11:15 | 2024-02-14 11:11:15 |
|  4 | Tanaka | 2004-04-04 | 160.20 |       2 | 2024-02-14 11:11:56 | 2024-02-14 11:11:56 |
|  5 | Ito    | 2005-05-05 | 181.51 |       3 | 2024-02-14 11:12:41 | 2024-02-14 11:12:41 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Updating Records

Updating Records by Passing Parameters to execute

You can update records (UPDATE) by passing a parameter array to
$stmt->execute(parameter array); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "UPDATE t_test SET section=? WHERE id=?"
);
$stmt->execute( [1, 3] );
$stmt->execute( [2, 2] );
$stmt->execute( [3, 1] );


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  1 | Smith  | 2001-01-01 | 175.20 |       3 | 2024-02-14 11:16:21 | 2024-02-14 11:11:14 |
|  2 | James  | 2002-02-02 | 165.00 |       2 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  3 | David  | 2003-03-03 | 170.10 |       1 | 2024-02-14 11:16:19 | 2024-02-14 11:11:15 |
|  4 | Tanaka | 2004-04-04 | 160.20 |       2 | 2024-02-14 11:11:56 | 2024-02-14 11:11:56 |
|  5 | Ito    | 2005-05-05 | 181.51 |       3 | 2024-02-14 11:12:41 | 2024-02-14 11:12:41 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Updating Records Using bindValue with Question-Mark Placeholders

You can update records (UPDATE) by passing parameters using
$stmt->bindValue(parameter ID starting from 1, value, type);
followed by $stmt->execute(); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "UPDATE t_test SET tall=? WHERE id=?"
);
// Bind the first parameter as a string
$stmt->bindValue(1, 194.4, PDO::PARAM_STR);
// Bind the second parameter as an integer
$stmt->bindValue(2, 4, PDO::PARAM_INT);
$stmt->execute();


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  1 | Smith  | 2001-01-01 | 175.20 |       3 | 2024-02-14 11:16:21 | 2024-02-14 11:11:14 |
|  2 | James  | 2002-02-02 | 165.00 |       2 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  3 | David  | 2003-03-03 | 170.10 |       1 | 2024-02-14 11:16:19 | 2024-02-14 11:11:15 |
|  4 | Tanaka | 2004-04-04 | 194.40 |       2 | 2024-02-14 11:25:47 | 2024-02-14 11:11:56 |
|  5 | Ito    | 2005-05-05 | 181.51 |       3 | 2024-02-14 11:12:41 | 2024-02-14 11:12:41 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Updating Records Using bindValue with Named Placeholders

You can update records (UPDATE) by passing parameters using
$stmt->bindValue(parameter name, value, type);
followed by $stmt->execute(); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "UPDATE t_test SET birth=:birth WHERE id=:id"
);
// Bind the "birth" parameter as a string
$stmt->bindValue("birth", "1990-09-09", PDO::PARAM_STR);
// Bind the "id" parameter as an integer
$stmt->bindValue("id", 5, PDO::PARAM_INT);
$stmt->execute();


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  1 | Smith  | 2001-01-01 | 175.20 |       3 | 2024-02-14 11:16:21 | 2024-02-14 11:11:14 |
|  2 | James  | 2002-02-02 | 165.00 |       2 | 2024-02-14 11:11:14 | 2024-02-14 11:11:14 |
|  3 | David  | 2003-03-03 | 170.10 |       1 | 2024-02-14 11:16:19 | 2024-02-14 11:11:15 |
|  4 | Tanaka | 2004-04-04 | 194.40 |       2 | 2024-02-14 11:25:47 | 2024-02-14 11:11:56 |
|  5 | Ito    | 1990-09-09 | 181.51 |       3 | 2024-02-14 11:33:25 | 2024-02-14 11:12:41 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Selecting Records

Selecting Records by Passing Parameters to execute

You can retrieve records (SELECT) by passing a parameter array to
$stmt->execute(parameter array); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "SELECT * FROM t_test WHERE id BETWEEN ? AND ?"
);
$stmt->execute( [2, 4] );
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo "{$row['id']}, {$row['name']}, {$row['birth']}, {$row['tall']}, {$row['section']}\r\n";
}


2, James, 2002-02-02, 165.00, 2
3, David, 2003-03-03, 170.10, 1
4, Tanaka, 2004-04-04, 194.40, 2

Selecting Records Using bindValue with Question-Mark Placeholders

You can retrieve records (SELECT) by passing parameters using
$stmt->bindValue(parameter ID starting from 1, value, type);
followed by $stmt->execute();
This example outputs 2 rows starting from the 4th row using LIMIT (offset, row_count). 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "SELECT * FROM t_test LIMIT ? , ?"
);
// Bind the first parameter as an integer
$stmt->bindValue(1, 3, PDO::PARAM_INT);
// Bind the second parameter as an integer
$stmt->bindValue(2, 2, PDO::PARAM_INT);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo "{$row['id']}, {$row['name']}, {$row['birth']}, {$row['tall']}, {$row['section']}\r\n";
}


4, Tanaka, 2004-04-04, 194.40, 2
5, Ito, 1990-09-09, 181.51, 3

Selecting Records Using bindValue with Named Placeholders

You can retrieve records (SELECT) by passing parameters using
$stmt->bindValue(parameter name, value, type);
followed by $stmt->execute(); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "SELECT * FROM t_test WHERE name LIKE :name"
);
// Bind the "name" parameter as a string
$stmt->bindValue("name", "%to%", PDO::PARAM_STR);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo "{$row['id']}, {$row['name']}, {$row['birth']}, {$row['tall']}, {$row['section']}\r\n";
}


5, Ito,   1990-09-09, 181.51, 3

Deleting Records

Deleting Records by Passing Parameters to execute

You can delete records (DELETE) by passing a parameter array to
$stmt->execute(parameter array); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "DELETE FROM t_test WHERE id BETWEEN ? AND ?"
);
$stmt->execute( [1, 2] );


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  3 | David  | 2003-03-03 | 170.10 |       1 | 2024-02-14 11:16:19 | 2024-02-14 11:11:15 |
|  4 | Tanaka | 2004-04-04 | 194.40 |       2 | 2024-02-14 11:25:47 | 2024-02-14 11:11:56 |
|  5 | Ito    | 1990-09-09 | 181.51 |       3 | 2024-02-14 11:33:25 | 2024-02-14 11:12:41 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Deleting Records Using bindValue with Question-Mark Placeholders

You can delete records (DELETE) by passing parameters using
$stmt->bindValue(parameter ID starting from 1, value, type);
followed by $stmt->execute(); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "DELETE FROM t_test WHERE tall BETWEEN ? AND ?"
);
// Bind the first parameter as a string
$stmt->bindValue(1, 190, PDO::PARAM_STR);
// Bind the second parameter as a string
$stmt->bindValue(2, 200, PDO::PARAM_STR);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo "{$row['id']}, {$row['name']}, {$row['birth']}, {$row['tall']}, {$row['section']}\r\n";
}


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  3 | David  | 2003-03-03 | 170.10 |       1 | 2024-02-14 11:16:19 | 2024-02-14 11:11:15 |
|  5 | Ito    | 1990-09-09 | 181.51 |       3 | 2024-02-14 11:33:25 | 2024-02-14 11:12:41 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Deleting Records Using bindValue with Named Placeholders

You can delete records (DELETE) by passing parameters using
$stmt->bindValue(parameter name, value, type);
followed by $stmt->execute(); 

<?php
$dns = "mysql:dbname=DatabaseName;host=HostName";
$pdo = new PDO($dns, "Username", "Password");
$pdo->query("SET NAMES utf8mb4");

$stmt = $pdo->prepare(
  "DELETE FROM t_test WHERE name LIKE :name"
);
// Bind the "name" parameter as a string
$stmt->bindValue("name", "It%", PDO::PARAM_STR);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo "{$row['id']}, {$row['name']}, {$row['birth']}, {$row['tall']}, {$row['section']}\r\n";
}


+----+--------+------------+--------+---------+---------------------+---------------------+
| id | name   | birth      | tall   | section | up_date             | reg_date            |
+----+--------+------------+--------+---------+---------------------+---------------------+
|  3 | David  | 2003-03-03 | 170.10 |       1 | 2024-02-14 11:16:19 | 2024-02-14 11:11:15 |
+----+--------+------------+--------+---------+---------------------+---------------------+

Arguments and Constants

Main Constants Available for bindValue

$stmt->bindValue(parameter name or ID, value, type);

PDO::PARAM_BOOL
Boolean type
PDO::PARAM_INT
Integer type (SMALLINT, INT, BIGINT, etc.)
PDO::PARAM_STR
String type (CHAR, VARCHAR, etc.)

Main Constants Available for fetch

$stmt->fetch(how the record should be returned);

PDO::FETCH_ASSOC
Returns each row as an associative array using column names as keys
PDO::FETCH_NUM
Returns each row as an array indexed by column numbers starting from 0
PDO::FETCH_BOTH
Returns each row as an array containing both column names and numeric indexes (meaning each column is returned twice)

PHP | Samples