How to Create Tables in MySQL Database
In order to manipulate the database, you need tables inside the databases that actually holds the data. Without any tables, you cannot store any data in your MySQL database.
To create a table for MySQL database, you have to first create a SQL create statement and pass the statement to PHP function. There are two alternatives to PHP function you can use to create tables.
- mysqli_query()
- PDO::__query()
MySQLi Procedural
The process is the procedural way designing code and executing the query. You can use the PHP procedural method to create a MySQL database for your project.
A simple example is given below to use in your project as well as test it to your local Xamp or Wamp server. The credentials like hostname, username, and password are given below taken for the localhost. But, If you using another server like any hosting server, you can always change the below-given credentials.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$hostname = "localhost"; $username = "root"; $password = ""; $mydbname = "test" //make connection $dbconn = mysqli_connect($hostname,$username,$password,$mydbname); //check connection if(!$dbconn) { die("Connection Error: ".mysqli_connect_error()); } //create query $sql = "create table user(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,email VARCHAR(20) NOT NULL UNIQUE)"; //execute query and create database if(mysqli_query($dbconn, $sql)){ echo "Table created successfully"; }else{ echo "Could not create table " . mysqli_error($dbconn); } //close connection mysqli_close($dbconn); |
MySQLi Object-oriented
While Developing an application, Using Object-oriented in your coding is the best practice to design a code. You can create the database connection, create your query and execute them by using PHP object-oriented programming.
The process is not different from procedural, only the way of coding is different. Below is the simple object-oriented programming you can use in your project code to create a table for your MySQL database using PHP.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$hostname = "localhost"; $username = "root"; $password = ""; $mydbname = "test" //make connection $dbconn = new mysqli($hostname,$username,$password,$mydbname); //check connection if($dbconn->connect_error) { die("Connection Error: ".$dbconn->connect_error); } //create query $sql = "create table user(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,email VARCHAR(20) NOT NULL UNIQUE)"; //execute query and create database if($dbconn->query($sql)){ echo "Table created successfully"; }else{ echo "Table creation failed ".$dbconn->connect_error; } //close connection $dbconn->close(); |