Create MySQL Database And Table Using PHP In XAMPP

In this guide, we will discuss how to create a MySQL database and table using PHP and also how to create the MySQL/MariaDB databases and tables via phpMyAdmin in XAMPP stack.

Prerequisites

Make sure you have setup XAMPP stack in your system. The following guide explains how to setup XAMPP stack in Linux.

• How To Install XAMPP In Linux

Setting up XAMPP is much easier than LAMP and LEMP stacks. So, we will will be using XAMPP stack throughout this guide.

Connect to MySQL using PHP

1. Specify MySQL servername, username and password parameters in your PHP code.

Here, my servername is localhost, MySQL username is root and its password is empty.

2. Create a connection using the above details.

By using mysqli_connect() function, we will establish a connection. It will take three parameters. First will be the servername, second is the username (i.e. root) and last is the password. It will also take a database name which is optional here, because we are only creating the connection.

Code:

$connection = mysqli_connect($server_name, $user_name, $password);

3. Check the connection.

We can check the connection using the mysqli_connect_error() function specified in an if condition. This function will display an error, if the connection is failed.

Now, let us write a sample PHP code based on the above steps.

PHP Code to Connect to MySQL

Create a new PHP file named Connect_data.php under the /htdocs directory with the following contents in it.

Heads Up: If you use Linux, the htdocs folder will be under /opt/lampp/ directory. If you're on Windows, the htdocs will be usually in C:\xampp\ folder.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password);

// Checking the  connection
if (!$connection) {
  die("Failed ". mysqli_connect_error());
}
echo "Connection established successfully";
?>

Heads Up: If you're using LAMP/LEMP stacks, you may need to update the MySQL root password in the above code.

Open your web browser and point it to http://localhost/Connect_data.php URL. If you see the "Connection established successfully" message, you're connected with XAMPP.

We are going to use this code snippet in out subsequent examples.

Create Database Manually via phpMyAdmin

We can define a database as a collection of related data. Data refers to tables. Within a single database, we can create any number of tables.

1. Start the Apache and MySQL modules from XAMPP control center.

2. Open phpMyAdmin by navigating to http://localhost/phpmyadmin/ URL from your web browser.

3. Click the Databases tab and enter the Database name that we are going to create. Here, I use "My_Company" as the database name. Leave the second tab as it is and click on Create.

A new database named "My_Company" has just been created. You can view the newly created database from the left pane or on the top title bar of the phpMyAdmin dashboard.

We can also do the same withe a piece of PHP code as described in the following section.

Create Database using PHP Code

We are going to include a SQL Query statement in our PHP code to create the database.

The CREATE command in SQL can be used to create a database.

Query Syntax:

CREATE DATABASE database_name

Where database_name is the name of the database.

Let me explain the steps to create a database using PHP.

Steps

The first three steps are same as we did while establishing connection with MySQL in the earlier step.

1. Specify servername, mysql username and password fields in the PHP code.

Here, the servername is localhost, username is root and password is empty.

2. Create a connection using the above details.

By using my_sqli_connect() function, we will establish a connection. It will take three parameters. First will be the servername, second is the username and last is password. It will also take a database name which is optional here, because we are just creating connection.

Code:

$connection = mysqli_connect($server_name, $user_name, $password);

3. Check the Connection.

We can check the connection using the mysqli_connect_error() function specified in an if condition. This function will display an error, if the connection is failed. This is an optional step.

4. Specify the SQL Query to create a database.

In this step, we specify the SQL query to create the database and store it in a variable.

Remember, we've already created a database named "My_Company". So, we will now create a new database called "mycompany" and we are storing it in a variable named query.

Code:

$query = "CREATE DATABASE mycompany";

5. Verifying the database creation.

If we want to check if the database is created or not, we can use the mysqli_query() function.

Code:

mysqli_query($connection, $query)

It will take two parameters.

• $connection specifies the connection details.
• $query is the sql query.

If this is True, then a database is created. Otherwise it will return an error. We can check the error by using the mysqli_error($connection) function.

6. Close the connection.

This is the last step where we have to close the connection by using the mysqli_close() function.

Code:

mysqli_close($connection);

Now let us write the actual PHP code to accomplish the above steps.

PHP Code to Create MySQL Database

Please note that we are going to create a new MySQL database named "mycompany" in this example.

Create a new PHP file named create_database.php under the /htdocs directory with the following contents in it.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password);

// Checking the  connection
if (!$connection) {
  die("Failed ". mysqli_connect_error());
}
echo "Connection established successfully." . "\n";

//sql query to create a database named mycompany
$query = "CREATE DATABASE mycompany";
if (mysqli_query($connection, $query)) {
  echo "A new database called mycompany is successfully created!";
} else {
  echo "Error:" . mysqli_error($connection);
}

mysqli_close($connection);
?>

Open your web browser and navigate to http://localhost/create_database.php. You will see an output like this in your browser window.

You can verify if the database "mycompany" is actually created from the phpMyAdmin dashboard.

Create Table Manually via phpMyAdmin

What we have seen so far is how to create a MySQL database using PHP code and via phpMyAdmin dashboard. The databases are empty. Now, we will create some tables in the databases.

1. Login to phpMyAdmin by navigating to http://localhost/phpmyadmin from your web browser.

2. Select the database in which you want to create tables. Here, I will be using "mycompany" database.

Provide the table Name and number of columns to be present in the database. Here, I give the table name as SALES and number of columns are 3. Click Go to create the table.

3. Specify the column names and length of column with data type.

• Column 1 name is "id" with type as INT and LENGTH/VALUES - 1
• Column 2 name is "name" with type as VARCHAR and LENGTH/VALUES - 255
• Column 3 name is "count" with type as INT and LENGTH/VALUES - 1

Here, INT represents Integer values and VARCHAR represents string values.

After entering the column names and their length, click Save button at the bottom.

Once the columns are created, you will see them under under "Structure" tab.

In this way, you can create as many tables as you want via phpMyAdmin.

Now, we will see how to create a table using a PHP code snippet.

Create Table using PHP Code

We will use a SQL Query statement in our PHP code to create a table in the database.

We can use CREATE command to create a table in a MySQL database.

Code:

CREATE TABLE table_name(
Column_name datatype,
Column_name datatype,
…………..
…………..);

Where, table_name is the name of the table and Column_name specifies the name of the column along with its datatype.

Steps

Creating tables in a MySQL database using PHP code consists of the following steps.

1. Specify MySQL server name, username, password and database name in the PHP code.

Here, the servername is localhost, username is root and password is empty.

We already have created a table called "Sales" under "mycompany" database. So, we will now create a new table called "Service" in the same database.

2. Connect to MySQL database using the above details.

By using mysqli_connect() function, we will establish a connection. It will take three parameters. First is the servername, second is the username and the last is password. It will also take a database name which is optional here, because we are just creating connection.

Code:

$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

3. Check the Connection.

We can check the connection using the mysqli_connect_error() function specified in an if condition. This function will display an error, if the connection is failed.

4. Specify the SQL Query to create a table.

In this step, we specify the SQL query to create a table called "Service" in the "My_Company database" and store it in a variable named query. The table "Service" has three columns.

Code:

$query = “CREATE TABLE  Service(
id int,
name varchar(244),
count int
)”;

5. Verifying the table creation.

To check if the table is created or not, we can use the mysqli_query() function.

Code:

mysqli_query($connection, $query)

It will take two parameters.

• The $connection parameter specifies the connection details.
• The $query parameter is the sql query that we use to create the table with the gieven number of columns in the database.

If this condition is True, then a table will be created. Otherwise it will return an error. We can check the error by using the mysqli_error($connection) function.

6. Close the connection.

This is the last step where we have to close the connection by using the mysqli_close() function.

Code:

mysqli_close($connection);

Now let us write a sample PHP code based on the above steps.

PHP Code to Create a Table in a Database

The following code will create a new table called "Service" in the existing "My_Company" database.

Create a new PHP file named create_table.php under /htdocs directory with the following contents in it.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name - "My_company"
$database_name = "My_Company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

//sql query to create a table named Service with three columns
$query = "CREATE TABLE  Service(
id int,
name varchar(244),
count int
)";
if (mysqli_query($connection, $query)) {
  echo "Table is successfully created in My_Company database.";
} else {
  echo "Error:" . mysqli_error($connection);
}

//close the connection
mysqli_close($connection);
?>

Now let us write a sample PHP code based on the above steps.

Open your web browser and navigate to http://localhost/create_table.php. You will see an output like this in your browser window.

You can verify if the table called "Service" is actually created in the "My_Company" database via phpMyAdmin dashboard.

To do so, open phpMyAdmin dashboard by navigating to http://localhost/phpmyadmin URL from the browser.

Expand the database name (i.e. My_Company) on the left pane and choose the table name (i.e. Service). And then click Structure tab on the right pane. Under the Structure tab, you will see the table's columns.

Conclusion

In this step by step tutorial, we discussed how to connect with MySQL database and how to create a MySQL database and a table using PHP code and via phpMyAdmin dashboard.

We have provided sample PHP codes for your reference. Amend the code as per your requirement and check it in XAMPP. In our upcoming articles, we will learn more PHP MySQL related topics.Read Next:

• How To Insert Data In MySQL Database Using PHP In XAMPP Stack