WordPress MySQLi CRUD example

PHP, MySQLi CRUD example

<?php
function log_d($tag, $msg) {
	$file = fopen('my-debug.log','a');
	fwrite($file, $tag.':'.$msg."\r\n");
	fclose($file);
}

function conn() {
	$host = "localhost";
	$user = "root";
	$pw = "autoset";
	$db = "wordpress";

	// Create connection
	$conn = new mysqli($host, $user, $pw, $db);
	// Check connection
	if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	}
	return $conn;
}

function create_table() {
	$conn = conn();
	// sql to create table
	$sql = "CREATE TABLE MyGuests (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
	firstname VARCHAR(30) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	email VARCHAR(50),
	reg_date TIMESTAMP
	)";

	if ($conn->query($sql) === TRUE) {
		log_d('테이블 생성', "Table MyGuests created successfully");
	} else {
		log_d('오류', "Error creating table: " . $conn->error);
	}

	$conn->close();
}

function insert(){
	$conn = conn();
	$sql = "INSERT INTO MyGuests (firstname, lastname, email)
	VALUES ('John', 'Doe', 'john@example.com')";

	if ($conn->query($sql) === TRUE) {
		$last_id = $conn->insert_id;
		log_d('레코드 추가', "New record created successfully. Last inserted ID is: " . $last_id);
	} else {
		log_d('추가 오류',  "Error: " . $sql . "<br>" . $conn->error);
	}

	$conn->close();
}

function insert_multiple(){
	$conn = conn();
	$sql = "INSERT INTO MyGuests (firstname, lastname, email)
	VALUES ('John', 'Doe', 'john@example.com');";
	$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
	VALUES ('Mary', 'Moe', 'mary@example.com');";
	$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
	VALUES ('Julie', 'Dooley', 'julie@example.com')";

	if ($conn->multi_query($sql) === TRUE) {
		log_d('다중 레코드 추가', "New record created successfully.");
	} else {
		log_d('다중 추가 오류',  "Error: " . $sql . "\r\n" . $conn->error);
	}

	$conn->close();
}

function prepare() {
	$conn = conn();
	// prepare and bind
	$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
	$stmt->bind_param("sss", $firstname, $lastname, $email);
	// i:integer, s:string, d:double, b:blob
	
	// set parameters and execute
	$firstname = "John";
	$lastname = "Doe";
	$email = "john@example.com";
	$stmt->execute();

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

	$firstname = "Julie";
	$lastname = "Dooley";
	$email = "julie@example.com";
	$stmt->execute();

	log_d('레코드 추가(prepare)',  "New records created successfully");

	$stmt->close();
	$conn->close();
}

function select(){
	$conn = conn();
	$sql = "SELECT id, firstname, lastname FROM MyGuests";
	$result = $conn->query($sql);

	if ($result->num_rows > 0) {
		// output data of each row
		while($row = $result->fetch_assoc()) {
			log_d('select',  "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "\r\n");
		}
	} else {
		log_d('select',  "0 results");
	}
	$conn->close();
}

function update() {
	$conn = conn();
	$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=5";

	if ($conn->query($sql) === TRUE) {
		log_d('update', "Record updated successfully");
	} else {
		log_d('update',  "Error updating record: " . $conn->error);
	}

	$conn->close();
}

function delete() {
	$conn = conn();
	// sql to delete a record
	$sql = "DELETE FROM MyGuests WHERE id=4";

	if ($conn->query($sql) === TRUE) {
		log_d('delete',  "Record deleted successfully");
	} else {
		log_d('delete',   "Error deleting record: " . $conn->error);
	}

	$conn->close();
}


/*
$sql = "SELECT * FROM Orders LIMIT 30";
$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";
$sql = "SELECT * FROM Orders LIMIT 15, 10";//위 문장과 동일한 결과
*/

//create_table();
//insert_multiple();
//prepare();
//select();
//update();
delete();
?>