Show MySQL types vs PHP types (MySQL formats) from a SELECT statement

mysqli_example.php

<?php

// Connect
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = mysqli_connect('localhost', 'root', '');
printf("Connected: %s\n", mysqli_get_host_info($mysqli));

// Create database
$database = 'mysqli_example';
$sql = sprintf("CREATE DATABASE IF NOT EXISTS %s", $database);
if (mysqli_query($mysqli, $sql)) {
    printf("Created database: '%s'\n", $database);
} else {
    printf("Error creating database: '%s'\n", mysqli_error($mysqli));
}

// Use database
mysqli_select_db($mysqli, $database);

// Create table
$table = 'users';
$sql = sprintf("CREATE TABLE IF NOT EXISTS %s (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  email VARCHAR(50) DEFAULT NULL,
  archive TINYINT(1) NOT NULL DEFAULT '0',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)", $table);
if (mysqli_query($mysqli, $sql)) {
    printf("Created table: '%s'\n\n", $table);
} else {
    printf("Error creating table: '%s'\n\n", mysqli_error($mysqli));
}

// Show Create Table
$sql = sprintf("SHOW CREATE TABLE %s", $table);
$result = mysqli_query($mysqli, $sql);
if ($result && mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['Create Table'], PHP_EOL, PHP_EOL;
    }
} else {
    echo "0 rows in table.", PHP_EOL;
}


// Insert data
$sql = sprintf("SELECT * FROM %s", $table);
$result = mysqli_query($mysqli, $sql);
if ($result && mysqli_num_rows($result) == 0) {
    $sql = sprintf("INSERT INTO %s (firstname, lastname, email) VALUES ('John', 'Doe', NULL)", $table);
    if (mysqli_query($mysqli, $sql)) {
        printf("Inserted new row. Last insert ID: %s\n\n", mysqli_insert_id($mysqli));
    } else {
        printf("Error: %s\n%s\n\n", $sql, mysqli_error($mysqli));
    }
}

// Show columns
$sql = sprintf("DESCRIBE %s", $table);
$describe = array();
$result = mysqli_query($mysqli, $sql);
if ($result && mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $describe[$row['Field']] = $row['Type'];
    }
} else {
    echo "0 rows in table.", PHP_EOL;
}

// Select data
$sql = sprintf("SELECT * FROM `%s`", $table);
echo $sql, PHP_EOL;
$output = array();
$result = mysqli_query($mysqli, $sql);
if ($result && mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        foreach ($row as $key => $value) {
            $output[$key]= array(
                'Value' => $value,
                'MySQL type' => $describe[$key],
                'PHP type (MySQL format)' => gettype($value),
            );
        }
        echo preg_replace("/=>[\s]+array \(/", '=> array(', var_export($output, TRUE)), PHP_EOL;
        ob_start(); var_dump($row); $ob = ob_get_clean();
        echo 'var_dump ', str_replace("=>\n", '=>', $ob), PHP_EOL;
    }
} else {
    echo "0 rows selected.", PHP_EOL;
}

// Drop database
$sql = sprintf("DROP DATABASE IF EXISTS %s", $database);
if (mysqli_query($mysqli, $sql)) {
    printf("Dropped database: '%s'\n", $database);
} else {
    printf("Error dropping database: '%s'\n", mysqli_error($mysqli));
}

// Disconnect
mysqli_close($mysqli);
echo "Disconnected.", PHP_EOL;

Result in php -a console:

php > include 'mysqli_example.php';
Connected: Localhost via UNIX socket
Created database: 'mysqli_example'
Created table: 'users'

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `archive` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Inserted new row. Last insert ID: 1

SELECT * FROM `users`
array (
  'id' => array(
    'Value' => '1',
    'MySQL type' => 'int(11) unsigned',
    'PHP type (MySQL format)' => 'string',
  ),
  'firstname' => array(
    'Value' => 'John',
    'MySQL type' => 'varchar(30)',
    'PHP type (MySQL format)' => 'string',
  ),
  'lastname' => array(
    'Value' => 'Doe',
    'MySQL type' => 'varchar(30)',
    'PHP type (MySQL format)' => 'string',
  ),
  'email' => array(
    'Value' => NULL,
    'MySQL type' => 'varchar(50)',
    'PHP type (MySQL format)' => 'NULL',
  ),
  'archive' => array(
    'Value' => '0',
    'MySQL type' => 'tinyint(1)',
    'PHP type (MySQL format)' => 'string',
  ),
  'created_at' => array(
    'Value' => '2022-09-09 00:35:50',
    'MySQL type' => 'timestamp',
    'PHP type (MySQL format)' => 'string',
  ),
)
var_dump /home/vagrant/Code/libraries/mysqli_example.php:88:
array(6) {
  'id' =>  string(1) "1"
  'firstname' =>  string(4) "John"
  'lastname' =>  string(3) "Doe"
  'email' =>  NULL
  'archive' =>  string(1) "0"
  'created_at' =>  string(19) "2022-09-09 00:35:50"
}

Dropped database: 'mysqli_example'
Disconnected.
php >