Languages
[Edit]
EN

PHP - how to make MySQL insert query with PDO?

8 points
Created by:
Root-ssh
175020

In PHP it is possible to make SQL INSERT query with PDO in following way.

1. INSERT query with PDO example

<?php

	if(isset($_GET['name']) && isset($_GET['role']))
	{
		$name = $_GET['name'];
		$role = $_GET['role'];

		$db_name = 'test';
		$db_host = '127.0.0.1'; // 'localhost'
		$db_username = 'root';
		$db_password = 'root';

		$dsn = 'mysql:dbname=' . $db_name . ';host=' . $db_host . ';charset=utf8';
		$pdo = new PDO($dsn, $db_username, $db_password);

		$query = 'INSERT INTO `users` (`name`, `role`) VALUES (:name, :role)';
		$statement = $pdo->prepare($query);

		if($statement === FALSE)
			die('Query preparation error!');

		$parameters = array(
			'name' => $name,
			'role' => $role
		);

		if($statement->execute($parameters))
		{
			$count = $statement->rowCount();

			if($count > 0)
			{
				$id = $pdo->lastInsertId('id');
				
				echo 'Last added user has id ' . $id . '.';
			}
			else
				echo 'Add user operation error!';
		}
		else
			echo 'Query execution error!';
	}

?>

Running:

http://localhost/add-user.php?name=Matt&role=moderator

Result:

SQL INSERT query with PDO class - PHP / MySQL.
SQL INSERT query with PDO class - PHP / MySQL.

Database:

Database state after SQL INSERT query with PDO class - HeidiSQL.
Database state after SQL INSERT query with PDO class - HeidiSQL.

2. Database preparation

create_tables.sql file:

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`role` VARCHAR(15) NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

insert_data.sql file:

INSERT INTO `users`
	(`name`, `role`)
VALUES
	('John', 'admin'),
	('Chris', 'moderator'),
	('Kate', 'user'),
	('Denis', 'moderator');

See also

  1. PHP - how to prevent SQL injection?
  2. PHP - how to make MySQL select query with PDO?
  3. PHP - how to make MySQL update query with PDO?
  4. PHP - how to make MySQL delete query with PDO?
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

PHP - MySQL

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join