How to Create a MySQL Table with PDO in PHP

In this tutorial, we are going to see how to create a MySQL table with PDO in PHP. The database table has its own unique name and consists of columns and rows. To create a new table in a database, we use the statement CREATE TABLE. The following SQL statement creates the table “Users”:

CREATE TABLE Users (
	id INT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(50) NOT NULL,
	lastname VARCHAR(50) NOT NULL,
	address VARCHAR(250)
)

The above SQL statement creates the “Users” table through a command-line tool or a MySQL GUI tool. However, we are going to use the PDO API to create tables with PHP.
 

 

How to Create a MySQL Table with PDO in PHP

<?php
 $host='localhost';
 $dbname = 'test';
 $username = 'root';
 $password = '';
 
 try{
  $dsn = "mysql:host=$host;dbname=$dbname";
  $pdo = new PDO($dsn, $username, $password);
 
  $sql = <<<EOSQL
CREATE TABLE Users (
	id INT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(50) NOT NULL,
	lastname VARCHAR(50) NOT NULL,
	address VARCHAR(250)
) ENGINE=InnoDB
EOSQL;
 
  $msg = '';
  
  $res = $pdo->exec($sql);
 
  if($res !== false){
	 $msg =  "The Users table is created successfully!";
  }else{
	 $msg =  "Error occurs when creating the Users table.";
  }

  // display the message
  if($msg != '')
	 echo $msg;
 
  }catch (PDOException $e){
	 echo $e->getMessage();
}
?>

 

How it works!

• We have created a connection to the MySQL database by instantiating an instance of the PDO class and passing the $dsn argument to its constructor.
• Next, we specified the SQL statement that creates the “Users” table.
• Finally, we call the exec() method of the PDO class to execute the SQL statement.

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More