PHP download

PHP code example of pierresh / phpstan-pdo-mysql

1. Go to this page and download the library: Download pierresh/phpstan-pdo-mysql library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

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

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

pierresh / phpstan-pdo-mysql example snippets


// ❌ Incomplete query
$stmt = $db->query("SELECT * FROM");


$sql = "SELECT * FROM";
$stmt = $db->query($sql);


// ✅ Valid SQL
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");


// ❌ Missing parameter
$stmt = $db->prepare("SELECT * FROM users WHERE id = :id AND name = :name");
$stmt->execute(['id' => 1]); // Missing :name


// ❌ Extra parameter
$stmt = $db->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1, 'extra' => 'unused']);


// ❌ Wrong parameter name
$stmt = $db->prepare("SELECT * FROM users WHERE id = :user_id");
$stmt->execute(['id' => 1]); // Should be :user_id


// ✅ Valid bindings
$stmt = $db->prepare("SELECT * FROM users WHERE id = :id AND name = :name");
$stmt->execute(['id' => 1, 'name' => 'John']);


$stmt = $db->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindValue(':id', 1); // This is ignored!
$stmt->execute(['name' => 'John']); // Wrong parameter


// ❌ Column typo: "nam" instead of "name"
$stmt = $db->prepare("SELECT id, nam, email FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string, email: string} */
$user = $stmt->fetch();


// ❌ Missing column
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string, email: string} */
$user = $stmt->fetch();


// ✅ Valid columns
$stmt = $db->prepare("SELECT id, name, email FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string, email: string} */
$user = $stmt->fetch();

// ✅ Also valid - selecting extra columns is fine
$stmt = $db->prepare("SELECT id, name, email, created_at FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string, email: string} */
$user = $stmt->fetch(); // No error - extra column `created_at` is ignored


/**
 * @phpstan-type User object{id: int, name: string, email: string}
 */
class UserRepository
{
    public function findUser(int $id): void
    {
        // Typo: "nam" instead of "name", also missing "email"
        $stmt = $this->db->prepare("SELECT id, nam FROM users WHERE id = :id");
        $stmt->execute(['id' => $id]);

        /** @var User */
        $user = $stmt->fetch();

}
}


// ❌ fetchAll() returns an array of objects, not a single object
$stmt = $db->prepare("SELECT id, name FROM users");
$stmt->execute();

/** @var object{id: int, name: string} */
$users = $stmt->fetchAll(); // Wrong: should be array type


// ❌ fetch() returns a single object, not an array
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var array<object{id: int, name: string}> */
$user = $stmt->fetch(); // Wrong: should be single object type


// ✅ Correct: fetchAll() with array type (generic syntax)
$stmt = $db->prepare("SELECT id, name FROM users");
$stmt->execute();

/** @var array<object{id: int, name: string}> */
$users = $stmt->fetchAll();

// ✅ Correct: fetchAll() with array type (suffix syntax)
/** @var object{id: int, name: string}[] */
$users = $stmt->fetchAll();

// ✅ Correct: fetch() with single object type
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string} */
$user = $stmt->fetch();


// ❌ Missing |false in type annotation
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string} */
$user = $stmt->fetch(); // Can return false!


// ✅ Correct: Include |false in union type
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string}|false */
$user = $stmt->fetch();

// Both styles are supported:
/** @var object{id: int, name: string} | false */  // With spaces
/** @var false|object{id: int, name: string} */    // Reverse order


// ✅ Correct: Check rowCount() with throw/return
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

if ($stmt->rowCount() === 0) {
    throw new \RuntimeException('User not found');
}

/** @var object{id: int, name: string} */
$user = $stmt->fetch(); // Safe - won't execute if no rows


// ✅ Correct: Check for false after fetch
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

/** @var object{id: int, name: string} */
$user = $stmt->fetch();

if ($user === false) {
    throw new \RuntimeException('User not found');
}
// Or: if ($user !== false) { ... }
// Or: if (!$user) { ... }


// ❌ rowCount() without throw/return doesn't help
$stmt = $db->prepare("SELECT id, name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

if ($stmt->rowCount() === 0) {
    // Empty block - execution continues!
}

/** @var object{id: int, name: string} */
$user = $stmt->fetch(); // Still can return false!


// ❌ Self-reference in JOIN condition
$stmt = $db->prepare("
    SELECT *
    FROM orders
    INNER JOIN users ON users.id = users.id
");


// ❌ Self-reference in WHERE clause
$stmt = $db->prepare("
    SELECT *
    FROM products
    WHERE products.category_id = products.category_id
");


// ❌ Multiple self-references in same query
$stmt = $db->prepare("
    SELECT *
    FROM orders
    INNER JOIN products ON products.id = products.id
    WHERE products.active = products.active
");


// ✅ Valid JOIN - different columns
$stmt = $db->prepare("
    SELECT *
    FROM orders
    INNER JOIN users ON orders.user_id = users.id
");

// ✅ Valid WHERE - comparing to a value
$stmt = $db->prepare("
    SELECT *
    FROM products
    WHERE products.category_id = 5
");


// ❌ Table 'user' doesn't exist - should be 'users'
$stmt = $db->prepare("SELECT user.name FROM users WHERE users.id = :id");


// ❌ Wrong alias - using 'usr' but alias is 'u'
$stmt = $db->prepare("SELECT usr.name FROM users AS u WHERE u.id = :id");


// ❌ Table 'orders' not in FROM or JOIN
$stmt = $db->prepare("SELECT users.id, orders.total FROM users WHERE users.id = :id");


// ✅ Correct table name
$stmt = $db->prepare("SELECT users.name FROM users WHERE users.id = :id");

// ✅ Correct alias usage
$stmt = $db->prepare("SELECT u.name FROM users AS u WHERE u.id = :id");

// ✅ Both table name and alias can be used
$stmt = $db->prepare("SELECT users.id, u.name FROM users AS u WHERE u.id = :id");

// ✅ Multiple tables with JOIN
$stmt = $db->prepare("
    SELECT u.name, o.total
    FROM users AS u
    INNER JOIN orders AS o ON u.id = o.user_id
    WHERE u.id = :id
");


// ❌ Always-true condition
$stmt = $db->prepare("
    SELECT *
    FROM users
    WHERE 1 = 1
");


// ❌ Always-false condition
$stmt = $db->prepare("
    SELECT *
    FROM users
    WHERE 1 = 0
");


// ❌ String literal tautology
$stmt = $db->prepare("SELECT * FROM users WHERE 'yes' = 'yes'");


// ❌ Boolean tautology
$stmt = $db->prepare("SELECT * FROM users WHERE TRUE = FALSE");


// ❌ Tautology in JOIN condition
$stmt = $db->prepare("
    SELECT *
    FROM users
    INNER JOIN orders ON 1 = 1
");


// ✅ Valid - comparing column to literal
$stmt = $db->prepare("SELECT * FROM users WHERE status = 1");

// ✅ Valid - using parameter
$stmt = $db->prepare("SELECT * FROM users WHERE id = :id");


// ❌ IFNULL is MySQL-specific
$stmt = $db->prepare("SELECT IFNULL(name, 'Unknown') FROM users");


// ❌ IF() is MySQL-specific
$stmt = $db->prepare("SELECT IF(status = 1, 'Active', 'Inactive') FROM users");


// ✅ COALESCE is portable (works in MySQL, PostgreSQL, SQL Server)
$stmt = $db->prepare("SELECT COALESCE(name, 'Unknown') FROM users");

// ✅ CASE WHEN is portable
$stmt = $db->prepare("SELECT CASE WHEN status = 1 THEN 'Active' ELSE 'Inactive' END FROM users");


// ❌ NOW() is MySQL-specific
$stmt = $db->prepare("SELECT * FROM users WHERE created_at > NOW()");


// ❌ CURDATE() is MySQL-specific
$stmt = $db->prepare("SELECT * FROM users WHERE birth_date = CURDATE()");


// ❌ LIMIT offset, count is MySQL-specific
$stmt = $db->prepare("SELECT * FROM users LIMIT 10, 5");


// ✅ Bind PHP datetime variables
$stmt = $db->prepare("SELECT * FROM users WHERE created_at > :now");
$stmt->execute(['now' => (new \DateTime())->format('Y-m-d H:i:s')]);

$stmt = $db->prepare("SELECT * FROM users WHERE birth_date = :today");
$stmt->execute(['today' => (new \DateTime())->format('Y-m-d')]);

// ✅ LIMIT count OFFSET offset is portable
$stmt = $db->prepare("SELECT * FROM users LIMIT 5 OFFSET 10");


// Direct string literals
$stmt = $db->prepare("SELECT ...");

// Variables
$sql = "SELECT ...";
$stmt = $db->prepare($sql);


use PHPUnit\Framework\TestCase;

class MyTest extends TestCase
{
    public function testExample(): void
    {
        $row = $stmt->fetch(); // Fetch data from database
        ddt($row); // Dumps type and stops execution
    }
}


/**
 * @phpstan-type Item object{
 *  id: int,
 *  name: string,
 *  status: int,
 * }
 */


// Nested objects
$workflow = new stdClass();
$workflow->id = 1;
$workflow->metadata = new stdClass();
$workflow->metadata->created_at = '2024-01-01';

ddt($workflow);

// Output:
/**
 * @phpstan-type Item object{
 *  id: int,
 *  metadata: object{
 *    created_at: string,
 *  },
 * }
 */


// Associative array
$config = ['database' => 'mysql', 'port' => 3306];
ddt($config);

// Output:
/**
 * @phpstan-type Item array{
 *  database: string,
 *  port: int,
 * }
 */


// Sequential array
$ids = [1, 2, 3, 4, 5];
ddt($ids);

// Output:
/**
 * @phpstan-type Item array<int, int>
 */


use PHPUnit\Framework\TestCase;

class MyTest extends TestCase
{
    public function testExample(): void
    {
        $row = $stmt->fetchObject(); // Fetch data from database
        ddc($row); // Dumps class definition and stops execution
    }
}


class Item
{
    public int $id;
    public string $name;
    public string $email;
    public ?string $phone;
}


// 1. First, discover the structure using ddc()
$stmt = $db->query("SELECT id, name, email, phone FROM users WHERE id = 1");
$row = $stmt->fetchObject();
ddc($row);

// 2. Create your view model class from the output
class UserViewModel
{
    public int $id;
    public string $name;
    public string $email;
    public ?string $phone;
}

// 3. Use it with PDO::fetchObject()
$stmt = $db->query("SELECT id, name, email, phone FROM users WHERE id = 1");
$user = $stmt->fetchObject(UserViewModel::class);