Tuesday, October 6, 2020

How to Create MySQL Database using PHP



Hello dear readers! welcome back to another section of my tutorial on PHP. In this section of my 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 into each of these sections one after the other.


Creating a Database

To create and delete a database you need to have admin privilege. It is so easy to create a new MySQL database. PHP uses mysql_query function for creating the MySQL database. This function takes two parameters and then returns True on success or False on failure.

Syntax

Following below is the syntax of the mysql_query function -

bool mysql_query( sql, connection );

Parameter Details

Following below is the details of the above parameters -

Sr.NoParameter & 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);
?>

RECOMMENDED: PHP Date and Time

Selecting a Database

Soon as you have established a connection with a database server, its required to select a particular database where all your tables are associated.

This is required because there may be multiple databases that are residing on a single server and you can only work with just 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 of the mysql_select_db function -

bool mysql_select_db( db_name, connection );

Parameter Details

Following below is the details of the above parameters -

Sr.NoParameter & 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);
   
?>


Creating Database Tables
To create tables in new database, you will need to do the same thing as creating the database. First create the SQL query to create the tables, then execute the SQL query using the 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);
?>


In case you need to create many 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 execute those commands.

Example
Consider the following 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);
?>


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

Feel free to ask your questions where necessary and i 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.
Share:

0 comments:

Post a Comment

Hello dear readers! Please kindly try your best to make sure your comments comply with our comment policy guidelines. You can visit our comment policy page to view these guidelines which are clearly stated. Thank you.