Tables

Create MySQL Tables

Create MySQL Tables

To begin with, the table creation command requires the following details −

  • Name of the table
  • Name of the fields
  • Definitions for each field

Syntax

Here is a generic SQL syntax to create a MySQL table −

CREATE TABLE table_name (column_name column_type);

Now, we will create the following table in the TUTORIALS database.

create table tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

Here, a few items need explanation −

  • Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user will try to create a record with a NULL value, then MySQL will raise an error.

  • Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field.

  • Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.

Creating Tables Using PHP Script

To create new table in any existing database you would need to use PHP function mysqli_query(). You will pass its second argument with a proper SQL command to create a table.

Example

The following program is an example to create a table using PHP script −

<html>
   <head>
      <title>Creating MySQL Tables</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'remotemysql.com:3036';
         $dbuser = 'your_username;
         $dbpass = 'your_password';
         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysqli_error());
         }
         echo 'Connected successfully<br />';
         $sql = "CREATE TABLE tutorials_tbl( ".
            "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
            "tutorial_title VARCHAR(100) NOT NULL, ".
            "tutorial_author VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( tutorial_id )); ";
         mysqli_select_db( 'TUTORIALS' );
         $retval = mysqli_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not create table: ' . mysqli_error());
         }
         echo "Table created successfully\n";
         mysqli_close($conn);
      ?>
   </body>
</html>

Drop MySQL Tables

It is very easy to drop an existing MySQL table, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table.

Syntax

Here is a generic SQL syntax to drop a MySQL table −

DROP TABLE table_name ;

Dropping Tables Using PHP Script

To drop an existing table in any database, you would need to use the PHP function mysqli_query(). You will pass its second argument with a proper SQL command to drop a table.

Example

<html>
   <head>
      <title>Creating MySQL Tables</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'remotemysql.com:3036';
         $dbuser = 'your_username;
         $dbpass = 'your_password';
         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysqli_error());
         }
         echo 'Connected successfully<br />';
         $sql = "DROP TABLE tutorials_tbl";
         mysqli_select_db( 'TUTORIALS' );
         $retval = mysqli_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not delete table: ' . mysqli_error());
         }
         echo "Table deleted successfully\n";
         mysqli_close($conn);
      ?>
   </body>
</html>