- PHP Basics
- PHP Home
- PHP Environment Setup
- PHP Getting Started
- PHP Basic Syntax
- PHP echo
- PHP print
- PHP echo Vs print
- PHP Comments
- PHP Data Types
- PHP Variables
- PHP Variable Scope
- PHP gettype()
- PHP Constants
- PHP Operators
- PHP Program Control
- PHP Decision Making
- PHP if-elseif-else
- PHP switch
- PHP Loops
- PHP for Loop
- PHP while Loop
- PHP do-while Loop
- PHP foreach Loop
- PHP break & continue
- PHP Popular Topics
- PHP Arrays
- PHP print_r()
- PHP Strings
- PHP Functions
- PHP References
- PHP Object Oriented
- PHP Object Oriented
- PHP Classes & Objects
- PHP Member Variable
- PHP Member Function
- PHP Encapsulation
- PHP Data Abstraction
- PHP Inheritance
- PHP Constructor Destructor
- PHP Polymorphism
- PHP Web Developments
- PHP Web Developments
- PHP GET & POST
- PHP Read Requested Data
- PHP File Handling (I/O)
- PHP File Handling (I/O)
- PHP fopen() | Open File
- PHP Create a File
- PHP fwrite() | Write to File
- PHP fread() | Read File
- PHP feof()
- PHP fgetc()
- PHP fgets()
- PHP fclose() | Close File
- PHP unlink() | Delete File
- PHP Append to File
- PHP copy() | Copy File
- PHP file_get_contents()
- PHP file_put_contents()
- PHP file_exists()
- PHP filesize()
- PHP rename() | Rename File
- PHP fseek()
- PHP ftell()
- PHP rewind()
- PHP disk_free_space()
- PHP disk_total_space()
- PHP mkdir() | Create Directory
- PHP rmdir() | Remove Directory
- PHP glob() | Get Files/Directories
- PHP basename() | Get filename
- PHP dirname() | Get Path
- PHP filemtime()
- PHP file()
- PHP Advanced
- PHP Cookies
- PHP Sessions
- PHP Send Emails
- PHP Serialization
- PHP Namespaces
- PHP File Upload
- PHP Date and Time
- PHP Image Processing
- PHP Regular Expression
- PHP Predefined Variables
- PHP Error Handling
- PHP Debugging
- PHP and MySQLi Tutorial
- PHP and MySQLi Home
- PHP MySQLi Setup
- PHP MySQLi Create DB
- PHP MySQLi Create Table
- PHP MySQLi Connect to DB
- PHP MySQLi Insert Record
- PHP MySQLi Fetch Record
- PHP MySQLi Update Record
- PHP MySQLi Delete Record
- PHP MySQLi SignUp Page
- PHP MySQLi LogIn Page
- PHP MySQLi Store User Data
- PHP MySQLi Close Connection
- PHP connect_errno
- PHP connect_error
- PHP query()
- PHP fetch_row()
- PHP fetch_assoc()
- PHP fetch_array()
- PHP free_result()
- PHP error
- PHP prepare()
- PHP bind_param()
- PHP execute()
- PHP fetch()
- PHP store_result()
- PHP num_rows
- PHP bind_result()
- PHP get_result()
- PHP mysqli_result Class
- PHP Error Constants
- PHP mysqli_driver()
- PHP Misc
- PHP error_reporting()
- PHP Escape Special Characters
- PHP htmlspecialchars()
- PHP new
- PHP header()
- PHP getallheaders()
- PHP empty()
- PHP isset()
- PHP unset()
- PHP exit()
- PHP exit Vs break
- PHP include()
- PHP require()
- PHP include() Vs require()
- PHP AJAX & XML
- PHP AJAX
- PHP XML
- PHP File Handling Functions
- PHP abs()
- PHP Test
- PHP Online Test
- Give Online Test
- All Test List
Create MySQL Table using PHP MySQLi Script
This article is created to describe, how to create a MySQL table using PHP MySQLi script. I have approached the following two ways, to do the task:
- Using PHP MySQLi object-oriented script
- Using PHP MySQLi procedural script
Whatever we choose the approach to create a table using PHP MySQLi script. Some 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 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 following columns:
- id
- name
- age
while creating the columns, be sure to provide the data type of the column along with 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 consists of letters, numbers, and special characters.
Note - The INT refers to a medium size integer. The range of signed int are from -2147483648 to 2147483647.
Note - The NOT NULL is used for, not to accept NULL values.
Create MySQL Table using PHP MySQLi Object-Oriented Script
To create a MySQL table using PHP MySQLi object-oriented script, follow the example given below:
<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "codescracker"; $conn = new mysqli($servername, $username, $password, $dbname); if($conn->connect_errno) { echo "Connection to the database failed!<BR>"; 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!<BR>"; echo "Reason: ", $conn->error; } $conn->close(); ?>
The output produced by above PHP example on 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 codescracker database. There, you will see the customer table with four columns namely id, name, age, and email. Here is the snapshot of the table:
That is:
Now let me re-execute the above PHP script again. Okay, here is the output I have got:
That is:
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 {main} 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 above output is, the error we are seeing, is not 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:
<?php $driver = new mysqli_driver(); $driver -> report_mode = MYSQLI_REPORT_OFF; $conn = new mysqli("localhost", "root", "", "codescracker"); if($conn->connect_errno) { echo "Connection to the database failed!<BR>"; 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!<BR>"; echo "Reason: ", $conn->error; } // block of code to process further $conn->close(); ?>
Now the output should be:
Table has not been created! Reason: Table 'customer' already exists
Note - The mysqli() 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/return the error code (if any) from last connect call, in object-oriented style.
Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.
Note - The query() is used to perform query on the MySQL database, in object-oriented style.
Note - The error is used to return the description of error (if any), by the most recent function call, in object-oriented style.
Note - The close() is used to close an opened connection, in object-oriented style.
Note - The mysqli_driver() 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 PHP MySQLi procedural script, follow the example given below:
<?php mysqli_report(MYSQLI_REPORT_OFF); $conn = mysqli_connect("localhost", "root", "", "codescracker"); if(mysqli_connect_error()) { echo "Connection to the database failed!<BR>"; 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!<BR>"; echo "Reason: ", mysqli_error($conn); } mysqli_close($conn); ?>
Note - The mysqli_report() is used to modify the error reporting mode, in procedural style.
Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.
Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.
Note - The mysqli_connect_error() is used to return the error description (if any) from the last connection, in procedural style.
Note - The mysqli_query() is used to perform query on the MySQL database, in procedural style.
Note - The mysqli_error() is used to return the description of error (if any), by the most recent function call, in object-oriented style.
Note - The mysqli_close() 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, that I am using XAMPP for this PHP MySQL 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, next to Apache
Step No.3: Click on the Start, next to MySQL
Step No.4: Click on the Admin, next MySQL
Step No.5: Click on the Databases
Step No.6: Select/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/button. Here is the snapshot of the new window:
Step No.9: Now fill the details of all the four columns, and then click on the Save to create the table. That is it.
« Previous Tutorial Next Tutorial »
Follow/Like Us on Facebook
Subscribe Us on YouTube