DAY 13
PHP connection to Mysql
PHP will work with virtually all
database software, including Oracle and Sybase but most commonly used is freely
available MySQL database.
What you should already have ?
·
You have gone through MySQL tutorial
to understand MySQL Basics.
·
Downloaded and installed a latest
version of MySQL.
·
Created database user guest with
password guest123.
·
If you have not created a database
then you would need root user and its password to create a database.
We have divided this chapter in the
following sections −
·
Connecting to MySQL database − Learn
how to use PHP to open and close a MySQL database connection.
·
Create MySQL Database Using PHP − This part explains how to create MySQL database and tables
using PHP.
·
Delete MySQL Database Using PHP − This part explains how to delete MySQL database and tables
using PHP.
·
Insert Data To MySQL Database − Once
you have created your database and tables then you would like to insert your
data into created tables. This session will take you through real example on
data insert.
·
Using Paging through PHP − This
one explains how to show your query result into multiple pages and how to
create the navigation link.
·
Updating Data Into MySQL Database − This part explains how to update existing records into
MySQL database using PHP.
·
Deleting Data From MySQL Database − This part explains how to delete or purge existing records
from MySQL database using PHP.
·
Using PHP To Backup MySQL Database − Learn different ways to take backup of your MySQL database
for safety purpose.
The following content demonstrates
how to create a login page with MySQL Data base. Before enter into the code
part, You would need special privileges to create or to delete a MySQL
database. So assuming you have access to root user, you can create any database
using mysql mysqladmin binary.
Config.php
Config.php file is having information
about MySQL Data base configuration.
<?php
define('DB_SERVER','localhost:3036');
define('DB_USERNAME','root');
define('DB_PASSWORD','rootpassword');
define('DB_DATABASE','database');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>
Login.php
Login PHP is having information about
php script and HTML script to do login.
<?php
include("config.php");
session_start();
if($_SERVER["REQUEST_METHOD"]=="POST"){
// username and password sent from form
$myusername = mysqli_real_escape_string($db,$_POST['username']);
$mypassword = mysqli_real_escape_string($db,$_POST['password']);
$sql ="SELECT id FROM admin WHERE username = '$myusername' and passcode = '$mypassword'";
$result = mysqli_query($db,$sql);
$row = mysqli_fetch_array($result,MYSQLI_ASSOC);
$active = $row['active'];
$count = mysqli_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count ==1){
session_register("myusername");
$_SESSION['login_user']= $myusername;
header("location: welcome.php");
}else{
$error ="Your Login Name or Password is invalid";
}
}
?>
<html>
<head>
<title>Login Page</title>
<styletype="text/css">
body {
font-family:Arial,Helvetica, sans-serif;
font-size:14px;
}
label {
font-weight:bold;
width:100px;
font-size:14px;
}
.box {
border:#666666 solid 1px;
}
</style>
</head>
<bodybgcolor="#FFFFFF">
<divalign="center">
<divstyle="width:300px; border: solid 1px#333333; "align="left">
<divstyle="background-color:#333333; color:#FFFFFF; padding:3px;"><b>Login</b></div>
<divstyle="margin:30px">
<formaction=""method="post">
<label>UserName :</label><inputtype="text"name="username"class="box"/><br/><br/>
<label>Password :</label><inputtype="password"name="password"class="box"/><br/><br/>
<inputtype="submit"value=" Submit "/><br/>
</form>
<divstyle="font-size:11px; color:#cc0000; margin-top:10px"><?php echo $error;?></div>
</div>
</div>
</div>
</body>
</html>
welcome.php
After successful login, it will
display welcome page.
<?php
include('session.php');
?>
<html">
<head>
<title>Welcome </title>
</head>
<body>
<h1>Welcome <?php echo $login_session;?></h1>
<h2><ahref="logout.php">Sign Out</a></h2>
</body>
</html>
Logout page
Logout page is having information
about how to logout from login session.
<?php
session_start();
if(session_destroy()){
header("Location: login.php");
}
?>
session.php
Session.php will verify the session,
if there is no session it will redirect to login page.
<?php
include('config.php');
session_start();
$user_check = $_SESSION['login_user'];
$ses_sql = mysqli_query($db,"select username from admin where username = '$user_check' ");
$row = mysqli_fetch_array($ses_sql,MYSQLI_ASSOC);
$login_session = $row['username'];
if(!isset($_SESSION['login_user'])){
header("location:login.php");
}
?>
