Using MySQL Create Table Functionality the Right Way

In previous lessons, we covered connecting to MySQL and creating your first database. Now, for it to contain the data you wish to use, you have to learn how to make MySQL create table.

Database tables use columns and rows to organize all the specific data you need to keep. As you analyze MySQL create table examples, you will notice that one column can only hold a particular type of data (for example, integers or strings). You can also set attributes to make the columns more clear.

Understanding how to create table in MySQL is crucial for you to keep your data organized. That is why in this tutorial we will introduce you to MySQL create table statement and present some examples on how to use it.

MySQL Create Table: Main Tips

• By using PHP and SQL statements, you can create tables in your databases.
• Creating a specific table allows a dynamic data management from the client side. For example, by combining PHP and SQL create table statements, you can create a visual interface for database management that will suit your needs.

Table Creation Process Revealed

Using MySQL create table statement, you can create new database tables. To give you a better idea, we will start with creating and reviewing a MySQL create table example called users. You can see it will include five columns that are called user_id, username, password, email and register_date:

CREATE TABLE users ( user_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(40) NOT NULL, password VARCHAR(40) NOT NULL, email VARCHAR(60), register_date TIMESTAMP )

Keep in mind that each column in every database table has a particular property called data type, which specifies the type of data it holds.

Of course, there are more attributes that you can specify for a column. Let's take a look:

• NOT NULL - specifies that the field cannot be left empty.
• DEFAULT - sets a specific value to pass if no value is passed from the client side.
• UNSIGNED - prohibits negative value from being entered (applies to numeric data types)
• AUTO_INCREMENT - MySQL generates a value itself and with each new entry, the value is increased by one.
• PRIMARY KEY - sets the individual rows in this column to be unique. When used with AUTO_INCREMENT, it can be used to generate IDs for records.

All database tables are recommended to include a column with the PRIMARY KEY property. As a result, you will keep rows from duplicating.

In the MySQL create table examples below, you can see how database tables can be created using PHP. The first two are created using MySQLi, and the third one is generated via POD:

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  // Create connection
  $conn = new mysqli($host, $user, $pass, $db);  	
  // Check connection  	
  if ($conn->connect_error) {      
    die("Failed to connect: " . $conn->connect_error);
  }
  // sql to create table  	
  $sql = "CREATE TABLE users (user_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(40) NOT NULL,password VARCHAR(40) NOT NULL,email VARCHAR(60),register_date TIMESTAMP)";  	
  if ($conn->query($sql) === TRUE) {    
    echo "users table created.";
  } else { 
    echo "Failed to create table: " . $conn->error;
  }
  $conn->close();  
?>

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db =  'db';
  // Create connection  
  $conn = mysqli_connect($host, $user, $pass, $db);  	
  // Check connection  	
  if (!$conn) {      
    die("Failed to connect: " . mysqli_connect_error());
  }
  // sql to create table  	
  $sql = "CREATE TABLE users (user_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(40) NOT NULL,password VARCHAR(40) NOT NULL,email VARCHAR(60),register_date TIMESTAMP)";  	
  if (mysqli_query($conn, $sql)) {    
    echo "users table created successfully";
  } else {
    echo "Failed to create table: " . mysqli_error($conn);
  }
  mysqli_close($conn);  
?>

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db =  'db';
  try {
    $conn = new PDO("mysql:host=$host;dbname=$db", $user, $pass);    
    // set the PDO error mode to exception    
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
    // sql to create table   
    $sql = "CREATE TABLE users ( user_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(40) NOT NULL, password VARCHAR(40) NOT NULL, email VARCHAR(60), register_date TIMESTAMP )";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "users table created successfully";
  } catch (PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
  }
  $conn = null;  
?>

MySQL Create Table: Summary

• To create tables in your database, use PHP and SQL statements.
• A custom-made table makes your data management much more dynamic.
• If you combine PHP and SQL create table statements, you can create a visual interface for database management.