MySQLi Objektum Orientáltan

Szerző: Sallai András
Copyright © Sallai András, 2020
Licenc: GNU Free Documentation License 1.3
Web: https://szit.hu

Adatbázis

create database feherbt
character set utf8
collate utf8_hungarian_ci;
 
grant all privileges
on feherbt.*
to feherbt@localhost
identified by 'titok';
 
 
use feherbt;
create table employees (
    id int not null primary key auto_increment,
    name varchar(30),
    city varchar(30),
    salary double
);

Példa

Struktúra

projekt01/
  |-src/
  |  |-includes/
  |  |   |-employee.php
  |  |   |-getdata.php
  |  |   |-insertdata.php
  |  |   `-mariadb.php
  |  |-templates/
  |  |   |-footer.tpl
  |  |   |-header.tpl
  |  |   |-index.tpl
  |  |   `-li.tpl
  |  `-index.php
  `-test/
     `-insertEmployeeTest.php

employee.php

employee.php

<?php
 
class Employee {
    public $id;
    public $name;
    public $city;
    public $salary;
}

mariadb.php

mariadb.php

<?php
 
class Mariadb {
    public static function connectDb() {        
        $conn = new mysqli('localhost', 'feherbt', 'titok', 'feherbt');
        if($conn->connect_error) {
            error_log('Hiba! A kapcsolódás sikertelen!');
            die;
        }
        $conn->set_charset("utf-8");
        return $conn;
    }
}

getdata.php

getdata.php

<?php 
 
include 'mariadb.php';
 
class Getdata {
    public static function getEmployees() {
        $employees = array();        
        $conn = Mariadb::connectDb();
        $sql = "select name, city, salary from employees";
        if ($res = $conn->query($sql)) {
            while(($record = $res->fetch_assoc()) !== null) {
                $employee = new Employee();
                $employee->name = $record['name'];
                $employee->city = $record['city'];
                $employee->salary = $record['salary'];
                array_push($employees, $employee);
            }
        }else {
            error_log('Hiba! A lekérdezés sikertelen!');
        }
        $conn->close();
        return $employees;
    }
}

index.php

index.php

<?php
 
include 'includes/getdata.php';
include 'includes/employee.php';
 
$employees = Getdata::getEmployees();
 
echo file_get_contents('templates/header.tpl');
 
$page = file_get_contents('templates/index.tpl');
 
$li = file_get_contents('templates/li.tpl');
$employeeList = '';
foreach($employees as $employee) {    
    $employeeList .= str_replace('{{ employee }}', $employee->name, $li);
}
 
$page = str_replace('{{ employeeList }}', $employeeList, $page);
echo $page;
 
echo file_get_contents('templates/footer.tpl');

header.tpl

header.tpl

<!DOCTYPE html>
<html lang="hu">
<head>
<meta charset="utf-8">
<title>Minta</title>
</head>
<body>
 
    <div class="container">
 
 
    <h1>Minta</h1>

footer.tpl

footer.tpl

 
    </div>
</body>
</html>

index.tpl

index.tpl

<ul>
    {{ employeeList }}
</ul>

li.tpl

li.tpl

<li>{{ employee }}</li>

Beszúrás

insertEmployee.php

include_once 'includes/employee.php';
include_once 'includes/mariadb.php';
 
class Insertdata {
    public static function insertEmployee($employee) {
        $conn = Mariadb::connectDb();
        $sql = "insert into employees " .
               "(name, city, salary) values " .
               "(?, ?, ?)";
 
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ssd", 
            $employee->name,
            $employee->city,
            $employee->salary
            );
        $insertOk = false;
        if($stmt->execute()) {
            $insertOk = true;
            error_log('Beszúrt, azonosító: ' . $conn->insert_id . "\n");
        }else {
            error_log("Hiba! A beszúrás sikertelen!\n");
        }        
        $stmt->close();        
        $conn->close();
        return $insertOk;
    }
}

Teszt

insertEmployeeTest.php

<?php 
 
include '../src/includes/insertdata.php';
include_once '../src/includes/employee.php';
 
$employee = new Employee();
$employee->name = 'Parány Mihály';
$employee->city = 'Szeged';
$employee->salary = 2850000;
if(Insertdata::insertEmployee($employee)) {
    echo 'Ok    Sikeres beszúrás';
}else{
    echo 'Hiba! Sikertelen beszúrás!';
}