Create table in php file

Create MySQL Table using PHP mysqli Script

This article is created to describe how to create a MySQL table using the PHP mysqli script. To complete the task, I approached it in two ways:

Whatever method we use to create a table with PHP mysqli script. Some of the main steps we have to follow are:

• Open a connection to the database.
• Write the SQL statement regarding the table creation.
• Initialize the written SQL statement to a variable.
• Use this variable to perform the query against the MySQL database.
• Close the database connection.

For example, in this article, to create a table, I am going to use the following SQL code:

CREATE TABLE customer ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, age INT(2), email VARCHAR(40) NOT NULL)

This SQL query creates a table named customer with the following columns:

While creating the columns, be sure to provide the data type of the column along with its size.

Note: The UNSIGNED keyword is used when we do not want any negative value to be inserted in the column in any way.

Note: The AUTO_INCREMENT is used to increment the value by 1 each time on the insertion of a new row.

Note: The PRIMARY KEY is used when we need to specify a column as a primary column. The primary column (field) uniquely identifies each row (record) in the table.

Note: The VARCHAR refers to a string that can consist of letters, numbers, and special characters.

Note: The INT refers to a standard-sized integer. The range is from -2147483648 to 2147483647.

Note: The NOT NULL is used to not accept NULL values.

Create MySQL Table using PHP mysqli Object-Oriented Script

To create a MySQL table using a PHP mysqli object-oriented script, follow the example given below:

connect_errno) < echo "Connection to the database failed!"; echo "Reason: ", $conn->connect_error; exit(); > $sql = "CREATE TABLE customer ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, age INT(2), email VARCHAR(40) NOT NULL )"; $qry = $conn->query($sql); if($qry) < echo "Table created successfully."; // block of code to process further. > else < echo "Table has not been created!"; echo "Reason: ", $conn->error; > $conn->close(); ?>

The output produced by the above PHP example of «creating a table» is shown in the snapshot given below:

Since the table named customer is created in the database named codescracker. Therefore, if you open the MySQL database server and then the codescracker database. There, you will see the customer table with four columns: id, name, age, and email. Here is a snapshot of the table:

Now let me re-execute the above PHP script again. Okay, here is the output I have:

Fatal error: Uncaught mysqli_sql_exception: Table 'customer' already exists in C:\xampp\htdocs\index.php:23 Stack trace: #0 C:\xampp\htdocs\index.php(23): mysqli->query('CREATE TABLE cu. ') #1 thrown in C:\xampp\htdocs\index.php on line 23

Saying that the table named customer already exists. This output is produced because I have already created the table customer using the previous PHP mysqli object-oriented script.

But the problem with the above output is that the error we are seeing is not one that I have written in script. That is because of the default error reporting mode. Therefore, to print our own error message, we need to change the mode using either mysqli_driver() (for object-oriented script) or mysqli_report() (for procedural script). For example:

 report_mode = MYSQLI_REPORT_OFF; $conn = new mysqli("localhost", "root", "", "codescracker"); if($conn->connect_errno) < echo "Connection to the database failed!"; echo "Reason: ", $conn->connect_error; exit(); > $sqlCode = "CREATE TABLE customer ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, age INT(2), email VARCHAR(40) NOT NULL )"; if(!$conn->query($sqlCode)) < echo "Table has not been created!"; echo "Reason: ", $conn->error; > // block of code to process further $conn->close(); ?>

Table has not been created! Reason: Table 'customer' already exists

Note: The mysqli() function is used to open a connection to the MySQL database server in object-oriented style.

Note: The new keyword is used to create a new object.

Note: The connect_errno is used to get or return the error code (if any) from the last connect call in object-oriented style.

Note: The connect_error is used to get the error description (if any) from the last connection in object-oriented style.

Note: The query() function is used to perform queries on the MySQL database in object-oriented style.

Note: The error is used to return the description of the error (if any) by the most recent function call in object-oriented style.

Note: The close() function is used to close an opened connection in object-oriented style.

Note: The mysqli_driver() function is used to modify the error reporting mode in object-oriented style.

Create MySQL Table using PHP mysqli Procedural Script

To create a MySQL table using a PHP mysqli procedural script, follow the example given below:

"; echo "Reason: ", mysqli_connect_error(); exit(); > $sqlCode = "CREATE TABLE student ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, age INT(2), email VARCHAR(40) NOT NULL )"; if(mysqli_query($conn, $sqlCode)) < echo "Table has been created successfully."; // block of code to process further. > else < echo "Table has not been created!"; echo "Reason: ", mysqli_error($conn); > mysqli_close($conn); ?>

Note: The mysqli_report() function is used to modify the error reporting mode in procedural style.

Note: The mysqli_connect() function is used to open a connection to the MySQL database server in procedural style.

Note: The mysqli_connect_errno() function is used to get or return the error code (if any) from the last connect call in procedural style.

Note: The mysqli_connect_error() function is used to return the error description (if any) from the last connection in procedural style.

Note: The mysqli_query() function is used to perform queries on the MySQL database in procedural style.

Note: The mysqli_error() function is used to return the description of the error (if any) from the most recent function call in object-oriented style.

Note: The mysqli_close() function is used to close an opened connection to the MySQL database in procedural style.

PHP MySQL: Create Table Manually

We can also create a table manually. As already mentioned, I am using XAMPP for this «PHP mysqli» tutorial series. Therefore, follow these steps to create a MySQL table manually, without any PHP script.

Step No. 1: Open XAMPP.

Step No. 2: Click on the Start button next to Apache.

Step No. 3: Click on the Start button next to MySQL.

Step No. 4: Click on the Admin button next MySQL.

Step No. 5: Click on the Databases.

Step No. 6: Select or click on the Database, say codescracker.

Step No. 7: Type the name of the table, say client, and type the number of columns to insert in the table, say 4. Here is the snapshot:

Step No. 8: Now click on the Go link or button. Here is the snapshot of the new window:

Step No. 9: Now fill the details of all four columns, and then click on Save to create the table.

Liked this article? Share it!

Источник

PHP MySQL Create Table

A database table has its own unique name and consists of columns and rows.

Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE statement is used to create a table in MySQL.

We will create a table named «MyGuests», with five columns: «id», «firstname», «lastname», «email» and «reg_date»:

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Notes on the table above:

The data type specifies what type of data the column can hold. For a complete reference of all the available data types, go to our Data Types reference.

After the data type, you can specify other optional attributes for each column:

• 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
• AUTO INCREMENT — MySQL automatically increases the value of the field by 1 each time a new record is added
• 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

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

The following examples shows how to create the table in PHP:

Example (MySQLi Object-oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>

// sql to create table
$sql = «CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)»;

if ($conn->query($sql) === TRUE) echo «Table MyGuests created successfully»;
> else echo «Error creating table: » . $conn->error;
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>

// sql to create table
$sql = «CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)»;

if (mysqli_query($conn, $sql)) echo «Table MyGuests created successfully»;
> else echo «Error creating table: » . mysqli_error($conn);
>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;

try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// sql to create table
$sql = «CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)»;

// use exec() because no results are returned
$conn->exec($sql);
echo «Table MyGuests created successfully»;
> catch(PDOException $e) echo $sql . «
» . $e->getMessage();
>

Источник

PHP MySQL: Create A New Table

Summary: in this tutorial, we will show you how to use PHP to create MySQL database table by using PDO API.

The following are the steps to show you how to create a new table in a database:

• Open a database connection to the MySQL database server.
• Execute the CREATE TABLE statement to create new tables.

PHP MySQL: Create table example

We will create a new table named tasks in the sample database with the following SQL script:

CREATE TABLE IF NOT EXISTS tasks ( task_id INT AUTO_INCREMENT PRIMARY KEY, subject VARCHAR (255) DEFAULT NULL, start_date DATE DEFAULT NULL, end_date DATE DEFAULT NULL, description VARCHAR (400) DEFAULT NULL );Code language: SQL (Structured Query Language) (sql)

First, we create a class named CreateTableDemo that has DB configuration parameters. In the constructor of the CreateTableDemo class, we open a connection to the sample database by instantiating a new PDO object.

 /** * PHP MySQL Create Table Demo */ class CreateTableDemo < /** * database host */ const DB_HOST = 'localhost'; /** * database name */ const DB_NAME = 'classicmodels'; /** * database user */ const DB_USER = 'root'; /* * database password */ const DB_PASSWORD = ''; /** * * @var type */ private $pdo = null; /** * Open the database connection */ public function __construct() < // open database connection $conStr = sprintf("mysql:host=%s;dbname=%s", self::DB_HOST, self::DB_NAME); try < $this->pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD); > catch (PDOException $e) < echo $e->getMessage(); > > //. >Code language: PHP (php)

Next, in the destructor of the CreateTabledemo class, we close the database connection by assigning it the null value.

 /** * close the database connection */ public function __destruct() < // close the database connection $this->pdo = null; >Code language: PHP (php)

Then, define the createTaskTable() method to create the tasks table:

 /** * create the tasks table * @return boolean returns true on success or false on failure */ public function createTaskTable() < $sql =  return $this->pdo->exec($sql); >Code language: PHP (php)

To execute an SQL statement, you call the exec() method of the PDO object. After that, you can create an instance of the CreateTableDemo class and call the createTaskTable() method.

// create the tasks table $obj = new CreateTableDemo(); $obj->createTaskTable();Code language: PHP (php)

Finally, you can check the classicmodels sample database to see if the tasks table has been created.

You can download the PHP script file via the following link:

In this tutorial, we have shown you step by step how to use PDO object to create a table in the MySQL database.

Источник