Easy To Learn Create MySQL Table Using PHP

Create MySQL Table Using PHP

The MySQL CREATE TABLE statement is used to create a new table into the database.

We will create a table named "student_details", with five columns: "stu_id", "stu_name", "college", "email" and "mobile" :

CREATE TABLE student_details (
	stu_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	stu_name VARCHAR(255) NOT NULL,
	college VARCHAR(255) NOT NULL,
	email VARCHAR(255),
	mobile VARCHAR(20)
)

The above example creates a table named persons with four columns stu_id, stu_name, college, emai and mobile inside the demo database.

Notice that each field name is followed by a data type declaration; this declaration specifies what type of data the column can hold, whether integer, string, date, etc.

There are a few additional constraints (also called modifiers) that are specified after the column name in the preceding SQL statement, like :

PRIMARY KEY : Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT
AUTO INCREMENT : MySQL automatically increases the value of the field by 1 each time a new record is added
NOT NULL : Each row must contain a value for that column, null values are not allowed
DEFAULT value : Set a default value that is added when no other value is passed
UNSIGNED : Used for number types, limits the stored data to positive numbers and zero

Each table should have a primary key column (in this case: the "stu_id" column). Its value must be unique for each record in the table.

Create a Table (MySQLi Procedural)

<?php

	// Create connection
	$ftl_connect = mysqli_connect("localhost", "root", "", "ftl_db");
	// Check connection
	if (!$ftl_connect) {
		die("Connection failed: " . mysqli_connect_error());
	}
	
	//Create a MySQL Table
	$sql = "CREATE TABLE student_details (
				stu_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
				stu_name VARCHAR(255) NOT NULL,
				college VARCHAR(255) NOT NULL,
				email VARCHAR(255),
				mobile VARCHAR(20)
			)";
	
	if (mysqli_query($ftl_connect, $sql)) {
		echo "Successfully created <b>student_details</b> table..!";
	} else {
		echo "Error : Creating table : " . mysqli_error($ftl_connect);
	}
	
	// Close Connection 
	mysqli_close($ftl_connect);
	
?>

Create a Table (MySQLi Object-oriented)

<?php

	// Create connection
	$ftl_connect = mysqli_connect("localhost", "root", "", "ftl_db");
	// Check connection
	if ($ftl_connect->connect_error) {
		die("Connection failed : " . $ftl_connect->connect_error);
	} 
	
	//Create a MySQL Table
	$sql = "CREATE TABLE student_details (
				stu_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
				stu_name VARCHAR(255) NOT NULL,
				college VARCHAR(255) NOT NULL,
				email VARCHAR(255),
				mobile VARCHAR(20)
			)";
	
	if ($ftl_connect->query($sql) === TRUE) {
		echo "Successfully created <b>student_details</b> table..!";
	} else {
		echo "Error : Creating table : " . $ftl_connect->error;
	}
	
	//close Connection
	$ftl_connect->close();
	
?>

Create a Table (PDO)

<?php

    try {
        $ftl_connect = new PDO("mysql:host=localhost;dbname=ftl_db", "root", "");
        // set the PDO error mode to exception
        $ftl_connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
       //Create a MySQL Table
		$sql = "CREATE TABLE student_details (
				stu_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
				stu_name VARCHAR(255) NOT NULL,
				college VARCHAR(255) NOT NULL,
				email VARCHAR(255),
				mobile VARCHAR(20)
			)";
    
        // use exec() because no results are returned
        $ftl_connect->exec($sql);
        	echo "Successfully created <b>student_details</b> table..!";
        }
    catch(PDOException $e)
        {
        	echo $sql . "<br>" . $e->getMessage();
        }
		
    //Close Connection
    $ftl_connect = null;
    
?>

PHP Tutorial