MySQL Database Commands
MySQL has four database commands as show, create, drop and use. You can execute these commands in command-line and in PHP scripts. However since many PHP applications use only one database, it’s rare to find these commands in PHP scripts.
Show Databases
Login to MySQL in command-line as the root user or any other privileged user and enter following command in MySQL prompt. You will be able to see a list of databases allowed for logged in user.
show databases;
You can try this command in PHP as below. Replace mysqli_connect() details with the details of a MySQL user in your computer.
<?php $link = mysqli_connect('localhost', 'robin', 'robin123'); $result = mysqli_query($link, 'show databases'); while ($row = mysqli_fetch_array($result)) { echo $row[0] . '<br />'; } ?>
Create Database
To create a new database, log in as a user who has privileges to create databases and use following command. Type the database name you want in place of my_database.
create database my_database;
In PHP, you can create a database as below.
<?php $link = mysqli_connect('localhost', 'root', 'root123'); $result = mysqli_query($link, 'create database my_database'); if ($result) { echo 'Database was created successfully'; } else { echo mysqli_error($link); } ?>
Drop Database
Use below command to delete a database. Replace my_database with the name of the database you want to delete.
drop database my_database;
You can achieve the same result in PHP by passing the command to mysqli_query() as below.
<?php $link = mysqli_connect('localhost', 'root', 'root123'); $result = mysqli_query($link, 'drop database my_database'); if ($result) { echo 'Database was dropped successfully'; } else { echo mysqli_error($link); } ?>
Use Database
In MySQL, before executing any table level commands, you need to choose a database. You can select the database my_database to work on using following command.
use my_database;
In PHP, you can simply pass the database to be used as the forth parameter of mysqli_connect(). After connecting, if you want to change the database, use mysqli_select_db().