How to create database and table in MySQL in PHP

Create Database

PHP uses mysqli query() or mysql_query() function to create a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required – SQL query to create a MySQL table.
2	$resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required – SQL query to create a MySQL table.

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to create a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Creating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = '[email protected]';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
         $sql = "CREATE TABLE tutorials_tbl( ".
            "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
            "tutorial_title VARCHAR(100) NOT NULL, ".
            "tutorial_author VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( tutorial_id )); ";
         if ($mysqli->query($sql)) {
            printf("Table tutorials_tbl created successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not create table: %s<br />", $mysqli->error);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Table tutorials_tbl created successfully.

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

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.

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