PHP and MYSQL database Connection.
• MySQL is an open-source relational database management
system (RDBMS). It is the most popular database system used
with PHP.
• It uses Structured Query Language (SQL) to access the
database. The data in a MySQL database are stored in tables
that consist of columns and rows.
• PHP offers two primary methods to connect to MySQL:
1. MySQLi - MySQLi will only work with MySQL databases.
2. PDO - work on 12 different database systems. If you have to switch
your project to use another database, PDO makes the process easy.
Step 1: Open a Connection to MySQL
• A connection typically requires four pieces of information: the
server name, username, password, and database name.
• PHP provides mysql_connect() function to open a database
connection.
• This function takes a single parameter, which is a connection
returned by the mysql_connect() function.
• It can be done in two ways:
1. MySQLi Object-Oriented
2. MySQLi Procedural
1. MySQLi Object-Oriented
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Connection
$conn = new mysqli($servername,$username, $password);
// For checking if connection is successful or not
if ($conn->connect_error) {
die("Connection failed " . $conn->connect_error);
}
echo "Connected successfully";
?>
2. MySQLi Procedure-Oriented
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Connection
$conn = mysqli_connect($servername, $username, $password);
// Check if connection is Successful or not
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Step 2: Selecting a Database
• Once you have established a connection to the MySQL server,
you'll need to select a database to work with.
• You can do this using the mysqli_select_db() function.
• The function takes two parameters: the connection and the name
of the database.
$select_db = mysqli_select_db($conn, $db);
<?php
$server = "localhost";
$username = "your_username";
$password = "your_password";
$db = "your_database";
$conn = mysqli_connect($server, $username, $password); // Establish connection
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$select_db = mysqli_select_db($conn, $db); // Select database
echo "Database selected successfully";
?>
Step 3: Execute SQL Statement to Perform Database
Operation (Create/Insert/Update/delete/Display)
Once the SQL statement has been written, the next step is to execute
it. This is done by using the mysqli_query() function, which requires
the following parameters:
1. The connection to the database
2. The SQL statement to be executed
$sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3')";
mysqli_query($conn, $sql))
$sql = "INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')";
if (mysqli_query($conn, $sql))
{
echo "New record created successfully";
}
else
{
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
Step 4: Close the connection
• Once the data has been inserted into the database, it is important
to close the connection to the database.
• This is done using the mysqli_close() function, which requires the
following parameter:
1. The connection to the database
mysqli_close($conn);
Write a php code to insert row in following table
Person
Id firstname email
1023 Ana ana@[Link]
1026 Sam sam@[Link]
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO Person (firstname, lastname, email) VALUES ('John', 'Doe', 'john@[Link]')";
if (mysqli_query($conn, $sql))
{
echo "New record inserted successfully";
}
else
{
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT * FROM Person";
$result = mysqli_query($conn, $sql); // Execute the SQL query
// Process the result set
let row=mysqli_num_rows($result);
if (row > 0)
{
while($row = mysqli_fetch_assoc($result))
{
echo "id: " . $row["id"].;
echo " FirstName: " . $row["firstname"];
echo “LastName:”.$row["lastname"]. "<br>";
}
}
else
{
echo "0 results";
}
mysqli_close($conn);
Write a PHP program to create a login form and validate user
credentials using a MySQL database .
<!DOCTYPE html> [Link]
<html>
<head>
<title>Login Form</title>
</head>
<body>
<h2>Login Form</h2>
<form method="post" action=”[Link]”>
Username: <input type="text" name="username" required><br><br>
Password: <input type="password" name="password" required><br><br>
<input type="submit" name="login" value="Login">
</form>
</body>
</html>
[Link]
<?php
$host = "localhost";
$user = "root";
$pass = "123";
$dbname = "mydb";
$conn = mysqli_connect($host, $user, $pass, $dbname);
if (isset($_POST['login'])) //check if button pressed
{
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM Person WHERE username='$username' AND password='$password'";
$result = mysqli_query($conn, $query);
let row= mysqli_num_rows($result);
if (row > 0)
{
echo "<h3>Login Successful</h3>";
}
else
{
echo "<h3>Invalid Username or Password</h3>";
}
}
?>
Write a PHP program to create a signup form /Registration Form
and store user details in a MySQL database using procedural
mysqli.
<!DOCTYPE html> [Link]
<html>
<head>
<title>Signup Form</title>
</head>
<body>
<h2>Signup Form</h2>
<form method="post" action=”[Link]”>
Username: <input type="text" name="username" required><br><br>
Password: <input type="password" name="password" required><br><br>
<input type="submit" name="signup" value="Signup">
</form>
</body>
</html>
[Link]
<?php
$host = "localhost";
$user = "root";
$pass = "123";
$dbname = "mydb";
$conn = mysqli_connect($host, $user, $pass, $dbname);
if (isset($_POST['signup'])) //check if button pressed
{
$username = $_POST['username'];
$password = $_POST['password'];
$query = "INSERT INTO users (username, password) VALUES ('$username', '$password')";
if (mysqli_query($conn, $query))
{
echo "<h3>Registration Successful</h3>";
}
else
{
echo "<h3>Error: " . mysqli_error($conn) . "</h3>";
}
}
?>