Structured Query Language is used to access database.SQL is an open source programming language that is anyone can use and modify the software. You can easily download MySQL( http://www.mysql.com) from internet and use it without paying it.

SQL is most popular for adding, deleting, modifying data from database. It is well known for its quick processing, reliability and flexible to use. Here are some of the queries of MySQL:

In previous post we learn about how to connect with database in php and how to create database using php

Now we learn about---

How to show database- All database in your database server will be shown.

show databases query is used to show all the databases in your database server.  

<?php 

// Connection with database server
$con = mysqli_connect("192.168.1.51","username","password");
//query to show database
$rec=$con->query("show databases");
while($res=$rec->fetch_assoc())
{
foreach($res as $k=>$v)
{
    echo("$v</br>");
}
}
?>
Output:
  Database1 
Database2
Database3
Database4
Database5

How to create table using PHP: You will learn to create database.

To create a table we have to first create database. A database table has its own unique name and consists of column and rows. create table statement is used to create a table.

<?php
$con = mysqli_connect("192.168.1.51","username","password","first_db");
// creating table
$sql = "CREATE TABLE MyTable ( //MyTable is the name of the table which should be given
id INT(6) PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50))";
if ($con->query($sql) === TRUE) {
    echo "Table MyTable created successfully";
} else {
    echo "Error creating table: " . $con->error;
}
?>

 

Output:

Table MyTable created successfully

How to insert data into a table: once you have created your table then you would like to insert data. In this part you will learn to insert your data into created table.

<?php
$con = mysqli_connect("192.168.1.51","username","password","first_db");
$record=$con->query("insert into MyTable values(1,'Neha','singh','neha@example.com')");
if ($con->query($record) === TRUE) {
    echo "New record inserted successfully";
} else {
    echo "Error: " . $record . "<br>" . $con->error;
}
$con->close();
?>

  Output: 

New record inserted successfully

How to modify data into MySQL database: This part will teach you to update
data of existing record.

<?php

$con = mysqli_connect("192.168.1.51","username","password","first_db");
$record = "UPDATE MyTable SET lastname='bharat' WHERE id=2";
if ($con->query($record) === TRUE) {
    echo " Record updated successfully .";

} else {
    echo "Error: " . $record . "<br>" . $con->error;
}
$con->close();
?>
Output: 
Record updated successfully.

Note: Where clause is used to specify which record is to be update. If you elide where clause than all record will be updated.

How to delete data from MySQL database: This part will delete data of existing
record using PHP.

The DELETE statement is used to delete records from a table. This part will delete data of existing record using PHP.

<?php
$con = mysqli_connect("192.168.1.51","username","password","first_db");
$record = "DELETE FROM MyTable WHERE id=1";
if ($con->query($record) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error: " . $record . "<br>" . $con->error;
}
$con->close();
?>

  Output: 

Record deleted successfully.


Note: Where clause is used to specify which record is to be deleted. If you elide where clause than all record will be deleted.
How to drop table: This section will teach you to remove table from database.
The DROP TABLE statement is used to delete a table.

<?php
$con = mysqli_connect("192.168.1.51","username","password","first_db");
$record = "DROP TABLE MyTable";
if ($con->query($record) === TRUE) {
    echo "Table is deleted successfully.";
} else {
    echo "Error: " . $record . "<br>" . $con->error;
}
$con->close();
?>
 Output: 
Table is deleted successfully.
How to delete database: using PHP it is possible to drop database.
The DROP statement is used to delete or remove database is used.
<?php
$con = mysqli_connect("192.168.1.51","username","password");
$record = "DROP DATABASE first_db";
if ($con->query($record) === TRUE) {
    echo "Database is deleted successfully";
} else {
    echo "Error: " . $record . "<br>" . $con->error;
}
$con->close();
?>
  
Output:
Database is deleted successfully.

 

 

  Modified On Dec-18-2017 11:55:18 PM
  1. Thanks Royce it is very informative  post about mysql.I would like to appreciate you for making very useful and helpful post.

Leave Comment