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