Create Database

CREATE DATABASE `newdatabase` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

---

Grand Privileges

GRANT ALL PRIVILEGES ON `newdatabase`.* TO 'someone'@'localhost' WITH GRANT OPTION;

---

Connect to MySql

$con = new mysqli(host, user, password, newdatabase);
if ($con->connect_errno) {
    error_log('Failed to connect to MySQL: ['.$con->connect_errno.'] '.$con->connect_error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$con->set_charset("utf8")) {
    error_log('Mysql loading character set utf8: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
mysqli_close($con);

---

Drop Tables

$sql = 'DROP TABLE IF EXISTS `mytable`, `mytable2`';
if (!($stmt =  $con->prepare($sql))) {
    error_log('Mysql Prepare failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->execute()) {
    error_log('Mysql Execute failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
$stmt->close();

---

Create Table

$sql = 'CREATE TABLE IF NOT EXISTS `mytable` (
    `Id` int unsigned AUTO_INCREMENT,
    `SomeInt` mediumint unsigned DEFAULT 0,
    `SomeFloat` decimal(5,2) DEFAULT 0,
    `SomeName` varchar(64) DEFAULT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;';
if (!($stmt =  $con->prepare($sql))) {
    error_log('Mysql Prepare failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->execute()) {
    error_log('Mysql Execute failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
$stmt->close();

---

Select Data

$sql = "SELECT `SomeInt`, `SomeFloat`, `SomeName` FROM `mytable` WHERE `Id` >= $some_value ORDER BY `Id` ASC";
if (!($stmt =  $con->prepare($sql))) {
    error_log('Mysql Prepare failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->bind_result($someint, $somefloat, $somename)) {
    $phperror = error_get_last();
    error_log('Bind Result Error: '.$phperror['message'].' in '.$phperror['file'].' on line '.$phperror['line']);
    exit;
}
if (!$stmt->execute()) {
    error_log('Mysql Execute failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->store_result()) {
    $phperror = error_get_last();
    error_log('Store Result Error: '.$phperror['message'].' in '.$phperror['file'].' on line '.$phperror['line']);
    exit;
}
while ($stmt->fetch()) {
    // do somethng with the data
    $someint;
    $somefloat;
    $somename;
}
$stmt->close();

---

Select Data Fetch ASSOC

$sql = "SELECT * FROM `mytable` WHERE `Id` = $some_value ORDER BY `Id` DESC LIMIT 1";
if (!($stmt =  $con->prepare($sql))) {
    error_log('Mysql Prepare failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->execute()) {
    error_log('Mysql Execute failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt_result = $stmt->get_result()) {
    $phperror = error_get_last();
    error_log('Get Result Error: '.$phperror['message'].' in '.$phperror['file'].' on line '.$phperror['line']);
    exit;
}
if ($stmt_result->num_rows > 0) {
    while ($row = $stmt_result->fetch_assoc()) {
        // do somethng with the data
        $row['Id'];
        $row['SomeInt'];
        $row['SomeFloat'];
        $row['SomeName'];
    }
}
$stmt->close();

---

Select Data INNER JOIN

$sql = "SELECT ne.`SomeInt`, ot.`OtherInt` FROM `mytable` AS `ne` INNER JOIN `othertable` AS `ot` ON ne.`somename` = ot.`othername`";
if (!($stmt =  $con->prepare($sql))) {
    error_log('Mysql Prepare failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->bind_result($someint, $otherint)) {
    $phperror = error_get_last();
    error_log('Bind Result Error: '.$phperror['message'].' in '.$phperror['file'].' on line '.$phperror['line']);
    exit;
}
if (!$stmt->execute()) {
    error_log('Mysql Execute failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->store_result()) {
    $phperror = error_get_last();
    error_log('Store Result Error: '.$phperror['message'].' in '.$phperror['file'].' on line '.$phperror['line']);
    exit;
}
while ($stmt->fetch()) {
    // do somethng with the data
    $someint;
    $otherint;
}
$stmt->close();

---

Insert Data

$sql = "INSERT INTO `mytable` (`SomeInt`, `SomeFloat`, `SomeName`) VALUES ($someint, $somefloat, $somename)";
if (!($stmt =  $con->prepare($sql))) {
    error_log('Mysql Prepare failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->execute()) {
    error_log('Mysql Execute failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
$stmt->close();

---

Insert With BindParam Data

$sql = 'INSERT INTO `mytable` (`SomeInt`, `SomeFloat`, `SomeName`) VALUES (?, ?, ?)';
if (!($stmt =  $con->prepare($sql))) {
    error_log('Mysql Prepare failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->bind_param('ids', $someint, $somefloat, $somename) {
    $phperror = error_get_last();
    error_log('Bind Param Error: '.$phperror['message'].' in '.$phperror['file'].' on line '.$phperror['line']);
    exit;
}
if (!$stmt->execute()) {
    error_log('Mysql Execute failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
$stmt->close();

---

Update Data

$sql = "UPDATE `mytable` SET `SomeInt` = $someint, `SomeFloat` = $somefloat, `SomeName` = $somename WHERE `Id` = $some_value";
if (!($stmt =  $con->prepare($sql))) {
    error_log('Mysql Prepare failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
if (!$stmt->execute()) {
    error_log('Mysql Execute failed: ['.$con->errno.'] '.$con->error.' : in '.__FILE__.' on line '.__LINE__);
    exit;
}
$stmt->close();

---

Update a Column

$sql = "UPDATE `mytable` SET `SomeInt` = $newvalue WHERE `Id` = $somevalue";
prepare, execute, close.

---

Update a Column using same/other Column as input

$sql = "UPDATE `mytable` SET `SomeInt` = `SomeInt` + $newvalue WHERE `Id` = $somevalue";
prepare, execute, close.

---

Add a Column

$sql = 'ALTER TABLE `mytable` ADD COLUMN `NewInt` int unsigned DEFAULT 0 AFTER `SomeInt`';
$sql = 'ALTER TABLE `mytable` ADD COLUMN `NewFloat` decimal(5,2) 0 AFTER `SomeFloat`';
$sql = 'ALTER TABLE `mytable` ADD COLUMN `NewMame` varchar(64) DEFAULT NULL AFTER `Somename`';
prepare, execute, close.

---

Modify a Column

$sql = 'ALTER TABLE `mytable` MODIFY `SomeInt` mediumint unsigned DEFAULT 0';
prepare, execute, close.

---

Rename Column

$sql = 'ALTER TABLE `mytable` CHANGE `SomeInt` `OtherInt` int unsigned DEFAULT 0';
prepare, execute, close.