Блокнот — @дневники: асоциальная сеть

<?php
$servername = "localhost";
$username = "root";
$password = "";

try {
	$conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Connected successfully";
}
catch (PDOException $e) {
	echo "Connection failed: " . $e->getMessage();
}

?>

Создать БД

<?php
$servername = "localhost";
$username = "root";
$password = "";

try {
	$conn = new PDO ("mysql:host=$servername", $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$sql = "CREATE DATABASE myDBPDO";
	$conn->exec($sql);
	echo "Database created successfully<br>";
}
catch (PDOException $e){
	echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

?>

Создать таблицу

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDBPDO";

try {
	$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$sql = "CREATE TABLE MyGuests3 (
		id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		firstname VARCHAR(30) NOT NULL,
		lastname VARCHAR(30) NOT NULL,
		email VARCHAR(50),
		reg_date TIMESTAMP
		)";
	$conn->exec($sql);
	echo "Table MyGuests3 created successfully";
}
catch (PDOException $e) {
	echo $sql . "<br>" . $e->getMessage();
}

?>

Добавить запись в таблицу

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDBPDO";

try {
	$conn = new PDO("mysql:host=$servername; dbname=$dbname", $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$sql = "INSERT IGNORE INTO MyGuests3 (firstname, lastname, email)
	VALUES ('Sam', 'Doe', 'john@example.com')";
	$conn->exec($sql);
	echo "New record created successfully";
}
catch (PDOException $e) {
	echo $sql . "<br>" . $e->getMessage;
}

$conn = null;

?>

Получить id последнего вставленного элемента

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDBPDO";

try {
	$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$sql = "INSERT IGNORE INTO MyGuests3 (firstname, lastname, email)
VALUE ('Bucky', 'Barns', 'steve@mail.ru')";
	$conn->exec($sql);
	$last_id = $conn->lastInsertId();
	echo "New record created successfully. Last inserted ID is: " . $last_id;
}
catch (PDOException $e) {
	echo $sql . "<br>" . $e->getMessage();
}
$conn = null;

?>

Добавить несколько записей в таблицу

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDBPDO";

try {
	$conn = new PDO("mysql:host=$servername;dbname=$dbname",$username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	$conn->beginTransaction();
	$conn->exec("INSERT IGNORE INTO MyGuests3 (firstname, lastname, email) 
	VALUES ('John', 'Doe', 'john@example.com')");
	$conn->exec("INSERT IGNORE INTO MyGuests3 (firstname, lastname, email) 
	VALUES ('Mary', 'Moe', 'mary@example.com')");
	$conn->exec("INSERT IGNORE INTO MyGuests3 (firstname, lastname, email) 
	VALUES ('Julie', 'Dooley', 'julie@example.com')");
	$conn->commit();
	echo "New records created successfully";
}
catch (PDOException $e) {
	$conn->rollback();
	echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Prepared Statements in PDO

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDBPDO";

try {
	$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	$stmt = $conn->prepare("INSERT IGNORE INTO MyGuests3 (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");
	$stmt->bindParam(':firstname', $firstname);
	$stmt->bindParam(':lastname', $lastname);
	$stmt->bindParam(':email', $email);
	
	$firstname = "John";
	$lastname = "Doe";
	$email = "john@example.com";
	$stmt->execute();

	$firstname = "Mary";
	$lastname = "Moe";
	$email = "mary@example.com";
	$stmt->execute();

	$firstname = "Julie";
	$lastname = "Doolie";
	$email = "julie@example.com";
	$stmt->execute();
	
	echo "New records created successfully";
}
catch (PDOException $e) {
	echo "Error: " . $e->getMessage();
}

$conn = null;

?>

@темы: MySQL, PHP, PDO

URL