Categories

Articles in MySQL Category

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.

For creating and dropping databases, you will need to use root user or a user with equivalent privileges.
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().

Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.