In this article, we are going to learn how to create table in to mysql database without directly interacting with with database itself. We are going to use php script that with enble us establish a connection to the database. this works with both local and remote databases as long as firewall rules are enabled.
Requirements
Make sure that you have the MySQL server installed on your server or computer.
Ensure that you have PHP installed. If not, click here to install PHP.
Editor where you are going to write your codes. I will prefer you use visual studio code. It is one of the most powerful lightweight code editors.
Connection
First and very important thing we need to create connection to our database. Have you ever seen how a VPN works? you establish a connection then you behave as if you are in that local network in remote site.
The same way, aafter creating this connection we will now have logged in to mysql and operating as if we are in the database codewise.
host = 'localhost';
$dbname = 'test';
$user = 'root';
$dbpass = '';
$conn;
$conn = new mysqli ($host, $user, $dbpass, $dbname);
if($conn=== false){
die("Error: Could not connect to the specified database to host : localhost" . $conn->connect_error);
}$host – Is used to declare database host which is localhost. this simply implies that database is hosted on the same server from where you are executing your code from.
$dbname – This one initializes the name of the database in which we want to create ou table.
$user – User to the database we want to connect.
$dbpass – Authentication password to the database we want to connect.
As per the above, am using localhost with test as my database name, root as username with no password. Replace the above with correct details matching your server.
Creating A Table
Once the connection is successful, initiate an SQL query to create the table in the connected/logged-in database. To be sure whether you are successful, use the if statement to check for status. If the table has been created successfully, you can output a message to notify you then close the connection.
$sql = "CREATE TABLE test_tbl( \n"
. " id INT NOT NULL AUTO_INCREMENT, \n"
. " first_name VARCHAR(100) NOT NULL, \n"
. " submission_date DATE, \n"
. " last_name VARCHAR(40) NOT NULL, \n"
. " PRIMARY KEY ( id ))";Find the complete code below. Copy and paste in your editor and try to execute.
<?php
$host = 'localhost';
$dbname = 'test';
$user = 'root';
$dbpass = '';
$conn;
$conn = new mysqli ($host, $user, $dbpass, $dbname);
if($conn=== false){
die("Error: Could not connect to the specified database to host : localhost" . $conn->connect_error);
}
$sql = "CREATE TABLE test_tbl( \n"
. " id INT NOT NULL AUTO_INCREMENT, \n"
. " first_name VARCHAR(100) NOT NULL, \n"
. " submission_date DATE, \n"
. " last_name VARCHAR(40) NOT NULL, \n"
. " PRIMARY KEY ( id ))";
Database
if($conn->query($sql)===true){
echo "Table was created successfully";
}
else{
"Error: could not execute $sql. " . $conn->error;
}
$conn->close();
?>Wish you all the best in your coding career.
