PHP7 Tutorial | Part 3 – MySQL and PHP

(MySQL and PHP – MySQLi Object-Oriented)
How to Connect MySQL and PHP. in the post you’ll learn How to create a MySQL connection using PHP (MySQLi Object-Oriented) and MySQLi extension.

What is HTML?

HTML (HyperText Markup Language) is developed by Tim Berners-Lee. An HTML document which can be displayed through a web browser is called a web page. HTML is not a computer language it’s a markup language.

For more information: HTML Tutorial : Introduction for beginners (Basic)

What is PHP?

PHP: Hypertext Preprocessor is a general purpose server side script language. it designed for web development. It designed by Rasmus Lerdorf in 1994 and first released in 1995. PHP is free to download and use. It can contain text, HTML, JavaScript, CSS and PHP code. it runs on Windows, Linux, Mac OS and etc… PHP can do many things such as generate dynamic web pages, manage data in your database, control user access, etc… PHP 7 is much faster than the previous versions. In this tutorial. You will learn to install and run PHP on Windows pc.

For more information
PHP 7 Tutorial: Introduction for Beginners (Basic)
PHP7 Tutorial | Part 2 – GET, POST and Cookies

What is SQL?

SQL (Structured Query Language) is a standard language for storing, managing and retrieving data in databases. SQL was developed at IBM by Donald Chamberlin and Raymond Boyce. and first released in 1991. SQL became a standard of the ANSI (American National Standards Institute) in 1986.

For more information: SQL Tutorial: Introduction for beginners (Basic)

How to Connect MySQL and PHP

in this tutorial we use MySQLi extension.

Open a MySQL Connection

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";

$con = new mysqli($serverName, $userName, $password); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

echo "Connected successfully!";

//Code

$con->close(); //closeTheConnection
?>

Create a MySQL Database

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";

$con = new mysqli($serverName, $userName, $password); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//createDatabase
$sql = "CREATE DATABASE testdata";
if ($con->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error: " . $con->error;
}

$con->close(); //closeTheConnection
?>

Create MySQL Tables

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//createTable
$sql = "CREATE TABLE students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
school VARCHAR(30) NOT NULL,
dob VARCHAR(10),
created_date TIMESTAMP
)";

if ($con->query($sql) === TRUE) {
    echo "Table created successfully";
} else {
    echo "Error: " . $con->error;
}


$con->close(); //closeTheConnection
?>

Insert Data Into MySQL

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}
//insertData
$sql = "INSERT INTO students (fname, lname, school, dob) VALUES ('First Name', 'Last Name', 'School', 'Date of Birth')";

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

$con->close(); //closeTheConnection
?>

If a column is AUTO_INCREMENT or TIMESTAMP. MySQL will automatically add the values for these columns.

Insert Multiple Records Into MySQL

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//insertMultipleData
$sql = "INSERT INTO students (fname, lname, school, dob) VALUES ('George', 'Doe', 'Dharmaraja College', '1995-12-21');";
$sql .= "INSERT INTO students (fname, lname, school, dob) VALUES ('Jhon', 'Row', 'New College', '1996-1-11');";
$sql .= "INSERT INTO students (fname, lname, school, dob) VALUES ('Hence', 'Ruthy', 'C College', '2000-2-1');";
$sql .= "INSERT INTO students (fname, lname, school, dob) VALUES ('Shehan', 'Doe', 'Dharmaraja College', '1995-12-21');";

if ($con->multi_query($sql) === TRUE) {
    echo "All records created successfully";
} else {
    echo "Error: " . $con->error;
}

$con->close(); //closeTheConnection
?>

Insert and Prepared Statements

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//prepare
$sql = "INSERT INTO students (fname, lname, school, dob) VALUES (?, ?, ?, ?)";
$stmt = $con->prepare($sql);
$stmt->bind_param("ssss", $fname, $lname, $school, $dob);
//"ssss" = Strin, String String, String

//setVariables
$fname = 'George';
$lname = 'Doe';
$school = 'Dharmaraja College';
$dob = '1995-12-21';

if ($stmt->execute() === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $stmt->error;
}



$stmt->close();
$con->close(); //closeTheConnection
?>

Select Data From MySQL

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//selectDataFromMySQL
$sql = "SELECT id, fname, school FROM students";
$output = $con->query($sql);

if ($output ->num_rows > 0) {
    while($row = $output->fetch_assoc()) {
        //getOutput
        $id = $row["id"];
        $fname = $row["fname"];
        $school = $row["school"];

        echo "Id: " . $id . "<br>";
        echo "Name: " . $fname . "<br>";
        echo "School: " . $school . "<br><br>";
    }
} else {
    echo "No results";
}

$con->close(); //closeTheConnection
?>

Select All Data From MySQL

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//selectDataFromMySQL
$sql = "SELECT * FROM students";
$output = $con->query($sql);

if ($output ->num_rows > 0) {
    while($row = $output->fetch_assoc()) {
        //getOutput
        echo "id: " . $row["id"] . "<br>";
        echo "Name: " . $row["fname"] . " " . $row["lname"]. "<br>";
        echo "School: " . $row["school"] . "<br>";
        echo "Date of Birth: " . $row["dob"] . "<br>";
    }
} else {
    echo "No results";
}

$con->close(); //closeTheConnection
?>

Select Data From MySQL (WHERE Clause)

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//selectDataFromMySQL
$sql = "SELECT * from students where fname='George'";
$output = $con->query($sql);

if ($output ->num_rows > 0) {
    while($row = $output->fetch_assoc()) {
        //getOutput
        $id = $row["id"];
        $fname = $row["fname"];
        $school = $row["school"];

        echo "id: " . $id . "<br>";
        echo "Name: " . $fname . "<br>";
        echo "School: " . $school . "<br>";
    }
} else {
    echo "No results";
}

$con->close(); //closeTheConnection
?>

Select Data From MySQL (LIKE Clause)

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//selectDataFromMySQL
$sql = "SELECT * FROM students WHERE school LIKE '%Dha%'";
$output = $con->query($sql);

if ($output ->num_rows > 0) {
    while($row = $output->fetch_assoc()) {
        //getOutput
        $id = $row["id"];
        $fname = $row["fname"];
        $school = $row["school"];

        echo "id: " . $id . "<br>";
        echo "Name: " . $fname . "<br>";
        echo "School: " . $school . "<br>";
    }
} else {
    echo "No results";
}

$con->close(); //closeTheConnection
?>

Update Data in MySQL

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//updateDataInMySQL 
$sql = "UPDATE students SET fname='Updated', lname='New' WHERE id=1";

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

$con->close(); //closeTheConnection
?>

Delete Data From MySQL

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//deleteDataFromMySQL 
$sql = "DELETE FROM students WHERE id=4";

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

$con->close(); //closeTheConnection
?>

Drop Table

MySQLi Object-Oriented

<?php
$serverName = "localhost";
$userName = "userName";
$password = "password";
$dbName = "testdata";

$con = new mysqli($serverName, $userName, $password, $dbName); //createConnection

//checkConnection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

//deleteDataFromMySQL 
$sql = "DROP TABLE students";
         
if($con->query($sql) === TRUE) {  
    echo "Table is deleted successfully";  
} else {  
    echo "Error: " . $con->error;
}  

$con->close(); //closeTheConnection
?>

Sources : 
https://pixabay.com/ (images)
https://pngimg.com (images)
ICT Books (Details)

Hello Guys,

Hope this post (How to Connect MySQL and PHP) will help you to learn something, If you enjoy my work then please share my posts with your friends and anyone who might be interested in programming and don’t forget to subscribe my mailing list.

If you had any problem with this post, please do mention it in the comment section.

Like ctechf Facebook fan page : https://www.facebook.com/ctechf

Also Read :

• HTML Tutorial | Introduction for Beginners
• How to install Ubuntu on Windows 10
• JavaSctipt Tutorial | Introduction for Beginners
• Python Tutorial | Introduction for Beginners.
• Python Tutorial | Introduction for Beginners (Part 2).
• Python Tutorial | How to Connect MySQL Database in Python
• Python Tutorial: Python GUI Programming (Tkinter)

How to be a Programmer

• How to be a Programmer | Part 1: Introduction
• How to be a Programmer | Part 2: Top 20 Programming Languages
• How to be a Programmer | Part 3: Basic Concept of Programming
• How to be a Programmer | Part 4: Control Structures
• How to be a Programmer | Part 5: Number Systems
• How to be a Programmer | Part 6: Coding Systems in Computer
• How to be a Programmer | Part7: Logic Gates and Boolean Algebra