(PHP MySQL) CREATE and DROP Tables


Use the query function to get PHP to execute the SQL statements: 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:

DROP TABLE CDs;

CREATE TABLE CDs (
  ASIN  CHAR(10) PRIMARY KEY,
  Title VARCHAR(32) NOT NULL,
  price REAL CHECK( price > 0.0 ) );

 <html><body>
 <?php
  $username = "user_id";
  $database = "user_id";
  $password = "p-word";
  $host     = "undcemmysql.mysql.database.azure.com";

  // Initializing MySQLi
  $conn     = mysqli_init( );

  // Creating an SSL connection
  mysqli_ssl_set( $conn, NULL, NULL, "DigiCertGlobalRootCA.crt.pem", NULL, NULL );

  // Opening a new connection to the MySQL server
  mysqli_real_connect( $conn, $host, $username, $password, $database, 3306 );

  // Connect to the database.
  if ( mysqli_connect_errno( ) )
    die( 'Failed to connect to MySQL: ' . mysqli_connect_error( ) );

  $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,


      My mother made us eat all sorts of vitamins and supplements.    
      One day I nearly choked on part of The Sunday Times.    
      — Milton Jones