August 20,2016

MYSQL is used as a database at the webserver and PHP is used to fetch data from the database. Our application will communicate with the PHP page with necessary parameters and PHP will contact MYSQL database and will fetch the result and return the results to us.

PHP – MYSQL

Creating Database

MYSQL database can be created easily using this simple script. The CREATE DATABASE statement creates the database.

<?php
   $con=mysqli_connect("example.com","username","password");
   $sql="CREATE DATABASE my_db";
   if (mysqli_query($con,$sql)) {
      echo "Database my_db created successfully";
   }
?>

Creating Tables

Once database is created, its time to create some tables in the database. The CREATE TABLE statement creates the database.

<?php
   $con=mysqli_connect("example.com","username","password","my_db");
   $sql="CREATE TABLE table1(Username CHAR(30),Password CHAR(30),Role CHAR(30))";
   if (mysqli_query($con,$sql)) {
      echo "Table have been created successfully";
   }
?>

Inserting Values in tables

When the database and tables are created. Now its time to insert some data into the tables. The Insert Into statement creates the database.

<?php
   $con=mysqli_connect("example.com","username","password","my_db");
   $sql="INSERT INTO table1 (FirstName, LastName, Age) VALUES ('admin', 'admin','adminstrator')";
   if (mysqli_query($con,$sql)) {
      echo "Values have been inserted successfully";
   }
?>

PHP – GET and POST methods

PHP is also used to fetch the record from the mysql database once it is created. In order to fetch record some information must be passed to PHP page regarding what record to be fetched.

The first method to pass information is through GET method in which $_GET command is used. The variables are passed in the url and the record is fetched. Its syntax is given below −

<?php
   $con=mysqli_connect("example.com","username","password","database name");

   if (mysqli_connect_errno($con)) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
   }

   $username = $_GET['username'];
   $password = $_GET['password'];
   $result = mysqli_query($con,"SELECT Role FROM table1 where Username='$username' 
      and Password='$password'");
   $row = mysqli_fetch_array($result);
   $data = $row[0];

   if($data){
      echo $data;
   }
   mysqli_close($con);
?>

The second method is to use POST method. The only change in the above script is to replace $_GET with $_POST. In Post method, the variables are not passed through URL.