How can we create a MySQL temporary table by using PHP script?

In PHP, you can create a MySQL temporary table using database connection functions like mysqli or PDO. Temporary tables are automatically dropped when the connection ends, making them useful for storing intermediate results during complex operations.

Using MySQLi Extension

The modern approach uses the mysqli extension to connect and execute the CREATE TEMPORARY TABLE statement −

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "TUTORIALS";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully<br>";

// SQL to create temporary table
$sql = "CREATE TEMPORARY TABLE SalesSummary (
    Product_Name VARCHAR(50) NOT NULL,
    total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
    total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
)";

if ($conn->query($sql) === TRUE) {
    echo "Temporary table SalesSummary created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Using PDO Extension

Alternatively, you can use PDO for better error handling and database portability −

<?php
$dsn = "mysql:host=localhost;dbname=TUTORIALS";
$username = "root";
$password = "password";

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $sql = "CREATE TEMPORARY TABLE SalesSummary (
        Product_Name VARCHAR(50) NOT NULL,
        total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
        total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
    )";
    
    $pdo->exec($sql);
    echo "Temporary table created successfully";
    
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Key Points

Conclusion

Creating MySQL temporary tables in PHP is straightforward using mysqli or PDO extensions. Use CREATE TEMPORARY TABLE for session-specific data that automatically cleans up when the connection closes.