enuk

MySQLi - Object Orientated

The following is considered up to date with PHP 8.1. Wrappers and common practices found elsewhere should be considered wrong practice. All example scripts should be re-written to follow this standard.

CONNECT


    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $db = new mysqli($host, $user, $password, $db, $port);
    $db->set_charset($charset);
    $db->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
    
Connect to the Database

    $db->close;
    
Close the connection to the Database

Query


    mysqli_query($db, $query); // procedural syntax
    $db->query($query); // object syntax
    
Procedural vs. Object Orientated

Prepared Statements

A Prepared Statement (parameterized statement) is created with a query template using placeholders (?) instead of providing actual values. The placeholders are later replaced with actual values that are bound to the parameters in the statement during execution.

A prepared statement also provides protection against SQL injection as the query is created with placeholders that are replaced with input values later during 'bind param' and 'execute'. There is also no need of escaping input values as the values are treated as literals avoiding the threat an SQL injection.

There are four types of binding allowed regarding variables:
"i" - Integer, "d" - Doubles, "b" - Blob, "s" - String

SELECT


    $stmt = $db->prepare("SELECT * FROM users WHERE email = ?");
    $stmt->execute([$email]);
    
Select Data via PHP 8.0 Shorthand

    $sql = "SELECT * FROM tablename WHERE id = ?";		
    $stmt = $db->prepare($sql);
    $stmt->bind_param("i", $_POST['id']);	
    $stmt->execute();
    
Select Data via Prepared Statment

    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $username = $row['username'];
    
This equals that

    $users = [];
    $sql = "SELECT * FROM users ORDER BY id DESC LIMIT 0, 10";
    $result = $db->query($sql);
    while ($row = $result->fetch_assoc()) {
        $users[] = $row;
    }
    
While this do that

    $sql = "SELECT * FROM categories";
    $result = $db->query($sql);
    $data = $result->fetch_all(MYSQLI_ASSOC);
    
Another example

INSERT


    $stmt = $db->prepare("INSERT INTO users (email, password) VALUES (?,?)");
    $stmt->execute([$email, $password_hash]);
    
Insert Data via PHP 8.0 Shorthand

    $sql = "INSERT INTO tablename (name, quantity, price) VALUES (?, ?, ?)"
    $stmt = $db->prepare($sql);
    $stmt->bind_param("sid", $_POST['name'], $_POST['quantity'], $_POST['price']);
    if($stmt->execute()){
    	 return true;
    }
    
Insert Data via Prepared Statement

    echo $db->insert_id;
    
Get the Last Inserted ID

UPDATE


    $stmt = $db->prepare("UPDATE users SET name=?, email=?, password=? WHERE id=?");
    $stmt->execute([$name, $email, $password, $id]);
    
Upate Data PHP 8.0 Shorthand

    $sql = "UPDATE tablename SET name = ?, quantity = ?, price = ? WHERE id = ?";
    $stmt = $db->prepare($sql);
    $stmt->bind_param("sidi", $_POST['name'], $_POST['quantity'], $_POST['price'], $_POST['id']);
    if($stmt->execute()){
    	 return true;
    }
    
Update Data via Prepared Statement

DELETE


    $stmt = $db->prepare("DELETE FROM users WHERE id=?");
    $stmt->execute([$id]);
    
Delete Data via PHP 8.0 Shorthand

    $sql = "DELETE FROM tablename WHERE id = ?";
    $stmt = $db->prepare($sql);
    $stmt->bind_param("i", $_POST['id']);
    if($stmt->execute()){
    	return true;
    }
    
Delete Data via Prepared Statement

    echo $db->affected_rows();
    
Get Affected Rows

Reference: https://phpdelusions.net/mysqli.