PHP MySQL Create Table

Using PHP to Build Tables in a MySQL Database

We learned how to build a database on the MySQL server in the previous tutorial. Now it's time to populate the database with tables that will store the data. The information is organized into rows and columns in a table.

To create a table in a database, use the SQL CREATE TABLE argument.

Let's start by creating a SQL query with the Build TABLE expression, then passing it to the PHP mysqli query() function to execute it and finally create our table.

<?php
  /* Attempt MySQL server connection.
  Assuming you are running MySQL
  server with default setting (user 'root' with no password) */
  $mysqli = new mysqli("localhost", "root", "", "demo");

  // Check connection
  if($mysqli === false){
  die("ERROR: Could not connect. " . $mysqli->connect_error);
  }

  // Attempt create
  // table query execution
  $sql = "CREATE TABLE persons(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  email VARCHAR(70) NOT NULL UNIQUE
  )";
  if($mysqli->query($sql) === true){
    echo "Table created successfully.";
  }
  else{
    echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
  }

  // Close connection
  $mysqli->close();
?>

Inside the demo database, the PHP code above generates a table called persons with four columns: id, first name, last name, and email.

Each field name is accompanied by a data type declaration, which defines the type of data that the column can contain, such as integers, strings, dates, and so on.

Following the column name in the preceding SQL statement, a few additional constraints (also known as modifiers) are defined, such as NOT NULL, PRIMARY KEY, AUTO INCREMENT, and so on. Constraints specify the types of values that can be used in columns.