PHP code example of sectoroverload2k / php-mysql-database

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

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

    

sectoroverload2k / php-mysql-database example snippets

use PhpMysqlDatabase\Database;

// Create connection
$db = new Database([
    'server' => 'localhost',
    'username' => 'username',
    'password' => 'password',
    'database' => 'database_name'
]);

// Simple query - returns MySQL object
$result = $db->query('SELECT * FROM users WHERE id = ?', [5]);

// YOU MUST CALL fetchAssoc() or fetchAll() to get the data
$user = $result->fetchAssoc();  // Returns single row as array
// OR
$users = $result->fetchAll();   // Returns all rows as array of arrays

// ❌ WRONG - This will cause errors
$result = $db->query('SELECT * FROM users WHERE id = ?', [5]);
$name = $result['name'];  // ERROR: Cannot use object as array

// ✅ CORRECT - Call fetch methods
$result = $db->query('SELECT * FROM users WHERE id = ?', [5]);
$user = $result->fetchAssoc();
$name = $user['name'];  // Works!

// SELECT - single row
$result = $db->query('SELECT * FROM users WHERE id = ?', [5]);
$user = $result->fetchAssoc();

// SELECT - multiple rows
$result = $db->query('SELECT * FROM users WHERE status = ?', ['active']);
$users = $result->fetchAll();

// INSERT
$db->query('INSERT INTO users (name, email) VALUES (?, ?)', ['John', '[email protected]']);
$userId = $db->insert_id();

// UPDATE
$db->query('UPDATE users SET status = ? WHERE id = ?', ['inactive', 5]);

// DELETE
$db->query('DELETE FROM users WHERE id = ?', [5]);

// Prepare
$stmt = $db->prepare('SELECT * FROM users WHERE username = ? AND status = ?');

// Bind parameters and execute
$stmt = $db->bind_param($stmt, ['john_doe', 'active']);
$result = $db->execute($stmt);

// Fetch data
$user = $result->fetchAssoc();

$result = $db->query('SELECT * FROM users WHERE email = ?', ['[email protected]']);
$user = $result->fetchAssoc();

if ($user) {
    echo "Found user: " . $user['name'];
} else {
    echo "User not found";
}

$result = $db->query('SELECT * FROM products WHERE category = ?', ['electronics']);
$products = $result->fetchAll();

foreach ($products as $product) {
    echo $product['name'] . ': $' . $product['price'] . "\n";
}

$db->query(
    'INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())',
    ['Jane Doe', '[email protected]']
);

$userId = $db->insert_id();
echo "Created user with ID: $userId";

$db->query(
    'UPDATE users SET last_login = NOW() WHERE id = ?',
    [123]
);

$rowsAffected = $db->affected_rows();
echo "Updated $rowsAffected rows";

$db->query('DELETE FROM sessions WHERE expires_at < NOW()', []);

$result = $db->query(
    'SELECT * FROM orders WHERE user_id = ? AND status = ? AND created_at > ?',
    [42, 'pending', '2024-01-01']
);

$orders = $result->fetchAll();

$result = $db->query('
    SELECT u.name, u.email, p.title, p.created_at
    FROM users u
    INNER JOIN posts p ON u.id = p.user_id
    WHERE u.id = ?
    ORDER BY p.created_at DESC
    LIMIT 10
', [5]);

$posts = $result->fetchAll();

$result = $db->query('SELECT * FROM users WHERE status = ?', ['active']);

// Fetch single row as associative array
$user = $result->fetchAssoc();
// Returns: ['id' => 1, 'name' => 'John', 'email' => '[email protected]']
// Returns: null if no rows

// Fetch all rows as array of associative arrays
$users = $result->fetchAll();
// Returns: [
//   ['id' => 1, 'name' => 'John', ...],
//   ['id' => 2, 'name' => 'Jane', ...],
// ]
// Returns: [] if no rows

// Fetch single row as numeric array
$user = $result->fetchRow();
// Returns: [1, 'John', '[email protected]']

// Get row count
$count = $result->numRows();

// Get column count
$cols = $result->numFields();

$id = 5;              // Auto-detected as integer (i)
$price = 99.99;       // Auto-detected as double (d)
$name = 'Product';    // Auto-detected as string (s)
$data = null;         // Auto-detected as string (s)

$db->query(
    'INSERT INTO products (id, name, price) VALUES (?, ?, ?)',
    [$id, $name, $price]
);

$stmt = $db->prepare('INSERT INTO products (name, price, in_stock) VALUES (?, ?, ?)');
$stmt = $db->bind_param($stmt, ['Laptop', 999.99, 10], 'sdi');
$db->execute($stmt);

try {
    $result = $db->query('SELECT * FROM users WHERE id = ?', [5]);
    $user = $result->fetchAssoc();
} catch (RestRouter\Exceptions\DBErrorException $e) {
    echo "Database error: " . $e->getMessage();
} catch (RestRouter\Exceptions\ForeignKeyException $e) {
    echo "Foreign key constraint: " . $e->getMessage();
}

$result = $db->query('SELECT id FROM users WHERE email = ?', ['[email protected]']);
$exists = ($result->fetchAssoc() !== null);

if ($exists) {
    echo "Email already registered";
}

$result = $db->query('SELECT COUNT(*) as count FROM users WHERE status = ?', ['active']);
$row = $result->fetchAssoc();
$count = $row['count'];

$page = 1;
$perPage = 20;
$offset = ($page - 1) * $perPage;

$result = $db->query(
    'SELECT * FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?',
    [$perPage, $offset]
);

$posts = $result->fetchAll();

$sql = 'SELECT * FROM products WHERE 1=1';
$params = [];

if (!empty($category)) {
    $sql .= ' AND category = ?';
    $params[] = $category;
}

if (!empty($minPrice)) {
    $sql .= ' AND price >= ?';
    $params[] = $minPrice;
}

$result = $db->query($sql, $params);
$products = $result->fetchAll();

try {
    $db->beginTransaction();

    $db->query('INSERT INTO orders (user_id, total) VALUES (?, ?)', [123, 99.99]);
    $orderId = $db->insert_id();

    $db->query('INSERT INTO order_items (order_id, product_id) VALUES (?, ?)',
               [$orderId, 456]);

    $db->commit();
} catch (RestRouter\Exceptions\DBErrorException $e) {
    $db->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

try {
    $db->beginTransaction();

    // Deduct from sender
    $db->query('UPDATE wallets SET balance = balance - ? WHERE user_id = ? AND balance >= ?',
               [100.00, $fromUser, 100.00]);

    // Add to receiver
    $db->query('UPDATE wallets SET balance = balance + ? WHERE user_id = ?',
               [100.00, $toUser]);

    // Log transaction
    $db->query('INSERT INTO transactions (from_user, to_user, amount) VALUES (?, ?, ?)',
               [$fromUser, $toUser, 100.00]);

    $db->commit();
} catch (Exception $e) {
    $db->rollback();
    throw $e;
}

$result = $db->query('SELECT * FROM users WHERE id = ?', [5]);
$name = $result['name'];  // ERROR!

$result = $db->query('SELECT * FROM users WHERE id = ?', [5]);
$user = $result->fetchAssoc();
$name = $user['name'];  // Correct!

$result = $db->query('DELETE FROM users WHERE id = ?', [5]);
$deleted = $result->fetchAssoc();  // Wrong - no results from DELETE

$db->query('DELETE FROM users WHERE id = ?', [5]);
// No fetch needed - deletion complete

bash
composer