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.