English
English
In web application development, creating and deleting database tables is an essential task. With PHP and the PDO (PHP Data Objects) extension, developers can efficiently manage the lifecycle of database tables. This article will explain how to use PHP and PDO to execute SQL statements for creating and deleting database tables, along with practical code examples to help you quickly master these fundamental operations.
To create a table in the database, we need to use SQL's `CREATE TABLE` statement. Below is an example of how to connect to a database using PHP and PDO and execute an SQL statement to create a new table.
<?php
// Establish a database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
<p>$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);</p>
<p>// SQL query to create the table<br>
$sql = "CREATE TABLE users (<br>
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,<br>
firstname VARCHAR(30) NOT NULL,<br>
lastname VARCHAR(30) NOT NULL,<br>
email VARCHAR(50),<br>
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP<br>
)";</p>
<p>// Execute the SQL query<br>
$conn->exec($sql);<br>
echo "Table created successfully";<br>
?><br>
In the above example, we create a table named `users` with columns such as `id`, `firstname`, `lastname`, `email`, and `reg_date`. The `reg_date` column is set to use a default timestamp and automatically update whenever a record is modified.
To delete a table from the database, we use the SQL `DROP TABLE` statement. Below is an example of how to use PDO in PHP to connect to the database and execute a table deletion operation.
<?php
// Establish a database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
<p>$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);</p>
<p>// SQL query to drop the table<br>
$sql = "DROP TABLE users";</p>
<p>// Execute the SQL query<br>
$conn->exec($sql);<br>
echo "Table dropped successfully";<br>
?><br>
In this example, we use the `DROP TABLE` statement to delete the table named `users`. After executing this code, the table will be permanently removed from the database.
With PHP and PDO, creating and deleting database tables is straightforward. Mastering these basic operations is crucial for managing databases efficiently. In practical development, developers can flexibly use these operations based on different requirements to manage and maintain database tables.
This article introduced how to perform SQL table creation and deletion operations using PHP and PDO. We first established a database connection, then used `CREATE TABLE` to create a new table and demonstrated how to use `DROP TABLE` to delete a table. Through these examples, you can easily understand and implement these common database operations.
