Create MySQL Database And Table Using PHP In XAMPP

Februari 8, 2023Januari 17, 2023 falma.u99 create, database, mysql, table, using, 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.

Alternatively, you can use the LAMP or LEMP stacks which provides both PHP and MySQL. If you’re on Linux, refer the following guides to install LAMP/LEMP stacks.

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.

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 /Connect_data.php URL. If you see the “Connection established successfully” message, you’re connected with XAMPP.

MySQL Connection Established

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.

Start Apache And MySQL Modules In XAMPP

2. Open phpMyAdmin by navigating to /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.

Create Database Via phpMyAdmin

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.

View Database In PhpMyAdmin

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.

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

Database Creation Successful

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

View Databases In PhpMyAdmin

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 /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.

Create Table Manually Via phpMyAdmin

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 * 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.

Enter Column Name And Length

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.

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

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

A Table Is Created In A Database

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 /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.

View Table Structure In PhpMyAdmin

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.