PHP code example of websitebeaver / simple-mysqli

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

websitebeaver / simple-mysqli example snippets

try {
  $mysqli = new SimpleMySQLi("localhost", "username", "password", "dbName", "utf8mb4", "assoc");
} catch(Exception $e) {
  error_log($e->getMessage());
  exit('Someting weird happened'); //Should be a message a typical user could understand
}

set_exception_handler(function($e) {
  error_log($e->getMessage());
  exit('Someting weird happened'); //Should be a message a typical user could understand
});
$mysqli = new SimpleMySQLi("localhost", "username", "password", "dbName", "utf8mb4", "assoc");

$insert = $mysqli->query("INSERT INTO myTable (name, age) VALUES (?, ?)", [$_POST['name'], $_POST['age']]);
echo $insert->affectedRows();
echo $insert->insertId();

$update = $mysqli->query("UPDATE myTable SET name = ? WHERE id = ?", [$_POST['name'], $_SESSION['id']]);
echo $update->affectedRows();

$delete = $mysqli->query("DELETE FROM myTable WHERE id = ?", [$_SESSION['id']]);
echo $delete->affectedRows();

$update = $mysqli->query("UPDATE myTable SET name = ? WHERE id = ?", [$_POST['name'], $_SESSION['id']]);
echo $update->affectedRows();
var_export($update->info()); //For more specific version

['Rows matched' => 1, 'Changed' => 0, 'Warnings' => 0]

$update = $mysqli->query("UPDATE myTable SET name = ? WHERE id = ?", [$_POST['name'], $_SESSION['id']]);
echo $update->rowsMatched(); //Rows Matched: 1
echo $update->affectedRows(); //Rows Changed: 0

$arr = $mysqli->query("SELECT id, name, age FROM events WHERE id <= ?", [4])->fetchAll("assoc");
if(!$arr) exit('No rows');
var_export($arr);

[
  ['id' => 24 'name' => 'Jerry', 'age' => 14],
  ['id' => 201 'name' => 'Alexa', 'age' => 22]
]

$arr = $mysqli->query("SELECT id, name, age FROM events WHERE id <= ?", [4])->fetchAll("obj");
if(!$arr) exit('No rows');
var_export($arr);

[
  stdClass Object ['id' => 24 'name' => 'Jerry', 'age' => 14],
  stdClass Object ['id' => 201 'name' => 'Alexa', 'age' => 22]
]

class myClass {}
$arr = $mysqli->query("SELECT id, name, age FROM events WHERE id <= ?", [4])->fetchAll("obj", "myClass");
if(!$arr) exit('No rows');
var_export($arr);

[
  myClass Object ['id' => 24 'name' => 'Jerry', 'age' => 14],
  myClass Object ['id' => 201 'name' => 'Alexa', 'age' => 22]
]

$arr = $mysqli->query("SELECT id, name, age FROM events WHERE id <= ?", [12])->fetch("assoc");
if(!$arr) exit('No rows');
var_export($arr);

['id' => 24 'name' => 'Jerry', 'age' => 14]

$arr = $mysqli->query("SELECT id, name, age FROM myTable WHERE name = ?", [$_POST['name']])->fetchAll("num"); //must use number array to use in list
if(!$arr) exit('No rows');
list($id, $name, $age) = $arr;
echo $age; //Output 34

$count = $mysqli->query("SELECT COUNT(*) FROM myTable WHERE name = ?", [$_POST['name']])->fetch("col");
if(!$count) exit('No rows');
echo $count; //Output: 284

$favoriteSport = $mysqli->query("SELECT favorite_sport FROM myTable WHERE id = ?", [23])->fetch("col");
if($favoriteSport->numRows() < 1) exit('No rows');
echo $favoriteSport; //Output: 'basketball'

$heights = $mysqli->query("SELECT height FROM myTable WHERE id < ?", [500])->fetchAll("col");
if(!$heights) exit('No rows');
var_export($heights);

[78, 64, 68, 54, 58, 63]

$result = $mysqli->query("SELECT name, email, number FROM events WHERE id <= ?", [450]);
while($row = $result->fetch("assoc")) {
  $names[] = $row['name'];
  $emails[] = $row['email'];
  $numbers[] = $row['number'];
}
if(!isset($names) || !isset($emails) || !isset($numbers)) exit('No rows');
var_export($names);

['Bobby', 'Jessica', 'Victor', 'Andrew', 'Mallory']

//First column must be unique, like a primary key; can only select 2 columns
$arr = $mysqli->query("SELECT id, name FROM myTable WHERE age <= ?", [25])->fetchAll("keyPair");
if(!$arr) exit('No rows');
var_export($arr);

[7 => 'Jerry', 10 => 'Bill', 29 => 'Bobby']

//First column must be unique, like a primary key
$arr = $mysqli->query("SELECT id, max_bench, max_squat FROM myTable WHERE weight < ?", [205])->fetchAll("keyPairArr");
if(!$arr) exit('No rows');
var_export($arr);

[
  17 => ['max_bench' => 230, 'max_squat' => 175],
  84 => ['max_bench' => 195, 'max_squat' => 235],
  136 => ['max_bench' => 135, 'max_squat' => 285]
]

//First column must be common value to group by
$arr = $mysqli->query("SELECT eye_color, name, weight FROM myTable WHERE age < ?", [29])->fetchAll("group");
if(!$arr) exit('No rows');
var_export($arr);

[
  'green' => [
    ['name' => 'Patrick', 'weight' => 178],
    ['name' => 'Olivia', 'weight' => 132]
  ],
  'blue' => [
    ['name' => 'Kyle', 'weight' => 128],
    ['name' => 'Ricky', 'weight' => 143]
  ],
  'brown' => [
    ['name' => 'Jordan', 'weight' => 173],
    ['name' => 'Eric', 'weight' => 198]
  ]
]

//First column must be common value to group by
$arr = $mysqli->query("SELECT eye_color, name FROM myTable WHERE age < ?", [29])->fetchAll("groupCol");
if(!$arr) exit('No rows');
var_export($arr);

[
  'green' => ['Patrick', 'Olivia'],
  'blue' => ['Kyle', 'Ricky'],
  'brown' => ['Jordan', 'Eric']
]

//First column must be common value to group by
$arr = $mysqli->query("SELECT eye_color, name, weight FROM myTable WHERE age < ?", [29])->fetchAll("groupObj");
if(!$arr) exit('No rows');
var_export($arr);

$mysqli->query("SELECT eye_color, name, weight FROM myTable WHERE age < ?", [29])->fetchAll("groupObj", "myClass");

[
  'green' => [
    stdClass Object ['name' => 'Patrick', 'weight' => 178],
    stdClass Object ['name' => 'Olivia', 'weight' => 132]
  ],
  'blue' => [
    stdClass Object ['name' => 'Kyle', 'weight' => 128],
    stdClass Object ['name' => 'Ricky', 'weight' => 143]
  ],
  'brown' => [
    stdClass Object ['name' => 'Jordan', 'weight' => 173],
    stdClass Object ['name' => 'Eric', 'weight' => 198]
  ]
]

$search = "%{$_POST['search']}%";
$arr = $mysqli->query("SELECT id, name, age FROM events WHERE name LIKE ?", [$search])->fetchAll();
if(!$arr) exit('No rows');
var_export($arr);

$inArr = [12, 23, 44];
$clause = $mysqli->whereIn($inArr); //Create question marks
$arr = $mysqli->query("SELECT event_name, description, location FROM events WHERE id IN($clause)", $inArr)->fetchAll();
if(!$arr) exit('No rows');
var_export($arr);

$inArr = [12, 23, 44];
$clause = $mysqli->whereIn($inArr); //Create question marks
$fullArr = array_merge($inArr, [5]); //Merge WHERE IN values with rest of query
$arr = $mysqli->query("SELECT event_name, description, location FROM events WHERE id IN($clause) AND id < ?", $fullArr)->fetchAll();
if(!$arr) exit('No rows');
var_export($arr);

$sql[] = "INSERT INTO myTable (name, age) VALUES (?, ?)";
$sql[] = "UPDATE myTable SET name = ? WHERE id = ?";
$sql[] = "UPDATE myTable SET name = ? WHERE id = ?";
$arrOfValues = [[$_POST['name'], $_POST['age']], ['Pablo', 34], [$_POST['name'], $_SESSION['id']]];
$mysqli->atomicQuery($sql, $arrOfValues);

$sql = "INSERT INTO myTable (name, age) VALUES (?, ?)";
$arrOfValues = [[$_POST['name'], $_POST['age']], ['Pablo', 34], [$_POST['name'], 22]];
$mysqli->atomicQuery($sql, $arrOfValues);

$mysqli->transaction(function($mysqli) {
  $insert = $mysqli->query("INSERT INTO myTable (sender, receiver) VALUES (?, ?)", [28, 330]);
  if($insert->affectedRows() < 1) throw new Exception('Error inserting');
  echo $insert->insertId();
  $insert->execute([243, 49]); //reuse same insert query
  $delete = $mysqli->query("DELETE FROM myTable WHERE max_bench < ?", [125]);
});

set_error_handler(function($errno, $errstr, $errfile, $errline) {
  throw new Exception("$errstr on line $errline in file $errfile");
});

//ry {
  $insert = $mysqli->query("INSERT INTO myTable (name, age) VALUES (?, ?)", [$_POST['name'], $_POST['age']]);
} catch (Exception $e) {
  error_log($e);
  exit('Error inserting');
}

//et_exception_handler(function($e) {
  error_log($e);
  exit('Error inserting');
});
$insert = $mysqli->query("INSERT INTO myTable (name, age) VALUES (?, ?)", [$_POST['name'], $_POST['age']]);

$arr = $mysqli->query("SELECT id, name, age FROM events WHERE id <= ?", [4])->fetchAll("assoc");
if(!$arr) exit('No rows');
$mysqli->freeResult(); //Free result
$mysqli->closeStmt(); //Close statement
$mysqli->close(); //Close connection

$arr = $mysqli->query("SELECT id, name, age FROM events WHERE id <= ?", [4])->fetchAll("assoc");
if(!$arr) exit('No rows');
$mysqli->freeResult()->closeStmt()->close();

new SimpleMySQLi(string $host, string $username, string $password, string $dbName, string $charset = 'utf8mb4', string $defaultFetchType = 'assoc')

function query(string $sql, array|string|int $values = [], string $types = ''): self

function execute(array $values = [], string $types = ''): self

function whereIn(array $inArr): string

function numRows(): int

function affectedRows(): int

function info(): array

function rowsMatched(): int

function insertId(): int

function fetch(string $fetchType = '', string $className = 'stdClass', array $classParams = [])

function fetchAll(string $fetchType = '', string $className = 'stdClass'): array

function atomicQuery(array|string $sql, array $values, array $types = []): void

function transaction(callable $callback($this)): void

function freeResult(): self

function closeStmt(): self

function close(): void