How to Create MySQL Database using PHP

Hello folks! welcome back to a new section of our tutorial on PHP. In this section of our PHP tutorial, we will be studying about how to create a MYSQL Database using PHP.

This tutorial is divided into three sections -

• Creating a Database

• Selecting a Database

• Creating Database Tables

We are going to be looking at each of these sections one after the other.

READ: How to Connect to MySQL Database using PHP

Creating a Database

To create and delete a database you need to have admin privilege. It is easy to create a new MySQL database. PHP makes use of the mysql_query function to create MySQL database. The mysql_query function takes two parameters and then returns TRUE on success or FALSE on failure.

Syntax

Following below is the syntax to use the mysql_query function -

bool mysql_query( sql, connection );

Parameter Details

Following below is the details of the above parameters -

Sr.No	Parameter & Description
1	sql Required - SQL query to create a database
2	connection Optional - if not specified then last opend connection by mysql_connect will be used.

Example

Try out the following example to create a database -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   echo 'Connected successfully';
   
   $sql = 'CREATE Database test_db';
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not create database: ' . mysql_error());
   }
   
   echo "Database test_db created successfully\n";
   mysql_close($conn);
?>

READ: PHP Date and Time

Selecting a Database

Soon as you have established a connection with the database server, it is required that you choose a particular database where all your tables are associated.

It is required because there may be multiple databases that are residing on a single web server and you can only work with a single database at a time.

PHP provides the mysql_select_db function for selecting a database. It returns TRUE on success or FALSE on failure.

Syntax

Following below is the syntax to use the mysql_select_db function -

bool mysql_select_db( db_name, connection );

Parameter Details

Following below is the details of the above parameters -

Sr.No	Parameter & Description
1	db_name Required - Database name to be selected
2	connection Optional - if not specified then last opend connection by mysql_connect will be used.

Example

Try the following example below to select a database -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'guest';
   $dbpass = 'guest123';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) { 
      die('Could not connect: ' . mysql_error());
   }
   
   echo 'Connected successfully';
   
   mysql_select_db( 'test_db' );
   mysql_close($conn);
   
?>

READ: PHP Regular Expressions

Creating Database Tables

To create tables in new database, you will need to do the same thing as creating the database. You first have to create the SQL query to create the tables, then execute the SQL query using mysql_query() function.

Example

Try out the following example to create a table -

<?php
   
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   echo 'Connected successfully';
   
   $sql = 'CREATE TABLE employee( '.
      'emp_id INT NOT NULL AUTO_INCREMENT, '.
      'emp_name VARCHAR(20) NOT NULL, '.
      'emp_address  VARCHAR(20) NOT NULL, '.
      'emp_salary   INT NOT NULL, '.
      'join_date    timestamp(14) NOT NULL, '.
      'primary key ( emp_id ))';
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not create table: ' . mysql_error());
   }
   
   echo "Table employee created successfully\n";
   
   mysql_close($conn);
?>

READ: PHP Coding Standards and Guidelines

If you need to create numerous tables then its better to create a text file first and put all the SQL commands in that text file and load that file into $sql variable and then execute those commands.

Example

Consider the below content in sql_query.txt file -

CREATE TABLE employee(
   emp_id INT NOT NULL AUTO_INCREMENT,
   emp_name VARCHAR(20) NOT NULL,
   emp_address  VARCHAR(20) NOT NULL,
   emp_salary   INT NOT NULL,
   join_date    timestamp(14) NOT NULL,
   primary key ( emp_id ));

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $query_file = 'sql_query.txt';
   
   $fp = fopen($query_file, 'r');
   $sql = fread($fp, filesize($query_file));
   fclose($fp); 
   
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not create table: ' . mysql_error());
   }
   
   echo "Table employee created successfully\n";
   mysql_close($conn);
?>

READ: PHP Cookies with Examples

Alright guys! This is where we are rounding up for this tutorial post. In our next tutorial guide, we are going to be discussing about how to delete MYSQL database using PHP.

Feel free to ask your questions where necessary and we will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Follow us on our various social media platforms to stay updated with our latest tutorials. You can also subscribe to our newsletter in order to get our tutorials delivered directly to your emails.

Thanks for reading and bye for now.