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:
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:
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.
<?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();
?>
<?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:
Sunil Singh
18-Mar-2017Thanks Royce it is very informative post about mysql.I would like to appreciate you for making very useful and helpful post.