Working with PHP and MySQL

Working with PHP and MySQL  

With PHP, you can connect to and manipulate databases.

MySQL is the most popular database system used with PHP.

---

What is MySQL?

• MySQL is a database system used on the web
• MySQL is a database system that runs on a server
• MySQL uses standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use

The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.

Installing MySQL (with WAMP) 

if you already Installed WAMP Server MySQL is already Installed and view through PhpMyadmin is a free, open source platform used to administer MySQL with a web browser; 

test PHPMyadmin click this link http://localhost/phpmyadmin/

Creating Database

1. Create database in PHPMyadmin interface 

1. 2.Create database in PHPMyadmin SQL Query

                    you can create databse with SQL Query 

                    CREATE DATABASE `user` ;

       3.Create Databse in PHP Script

before you create you need to connect to MySQL with user name and password 

Connecting to Database and Creating Database

<?php
$servername = "localhost";
$username = "username"; // user name 
$password = "password"; // your password

// Create connection$conn = mysqli_connect($servername, $username, $password);
// Check connectionif (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Create database$sql = "CREATE DATABASE myEmployee";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Creating table in side myEmployee database  

we need to select the carrasponding databse and create table use the following code 

this code create employee Table with 4 field (first name,second name,email, date )

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myEmployee";

// Create connection$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connectionif (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// sql to create table$sql = "CREATE TABLE employee (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if (mysqli_query($conn, $sql)) {
    echo "Table MyGuests created successfully";
} else {
    echo "Error creating table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Table Employee created 

Insert In to table 

<?php

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myEmployee";

// Create connection$conn = new mysqli($servername, $username, $password, $dbname);
// Check connectionif ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

fetch Data fro data base and out put in PHP page 

fetch the data from employee table and out put in PHP page

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myEmployee";

// Create connection$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connectionif (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM employee";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

Result 

Other Operations

DELETE

// sql to delete a record$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

UPDATE

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

LIMIT 

$sql = "SELECT * FROM Orders LIMIT 30";