EN
PHP - how to make MySQL insert query with PDO?
8 points
In PHP it is possible to make SQL INSERT
query with PDO in following way.
xxxxxxxxxx
1
2
3
if(isset($_GET['name']) && isset($_GET['role']))
4
{
5
$name = $_GET['name'];
6
$role = $_GET['role'];
7
8
$db_name = 'test';
9
$db_host = '127.0.0.1'; // 'localhost'
10
$db_username = 'root';
11
$db_password = 'root';
12
13
$dsn = 'mysql:dbname=' . $db_name . ';host=' . $db_host . ';charset=utf8';
14
$pdo = new PDO($dsn, $db_username, $db_password);
15
16
$query = 'INSERT INTO `users` (`name`, `role`) VALUES (:name, :role)';
17
$statement = $pdo->prepare($query);
18
19
if($statement === FALSE)
20
die('Query preparation error!');
21
22
$parameters = array(
23
'name' => $name,
24
'role' => $role
25
);
26
27
if($statement->execute($parameters))
28
{
29
$count = $statement->rowCount();
30
31
if($count > 0)
32
{
33
$id = $pdo->lastInsertId('id');
34
35
echo 'Last added user has id ' . $id . '.';
36
}
37
else
38
echo 'Add user operation error!';
39
}
40
else
41
echo 'Query execution error!';
42
}
43
44
Running:
xxxxxxxxxx
1
http://localhost/add-user.php?name=Matt&role=moderator
Result:

Database:

create_tables.sql
file:
xxxxxxxxxx
1
CREATE TABLE `users` (
2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3
`name` VARCHAR(100) NOT NULL,
4
`role` VARCHAR(15) NOT NULL,
5
PRIMARY KEY (`id`)
6
)
7
ENGINE=InnoDB;
insert_data.sql
file:
xxxxxxxxxx
1
INSERT INTO `users`
2
(`name`, `role`)
3
VALUES
4
('John', 'admin'),
5
('Chris', 'moderator'),
6
('Kate', 'user'),
7
('Denis', 'moderator');