PHP code example of pardnchiu / mysql-cli

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

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

    

pardnchiu / mysql-cli example snippets

use pardnchiu\SQL;

// Basic query
$users = SQL::table("users")
  ->where("status", "active")
  ->where("age", ">", 18)
  ->get();

// Complex query with aggregation
$reports = SQL::table("orders")
  ->select("user_id", "COUNT(*) as order_count", "SUM(amount) as total")
  ->where("created_at", ">=", "2024-01-01")
  ->groupBy("user_id")
  ->orderBy("total", "DESC")
  ->limit(10)
  ->get();

  SQL::table("users")           // Read operation (default)
  SQL::table("users", "WRITE")  // Write operation
  

  SQL::table("users")->select("id", "name", "email");
  SQL::table("products")->select("COUNT(*) as total");
  

  // Basic conditions
  SQL::table("users")->where("status", "active");
  SQL::table("orders")->where("amount", ">", 100);
  
  // LIKE search (automatically adds wildcards)
  SQL::table("users")->where("name", "LIKE", "John");
  

  SQL::table("users")->orderBy("created_at", "DESC");
  SQL::table("products")->orderBy("price", "ASC");
  

  SQL::table("users")->limit(20)->offset(40);
  

// Inner join
SQL::table("users")
  ->join("profiles", "users.id", "profiles.user_id")
  ->get();

// Left join
SQL::table("users")
  ->leftJoin("orders", "users.id", "orders.user_id")
  ->select("users.name", "COUNT(orders.id) as order_count")
  ->get();

// Right join
SQL::table("departments")
  ->rightJoin("employees", "departments.id", "employees.dept_id")
  ->get();

// Insert data and get ID
$userId = SQL::table("users", "WRITE")
  ->insertGetId([
    "name"        => "John Doe",
    "email"       => "[email protected]", 
    "created_at"  => "NOW()"
  ]);

// Update data
$result = SQL::table("users", "WRITE")
  ->where("id", $userId)
  ->update([
    "last_login"  => "NOW()",
    "login_count" => "login_count + 1"
  ]);

// Raw query
$customData = SQL::read(
  "SELECT u.name, COUNT(o.id) as orders FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > ? GROUP BY u.id",
  ["2024-01-01"]
);

try {
  $result = SQL::table("users", "WRITE")
    ->where("id", 1)
    ->update([
      "status"      => "active", 
      "updated_at"  => "NOW()"
    ]);
  
  // Check slow query warnings
  if (!empty($result["info"])) {
    error_log("Slow query warning: " . $result["info"]);
  }
  
  echo "Update successful, affected rows: " . $result["affected_rows"];
    
} catch (\PDOException $e) {
  // Database related errors
  error_log("Database error: " . $e->getMessage());
  
  // Handle based on error code
  $errorCode = $e->getCode();
  if ($errorCode === 2006 || $errorCode === 2013) {
    // Connection interrupted, system will auto retry
    echo "Connection exception, please try again later";
  } else {
    echo "Database operation failed";
  }
    
} catch (\InvalidArgumentException $e) {
  // Parameter errors
  error_log("Parameter error: " . $e->getMessage());
  echo "Request parameters are incorrect";
    
} catch (\Exception $e) {
  // Other errors
  error_log("System error: " . $e->getMessage());
  echo "System temporarily unavailable, please contact administrator";
}

// Enable detailed logging
error_reporting(E_ALL);

// Automatically log slow queries (over 20ms)
$users = SQL::table("users")
  ->where("status", "active")
  ->get();

// Check system logs:
// [Info] PD\SQL: [Slow Query: 25.43ms] [SELECT * FROM users WHERE status = ?]