#!/usr/bin/env php
<?php
error_reporting(E_ALL);
ini_set("display_errors", "on");

define("USER", "root");
define("PASS", "");
define("DB_NAME", "test"); // WARNING: Will be overwritten without prompt.

function query(mysqli $db, $query, $line) {
	$line = (int) $line;
	if (!mb_check_encoding($query, "UTF-8")) {
		fwrite(STDERR, "Error! Query is not properly encoded on line $line!\n");
		$db->close();
		exit(1);
	}
	$result = $db->query($query);
	if ($db->errno) {
		fwrite(STDERR, "Error! Database query failed in line $line:\n({$db->errno}) {$db->error}\n");
		if ($result instanceof mysqli_result) $result->free();
		$db->close();
		exit(1);
	}
	return $result;
}

// "\xF0\x9F\x99\x8B" is an emoji, but a perfectly valid UTF-8 character,
// referring to Unicode Point U+1F64B, "HAPPY PERSON RAISING ONE HAND", Unicode 6.0, Octobre 2010
// See: https://www.fileformat.info/info/unicode/char/1f64b/index.htm
$string = "\xF0\x9F\x99\x8B Huhu. wie geht es dir?";

$db = new mysqli("localhost", USER, PASS);
if ($db->connect_errno) {
	fwrite(STDERR, "Error! Failed to connect to database.\n({$db->connect_errno}) {$db->connect_error}\n");
	exit(1);
}

query($db, "SET CHARACTER SET utf8", __LINE__);
query($db, "SET NAMES utf8", __LINE__);
//query($db, "SET i_mean_it=TRUE");
query($db, "DROP DATABASE IF EXISTS `" . DB_NAME . "`", __LINE__);
query($db, "CREATE DATABASE `" . DB_NAME . "` CHARACTER SET utf8 COLLATE utf8_unicode_ci", __LINE__);
$db->select_db(DB_NAME);
if ($db->errno) {
	fwrite(STDERR, "Failed to switch to test database:\n({$db->errno}) {$db->error}\n");
	$db->close();
	exit(1);
}
query($db, "DROP TABLE IF EXISTS `test`", __LINE__);
query($db, "CREATE TABLE `test` (
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`string` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci", __LINE__ - 3);

$sql = "INSERT INTO `test` SET `string` = '" . $db->real_escape_string($string) . "'";
query($db, $sql, __LINE__);
echo "Query: $sql\n";
$result = query($db, "SELECT * FROM `test`", __LINE__);
echo "Inserted: '$string'\n";
while ($line = $result->fetch_row()) {
	echo "Returned: '{$line[1]}'\n";
}
$result->free();

echo "\nSHOW VARIABLES LIKE 'character%':\n";
$result = query($db, "SHOW VARIABLES LIKE 'character%'", __LINE__);
while ($row = $result->fetch_row()) {
	echo implode(" ", $row) . "\n";
}
$result->free();
$db->close();
?>