PHP code example of voku / simple-mysqli

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

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

    

voku / simple-mysqli example snippets

  use voku\db\DB;

  ance('yourDbHost', 'yourDbUser', 'yourDbPassword', 'yourDbName');
  
  // example
  // $db = DB::getInstance('localhost', 'root', '', 'test');

  use voku\db\DB;

  ms = [
      'dbname'   => 'yourDbName',
      'user'     => 'yourDbUser',
      'password' => 'yourDbPassword',
      'host'     => 'yourDbHost',
      'driver'   => 'mysqli', // 'pdo_mysql' || 'mysqli'
      'charset'  => 'utf8mb4',
  ];
  $config = new \Doctrine\DBAL\Configuration();
  $doctrineConnection = \Doctrine\DBAL\DriverManager::getConnection(
      $connectionParams,
      $config
  );
  $doctrineConnection->connect();

  $db = DB::getInstanceDoctrineHelper($doctrineConnection);

  use voku\db\DB;
  
  $db = DB::getInstance();
  
  $result = $db->query("SELECT * FROM users");
  $users  = $result->fetchAll();

  $db->select(string $table, array $where); // generate an SELECT query

  $where = [
      'page_type ='         => 'article',
      'page_type NOT LIKE'  => '%öäü123',
      'page_id >='          => 2,
  ];
  $articles = $db->select('page', $where);
  
  echo 'There are ' . count($articles) . ' article(s):' . PHP_EOL;
  
  foreach ($articles as $article) {
      echo 'Type: ' . $article['page_type'] . PHP_EOL;
      echo 'ID: ' . $article['page_id'] . PHP_EOL;
  }

  $where = [
      'page_type LIKE'     => '%foo',
      'page_type NOT LIKE' => 'bar',
  ];
  $data = [
      'page_template' => ['page_template +' => 1],
      'page_type'     => 'lall',
  ];
  $resultSelect = $db->update('page', $data, $where);

  $where = [
      'page_type NOT IN' => [
          'foo',
          'bar'
      ],
      'page_id >'        => 2,
  ];
  $resultSelect = $db->select('page', $where);

  $resultSelect = $db->execSQL("SELECT * FROM users", true, 3600);

  $db->insert( string $table, array $data );                // generate an INSERT query
  $db->replace( string $table, array $data );               // generate an REPLACE query
  $db->update( string $table, array $data, array $where );  // generate an UPDATE query
  $db->delete( string $table, array $where );               // generate a DELETE query

  $deleteArray = ['user_id' => 9];
  $ok = $db->delete('users', $deleteArray);
  if ($ok) {
    echo "user deleted!";
  } else {
    echo "can't delete user!";
  }

  $insertArray = [
    'name'   => "John",
    'email'  => "[email protected]",
    'group'  => 1,
    'active' => true,
  ];
  $newUserId = $db->insert('users', $insertArray);
  if ($newUserId) {
    echo "new user inserted with the id $new_user_id";
  }

  $replaceArray = [
      'name'   => 'lars',
      'email'  => '[email protected]',
      'group'  => 0
  ];
  $tmpId = $db->replace('users', $replaceArray);

  $sql = "SELECT * FROM users 
    WHERE id_user = :id_user
    AND active = :active
    LIMIT 1
  ";
  $result = $db->query($sql, ['id_user' => 11, 'active' => 1]);
  if ($result) {
    $user = $result->fetchArray();
    print_r($user);
  } else {
    echo "user not found";
  }

$db->beginTransaction();

$db->query(
    'UPDATE `users` SET `foo` = :foo WHERE id = :id',
    ['foo' => 100, 'id' => 1]
);
$db->query(
    'UPDATE `users_noop` SET `foo` = :foo WHERE id = :id',
    ['foo' => 100, 'id' => 2]
);

$db->endTransaction();

$db->transact(function($db) {
    $db->query(
        'UPDATE `users` SET `foo` = :foo WHERE id = :id',
        ['foo' => 100, 'id' => 1]
    );
    $db->query(
        'UPDATE `users_noop` SET `foo` = :foo WHERE id = :id',
        ['foo' => 100, 'id' => 2]
    );
});

  $result = $db->query("SELECT * FROM users");
  $allUsers = $result->fetchAll();

  $row = $result->fetch();        // fetch an single result row as defined by the config (array, object or Arrayy)
  $row = $result->fetchArray();   // fetch an single result row as array
  $row = $result->fetchArrayy();  // fetch an single result row as Arrayy object
  $row = $result->fetchObject();  // fetch an single result row as object
  $row = $result->fetchYield();   // fetch an single result row as Generator
  
  $data = $result->fetchAll();        // fetch all result data as defined by the config (array, object or Arrayy)
  $data = $result->fetchAllArray();   // fetch all result data as array
  $data = $result->fetchAllArrayy();  // fetch all result data as Array object
  $data = $result->fetchAllObject();  // fetch all result data as object
  $data = $result->fetchAllYield();   // fetch all result data as Generator
  
  $data = $result->fetchColumn(string $column, bool $skipNullValues);    // fetch a single column as string
  $data = $result->fetchAllColumn(string $column, bool $skipNullValues); // fetch a single column as an 1-dimension array
  
  $data = $result->fetchArrayPair(string $key, string $value);           // fetch data as a key/value pair array

$fields = $result->fetchFields();

$data = $result->fetch($row_number, $column);

$plan_details = $plans->fetchTranspose();

$transposedExample = [
  'title' => [
    1 => 'Title #1',
    2 => 'Title #2',
    3 => 'Title #3',
  ],
);

$countries = $result->fetchPairs('id');

$countries = $result->fetchPairs('id', 'name');

/*
[
  1 => 'Title #1',
  2 => 'Title #2',
  3 => 'Title #3',
]
*/

$students_grouped_by_gender = $result->fetchGroups('gender');

$student_names_grouped_by_gender = $result->fetchGroups('gender', 'name');

$first = $result->first();

$name = $result->first('name');

$last = $result->last();

$name = $result->last('name');

$slice = $result->slice(1, 10);

$result->map(function($row) {
    return (object) $row;
});
$object = $result->fetchCallable(0);

  $db->get()                  // alias for $db->fetch();
  $db->getAll()               // alias for $db->fetchAll();
  $db->getObject()            // alias for $db->fetchAllObject();
  $db->getArray()             // alias for $db->fetchAllArray();
  $db->getArrayy()            // alias for $db->fetchAllArrayy();
  $db->getYield()             // alias for $db->fetchAllYield();
  $db->getColumn($key)        // alias for $db->fetchColumn($key);

  $result = $db->select('users');

  // using while
  while ($row = $result->fetch()) {
    echo $row->name;
    echo $row->email;
  }

  // using foreach (via "fetchAllObject()")
  foreach($result->fetchAllObject() as $row) {
    echo $row->name;
    echo $row->email;
  }
  
  // using foreach (via "Result"-object)
  foreach($result as $row) {
    echo $row->name;
    echo $row->email;
  }
  
  // using foreach (via "Generator"-object)
  foreach($result->fetchAllYield() as $row) {
    echo $row->name;
    echo $row->email;
  }
  
  // INFO: "while + fetch()" and "fetchAllYield()" will use less memory that "foreach + "fetchAllObject()", because we will fetch each result entry seperatly

$sql = "
    INSERT INTO foo
      SET
        page_template = 'lall1',
        page_type = 'test1';
    INSERT INTO lall
      SET
        page_template = 'lall2',
        page_type = 'test2';
    INSERT INTO bar
      SET
        page_template = 'lall3',
        page_type = 'test3';
";
$result = $this->db->multi_query($sql); // true

$sql = "
    SELECT * FROM foo;
    SELECT * FROM lall;
    SELECT * FROM bar;
";
$result = $this->db->multi_query($sql); // Result[]
foreach ($result as $resultForEach) {
    $tmpArray = $resultForEach->fetchArray();
    ...
}

  use voku\db\DB;
  
  $db = DB::getInstance();
  
  // ------------- 
  // prepare the queries
  
  $query = 'INSERT INTO users
    SET 
      name = ?, 
      email = ?
  ';
  
  $prepare = $db->prepare($query);
  
  $name = '';
  $email = '';
  
  $prepare->bind_param_debug('ss', $name, $email);
  
  // -------------
  // execute query no. 1
  
  // INFO: "$template" and "$type" are references, since we use "bind_param" or "bind_param_debug" 
  $name = 'name_1_中';
  $email = '[email protected]';
  
  $prepare->execute();
  
  // DEBUG
  echo $prepare->get_sql_with_bound_parameters();
  
  // -------------
  // execute query no. 2
  
  // INFO: "$template" and "$type" are references, since we use "bind_param" or "bind_param_debug"  
  $name = 'Lars';
  $email = '[email protected]';
  
  $prepare->execute();
  
  // DEBUG
  echo $prepare->get_sql_with_bound_parameters();

  use voku\db\DB;
  
  $db = DB::getInstance();
  
  // -------------
  // insert some dummy-data, first
  
  $data = [
      'page_template' => 'tpl_test_new123123',
      'page_type'     => 'ö\'ä"ü',
  ];

  // will return the auto-increment value of the new row
  $resultInsert[1] = $db->insert($this->tableName, $data);
  $resultInsert[2] = $db->insert($this->tableName, $data);

  // ------------- 
  // prepare the queries

  $sql = 'SELECT * FROM ' . $this->tableName . ' 
    WHERE page_id = ?
  ';

  $prepare = $this->db->prepare($sql);
  $page_id = 0;
  $prepare->bind_param_debug('i', $page_id);

  // ------------- 
  // execute query no. 1

  $page_id = $resultInsert[1];
  $result = $prepare->execute();
  $data = $result->fetchArray();

  // $data['page_template'] === 'tpl_test_new123123'
  // $data['page_id'] === $page_id

  // ------------- 
  // execute query no. 2

  $page_id = $resultInsert[2];
  $result = $prepare->execute();
  $data = $result->fetchArray();

  // $data['page_id'] === $page_id
  // $data['page_template'] === 'tpl_test_new123123'

$this->trace(string $text, string $name) { ... }
$this->debug(string $text, string $name) { ... }
$this->info(string $text, string $name) { ... }
$this->warn(string $text, string $name) { ... } 
$this->error(string $text, string $name) { ... }
$this->fatal(string $text, string $name) { ... }

DB::getInstance(
    getConfig('db', 'hostname'),        // hostname
    getConfig('db', 'username'),        // username
    getConfig('db', 'password'),        // password
    getConfig('db', 'database'),        // database
    getConfig('db', 'port'),            // port
    getConfig('db', 'charset'),         // charset
    true,                               // exit_on_error
    true,                               // echo_on_error
    'cms\Logger',                       // logger_class_name
    getConfig('logger', 'level'),       // logger_level | 'TRACE', 'DEBUG', 'INFO', 'WARN', 'ERROR', 'FATAL'
    getConfig('session', 'db')          // session_to_db
);

  print_r($db->log());

  if ($db->errors()) {
    echo $db->lastError();
  }