5.1 Introduction to MySQL: Following are the features of MySQL Database:
1. MySQL is opensource database software maintained by oracle Corporation.
2. It is available in a packaged form with WAMP, LAMP and XAMP Servers popularly used for PHP.
3. It follows the SQL Standard and runs on Mac OS, Windows and all Linux versions of OS.
4. It can be customized as per the developers need also.
There are two approaches to start working with database
1. With Separate installation of MySQL Server software.
2. By using any of the packaged server WAMP, LAMP or XAMP.
Note: All the further steps are explained by considering WAMP server in your machine. For Linux and Mac OS, steps may vary and henceforth you should refer www.mysql.com for assistance.
Creation of database:
Syntax: create database <database_name>.
Following is a GUI based method:
1. Run WAMP Server in Windows and wait until color of server icons turns into green in icon tray.
2. Open any browser and write localhost/phpmyadmin
3. Enter Username root and Password empty.
4.Click on Database Tab then write name of database and click on create button.
You will see message in green background database “name ” has been created.
Creation of table:Steps to create table:
1. Select the database from left Pane tree.
2. Click on SQL Tab it will open editor box.
3. Write Table query.
Syntax:
create table<table-name>(
Colname1 datatype1 constraint1,
Colname2 datatype2 constraint2,
);
Example: create table student ( enrollvarchar(20));
4. Then Click on Go it will execute query and table will be created if there is no error.
MySQL connect: To connect with database through command line following steps are executed:
1. Open Command Prompt.
2. Write mysql –u <username> -p.
3. It will prompt for Password, enter it.
4. If there is no database then create database using syntax: create database <name>
5. To use that database write in Command Prompt > use <dbname>.
6. Now you are connected to your database through command line
MySQL insert:To add record in a table following syntax is used:
Insert into <table-name> (col1,col2….) values (val1,val2…..);
Example: insert into student (enroll) values (‘ab/1323’);
MySQL Update:
Syntax:
Update <table-name> set <colname> = <value> where < optional - condition>.
Example:
Update student set enroll = ‘ab/111’ where enroll=’ab/1323’;
MySQL select:
Syntax:
Select colname,…..from<table-name> where <condition>.
Example: Select * from student where enroll=’ab/123’;
MySQL delete.
Syntax:
deletefrom <table-name> where <condition>.
Example: delete from student where enroll=’ab/123’;
5.2 Connecting to a MYSQL database:
MYSQL database server from PHP: It is a best practice to use WAMP server for windows user or LAMP Server by Linux user to work with PHP with MySQL.
WAMP stands for W-Windows A-Apache M-MySQL P-PHP and L in LAMP is for Linux.
Connecting database server,Selecting MySQL database and closing database connection: There are three ways to connect PHP with MySQL similarly there are three ways to close the connection all are demonstrated below:
1. Using mysqli_connect() functionin a procedural way:
Example:
<?php
$server_name = "localhost"; // if location is different machine then ip is also accepted
$uname = "usr";
$passwd = "pwd";
// Using Function connection is created
$isConnected = mysqli_connect($server_name, $uname, $passwd);
// isConnected : true else return false
if (!$isConnected) {
die("Failed to Connect: " . mysqli_connect_error());
}
echo "Successfully Connected";
mysqli_close($isConnected); //close the connection
?>
2. Using mysqli Object Oriented way:
Example:
<?php
$server_name = "localhost"; // if location is different machine then ip should be mentioned
$uname = "usr";
$passwd = "pwd";
// Using Objectconnection is created
$isConnected = new mysqli($server_name, $uname, $passwd);
if (!$isConnected->connect_error) {
die("Failed to Connect: " .$isConnected->connect_error);
}
echo "Successfully Connected";
$isConnected->close(); //close the connection
?>
3. Using PHP Data Object (PDO) way:
Example:
<?php
$server_name = "localhost"; // if location is different machine then ip is also accepted
$uname = "usr";
$passwd = "pwd";
try{
$isConnected = new PDO("mysql:host=$server_name;dbname=myData", $uname, $passwd);
// set the PDO error mode to exception
$ isConnected->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e)
echo"Failed to Connect: " . $e->getMessage();
}
echo "Successfully Connected";
$connection=null; //close the connection
?>
//In PDO you have to mention database name also, else you will get an error.
Out of 3 ways best one is PDO as it is supported by 12 different database software and mysqli will only work with mysql. So PDO is more flexible. Henceforth examples are given by using PDO only.
Executing SQL statements retrieving the result,Create table and Insert data to the table from PHP application, Retrieve, Update and delete table data from MYSQL server and display it in PHP page:
Below example demonstrate the Create, Read, Update, and Delete (CRUD) operation in single page PHP application with MySQL using PDO.
<?php
$server_name = "localhost"; // if location is different machine then ip is also accepted
$uname = "usr";
$passwd = "pwd";
try{
$connection = new PDO("mysql:host=$server_name;dbname=myInfo", $uname, $passwd);
// set the PDO error mode to exception
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//Create table
$sqlcreate = "create table student (enroll varchar(20), city varchar(20))";
// use exec() for create statement
$connection->exec($sqlcreate);
echo "Table created";
//Insert record
$sqlinsert = "insert into student (enroll,city) values (‘CSE/123’,’Mumbai’)";
// use exec() for insert statement
$connection->exec($sqlinsert);
echo "Record added in table";
//Update Record
$sqlupdate = "update student set city=’Chennai’ where city=’Mumbai’";
// use exec() for update statement
$connection->exec($sqlupdate);
echo "Record updated in table";
//delete Record
$sqldelete = "delete from student where city=’Mumbai’";
//use exec() for delete statement
$connection->exec($sqldelete);
echo "Record deleted from table";
//Read Record
$sqlselect = "select * from student where city=’Chennai’";
//use query() for select statement
$result=$connection->query($sqlselect);
while($row=$result->fetch(PDO::FETCH_ASSOC)) //to get result row wise
{
echo $row[‘enroll’].” “.$row[‘city’];
}
}catch(PDOException $e)
echo "Failed to Connect: " . $e->getMessage();
}
echo "Successfully Connected";
$connection=null; //close the connection
?>
No comments:
Post a Comment