A Simple PHP Class For Prepared Statements in MySQLi (ok)

https://johnmorrisonline.com/simple-php-class-prepared-statements-mysqli/

C:\xampp\htdocs\php\index.php

<?php
if (!class_exists('DB')) {
  class DB {
    public function __construct($user, $password, $database, $host = 'localhost') {
      $this->user     = $user;
      $this->password = $password;
      $this->database = $database;
      $this->host     = $host;
    }
    protected function connect() {
      return new mysqli($this->host, $this->user, $this->password, $this->database);
    }
    public function query($query) {
      $db     = $this->connect();
      $result = $db->query($query);
      while ($row = $result->fetch_object()) {
        $results[] = $row;
      }
      return $results;
    }
    public function insert($table, $data, $format) {
      // Check for $table or $data not set
      if (empty($table) || empty($data)) {
        return false;
      }
      // Connect to the database
      $db = $this->connect();
      // Cast $data and $format to arrays
      $data   = (array) $data;
      $format = (array) $format;
      // Build format string
      $format = implode('', $format);
      $format = str_replace('%', '', $format);
      list($fields, $placeholders, $values) = $this->prep_query($data);
      // Prepend $format onto $values
      array_unshift($values, $format);
      // Prepary our query for binding
      $stmt = $db->prepare("INSERT INTO {$table} ({$fields}) VALUES ({$placeholders})");
      // Dynamically bind values
      call_user_func_array(array($stmt, 'bind_param'), $this->ref_values($values));
      // Execute the query
      $stmt->execute();
      // Check for successful insertion
      if ($stmt->affected_rows) {
        return true;
      }
      return false;
    }
    public function update($table, $data, $format, $where, $where_format) {
      // Check for $table or $data not set
      if (empty($table) || empty($data)) {
        return false;
      }
      // Connect to the database
      $db = $this->connect();
      // Cast $data and $format to arrays
      $data   = (array) $data;
      $format = (array) $format;
      // Build format array
      $format       = implode('', $format);
      $format       = str_replace('%', '', $format);
      $where_format = implode('', $where_format);
      $where_format = str_replace('%', '', $where_format);
      $format .= $where_format;
      list($fields, $placeholders, $values) = $this->prep_query($data, 'update');
      //Format where clause
      $where_clause = '';
      $where_values = '';
      $count        = 0;
      foreach ($where as $field => $value) {
        if ($count > 0) {
          $where_clause .= ' AND ';
        }
        $where_clause .= $field . '=?';
        $where_values[] = $value;
        $count++;
      }
      // Prepend $format onto $values
      array_unshift($values, $format);
      $values = array_merge($values, $where_values);
      // Prepary our query for binding
      $stmt = $db->prepare("UPDATE {$table} SET {$placeholders} WHERE {$where_clause}");
      // Dynamically bind values
      call_user_func_array(array($stmt, 'bind_param'), $this->ref_values($values));
      // Execute the query
      $stmt->execute();
      // Check for successful insertion
      if ($stmt->affected_rows) {
        return true;
      }
      return false;
    }
    public function select($query, $data, $format) {
      // Connect to the database
      $db = $this->connect();
      //Prepare our query for binding
      $stmt = $db->prepare($query);
      //Normalize format
      $format = implode('', $format);
      $format = str_replace('%', '', $format);
      // Prepend $format onto $values
      array_unshift($data, $format);
      //Dynamically bind values
      call_user_func_array(array($stmt, 'bind_param'), $this->ref_values($data));
      //Execute the query
      $stmt->execute();
      //Fetch results
      $result = $stmt->get_result();
      //Create results object
      while ($row = $result->fetch_object()) {
        $results[] = $row;
      }
      return $results;
    }
    public function delete($table, $id) {
      // Connect to the database
      $db = $this->connect();
      // Prepary our query for binding
      $stmt = $db->prepare("DELETE FROM {$table} WHERE ID = ?");
      // Dynamically bind values
      $stmt->bind_param('d', $id);
      // Execute the query
      $stmt->execute();
      // Check for successful insertion
      if ($stmt->affected_rows) {
        return true;
      }
    }
    private function prep_query($data, $type = 'insert') {
      // Instantiate $fields and $placeholders for looping
      $fields       = '';
      $placeholders = '';
      $values       = array();
      // Loop through $data and build $fields, $placeholders, and $values
      foreach ($data as $field => $value) {
        $fields .= "{$field},";
        $values[] = $value;
        if ($type == 'update') {
          $placeholders .= $field . '=?,';
        } else {
          $placeholders .= '?,';
        }
      }
      // Normalize $fields and $placeholders for inserting
      $fields       = substr($fields, 0, -1);
      $placeholders = substr($placeholders, 0, -1);
      return array($fields, $placeholders, $values);
    }
    private function ref_values($array) {
      $refs = array();
      foreach ($array as $key => $value) {
        $refs[$key] = &$array[$key];
      }
      return $refs;
    }
  }
}
$db = new DB('root', '', 'tut');
$result = $db->select('SELECT * FROM models WHERE ID = ?', array(1), array('%d'));
echo '<pre>';
  var_export($result);
echo '</pre>';

C:\Users\Administrator\Desktop\tut.sql

-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Feb 17, 2021 at 05:11 AM
-- Server version: 10.4.17-MariaDB
-- PHP Version: 7.4.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `tut`
--

-- --------------------------------------------------------

--
-- Table structure for table `makes`
--

CREATE TABLE `makes` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(111) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `makes`
--

INSERT INTO `makes` (`id`, `name`) VALUES
(1, 'Make 1'),
(2, 'Make 2');

-- --------------------------------------------------------

--
-- Table structure for table `models`
--

CREATE TABLE `models` (
  `id` int(11) UNSIGNED NOT NULL,
  `make` int(11) NOT NULL,
  `name` varchar(111) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `models`
--

INSERT INTO `models` (`id`, `make`, `name`) VALUES
(1, 1, 'Model 1'),
(2, 1, 'Model 2'),
(3, 1, 'Model 3'),
(4, 2, 'Model 4'),
(5, 2, 'Model 5');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `makes`
--
ALTER TABLE `makes`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `models`
--
ALTER TABLE `models`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `makes`
--
ALTER TABLE `makes`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `models`
--
ALTER TABLE `models`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

2KB

tut.sql