Slide 7.2: PHP MySQL create and drop tables

PHP MySQL Create and Drop Tables

Use the query function to get PHP to execute the SQL statements:

the DROP TABLE statement, used to remove a database table, and
the CREATE TABLE statement, used to create a table in MySQL.

query is used to send a query or command to a MySQL connection. A database must be selected before a table can be dropped or created. The database is selected with the mysqli function with one more parameter. The following example drops and creates a table using the following SQL commands:

1DROP TABLE CDs;
2 
3CREATE TABLE CDs (
4  ASIN  CHAR(10) PRIMARY KEY,
5  Title VARCHAR(32) NOT NULL,
6  price REAL CHECK( price > 0.0 ) );

 <html><body>
 <?php
  $servername = "undcsmysql.mysql.database.azure.com";
  $username   = "user.id@undcsmysql";
  $password   = "password";
  $dbname     = "user_id";         # your database or schema
  // Connect to the database.
  $conn       = new mysqli( $servername, $username, $password, $dbname );

  $action =  Drop    Create 

  if ( $action == "Drop" ) {      
    // Drop table CDs.
    $conn->query( "DROP TABLE CDs" );
    echo "Table CDs dropped";
  }

  elseif ( $action == "Create" ) {
    // Create table CDs.
    $sql = "CREATE TABLE CDs (
  ASIN  CHAR(10) PRIMARY KEY,
  Title VARCHAR(32) NOT NULL,
  price REAL CHECK( price > 0.0 ) )";
    $conn->query( $sql );
    echo "Table CDs created";
  }
  $conn->close( );
 ?>
 </body></html>

When you create a database field of type VARCHAR, you must specify the maximum length of the field, e.g. VARCHAR(15). The CREATE TABLE statement is complicated. For further studies,

its syntax can be found from here and
some example code can be found from here.