PHP MySQL Create Table Query With MySQLi Example Code and MySQLi_query Function

Create a MySQL Table Using MySQLi

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

We will create a table named “User”, with five columns: “id”, “firstname”, “lastname”, “email_id” and “age”:

Example – 1 Create a MySQL Table Example

`
	CREATE TABLE User (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(50) NOT NULL,
	lastname VARCHAR(50) NOT NULL,
	email_id VARCHAR(50),
	age INT
          )

Important points of 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.

  • 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

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

Example – 1 Create a MySQL Table Example

`
 <?php
$server = "localhost";
$user = "root";
$password = "";
$dbname = "elex_tutorial";

// Create connection
$connection = mysqli_connect($server, $user, $password, $dbname);
// Check connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// sql to create table
$sql = "CREATE TABLE User (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(50) NOT NULL,
	lastname VARCHAR(50) NOT NULL,
	email_id VARCHAR(50),
	age INT
)";

if (mysqli_query($connection, $sql)) {
    echo "Table User created successfully";
} else {
    echo "Error creating table: " . mysqli_error($connection);
}

mysqli_close($connection);
?> 

In the above code, we create a database through the MySQLi Procedure. And after running this program you have to visit phpMyAdmin and check that their a User name table will be generated in the elex_tutorial database or not.

You can leave a response, or trackback from your own site.

2 Responses to “PHP MySQL Create Table Query With MySQLi Example Code and MySQLi_query Function”

  1. Dale Furch says:

    Thanks for sharing your thoughts. I truly appreciate your efforts and I am waiting for your further post thanks once again.|

  2. A lot of thanks for each of your effort on this blog. Kim enjoys participating in investigation and it’s really easy to see why. My spouse and i learn all relating to the powerful mode you give effective steps on your web site and as well recommend response from some other people on that topic plus my daughter has been discovering a lot. Take pleasure in the remaining portion of the new year. You’re conducting a splendid job.

Leave a Reply to the article


Learn PHP

Learning & Certifications
Follow Us
Facebook Icon   Linked In Icon   Twitter Icon  
Validation and Recognition

Valid CSS! Valid HTML5!