How can PHP be utilized to prevent the creation of empty tables in a guestbook when the first entry is made?

When the first entry is made in a guestbook, the table should be created dynamically if it doesn't already exist to prevent empty tables. This can be achieved by checking if the table exists before inserting data and creating the table if it doesn't exist. 

<?php
// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "guestbook";

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

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

// Check if the table exists, if not, create it
$tableName = "entries";
$tableCheck = $conn->query("SHOW TABLES LIKE '$tableName'");

if($tableCheck->num_rows == 0) {
  $createTableQuery = "CREATE TABLE $tableName (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    message TEXT NOT NULL,
    entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )";

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

// Insert data into the table
$name = "John Doe";
$message = "Hello, this is my first entry!";
$insertQuery = "INSERT INTO $tableName (name, message) VALUES ('$name', '$message')";

if ($conn->query($insertQuery) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $insertQuery . "<br>" . $conn->error;
}

$conn->close();
?>

Keywords

PHP database SQL INSERT validation

Related Questions