CODE:
<html>
<body>
<?php
$host = "localhost";
$user = "root";
$password = "";
$link = new mysqli($host,$user,$password);
if(!$link){
die("Connection Failed: " .mysqli_connect_error());
}
echo "Connected Successfully";
echo "Program executed by Rejina Bhattarai";
?>
</body>
</html>
OUTPUT:
1. Create database
CODE:
<!DOCTYPE html>
<html>
<head> </head>
<body>
<?php
include "connect_server.php";
//create database
$sql = "CREATE DATABASE `group`";
//check sql code
if($link -> query($sql)===TRUE){
echo"<br> Database created succefully";
}
else{
echo " <br> Error creating Database: " .$link -> error;
}
?>
</body>
</html>
OUTPUT:
2. Create table
CODE:
<html>
<body>
<?php
include"connect_server.php";
//conneting to database
$sql = "USE `group`";
if($link -> query($sql) === TRUE){
echo"database 'group' selected successfully<br>";
}else{
echo "error selecting database: " .$link -> error;
}
//create table
$sql = "CREATE TABLE IF NOT EXISTS students(
id INT(7) AUTO_INCREMENT PRIMARY KEY,
Firstname VARCHAR(255) NOT NULL,
Lastname VARCHAR(255) NOT NULL,
Email VARCHAR(50)
)";
if($link -> query($sql) === TRUE){
echo"<br>table created successfully";
}else{
echo "error creating table " .$link -> error;
}
?>
</body>
</html>
OUTPUT:
3. Insert data from form
HTML CODE:
<HTML>
<body>
<form action="insert-data.php" method="POST">
<table>
<tr>
<td>Firstname: </td><td><input type="text" name="fname"></td>
</tr>
<tr> <td>lastname:</td> <td><input type="text" name="lname"></td></tr>
<tr> <td>Email:</td> <td><input type="text" name="email"></td></tr>
</table>
<input type="submit" value="submit">
</form>
</body>
</HTML>
OUTPUT:
Insert-data.php
<?php
include ("connect_server.php");
$sql1 = "USE `group`";
if ($link -> query($sql1) == TRUE) {
echo "database connected successfully<br>";
}
$fname = $_POST['fname'];
$lname = $_POST ['lname'];
$email = $_POST['email'];
if ($fname == NULL || $lname == NULL || $email == NULL)
{
echo"the fields are required to be filled.<br>";
}
$sql2 = "INSERT INTO students(Firstname, Lastname, Email)
VALUES ('$fname', '$lname', '$email');";
if ($link-> query($sql2) === TRUE)
{
echo ("records inserted successfully. <br>");
}
else
{
echo("error in inserting data: ".$link -> error);
}
$link -> close();
4. Display data in browser
<?php
include ("connect_server.php");
$sql1 = "USE `group`";
if ($link -> query($sql1) == TRUE) {
echo "database connected successfully<br>";
}
$sql = "SELECT * FROM `students`";
if ( $link -> query($sql) == FALSE) {
die ("no results found");
}
$result = $link-> query($sql);
if ($result->num_rows > 0)
{
echo "<table border ='1'>
<tr> <th>id</th>
<th> Firstname</th>
<th>Lastname</th> <th>Email</th></tr>";
while($rows = $result-> fetch_assoc())
{
echo "<tr>";
echo "<td>".$rows['id']."</td>";
echo "<td>".$rows['Firstname']."</td>";
echo "<td>".$rows['Lastname']."</td>";
echo "<td>".$rows['Email']."</td>";
echo "<tr>";
}
echo "</table>";
}
$link-> close();
?>
5. Update record through form
Form:
<html>
<body>
<?php
include "display-data.php";
?>
<form action="update1.php" method = "POST">
<h1> Enter the record to be updated</h1>
<table>
<tr>
<td> id </td> <td> <input type="text" name = "id"></td>
<td> Firstname</td> <td> <input type="text" name="fname"></td>
<td> Lastname</td> <td> <input type="text" name="lname"></td>
<td> Email</td> <td> <input type="text" name = "email"></td>
<td> <input type="submit" value = "Update" name="submit"></td>
</tr>
</table>
</form>
</body>
</html>
Update1.php
<?php
include "connect_server.php";
$sql = "USE `group`";
if ($link -> query ($sql) === TRUE)
{
echo "database selected.";
}
$id = $_POST['id'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$email = $_POST['email'];
$sql = "UPDATE students
set Firstname='$fname',
Lastname = '$lname',
Email = '$email' where id= $id;";
if ($link-> query($sql)=== FALSE)
{
echo "Error in updating: " .$link-> error;
}
else{
echo" <br>Updated successfully.";
}
include "display-data.php";
?>
6. Delete record
(I accidently deleted the previous table so I made a new one.)
<html>
<body>
<?php
include "display-data.php";
?>
<form action="" method="POST">
<table>
<tr><h3>enter the id of the record you want to delete</h3></tr>
<tr> <td>Id</td> <td><input type="text" name="id"></td>
<td><input type="submit" value="delete"></td></tr>
</table>
</form>
<?php
$host = "localhost";
$user = "root";
$password = "";
$dbname = "school";
$link = new mysqli($host, $user, $password, $dbname);
if (!$link)
{
die ("connection failed: " .mysqli_connect_error());
}
if($link -> query($sql) === false) {
echo "Error connecting database.". $conn -> error;
}
$id = $_POST['id'];
$query = "DELETE FROM `students` where id = $id;";
if($link -> query($query) === false) {
echo "error in deleting records". $link -> error;
}
?>
</body>
</html>
7. List out function of mysqli , it’s syntax and use in above programs.
● Database Connection: Establish a connection to a MySQL database using
mysqli_connect().
● Executing Queries: Run SQL queries with functions like mysqli_query() or use
mysqli_prepare() for secure prepared statements.
● Fetching Data: Retrieve query results using methods such as mysqli_fetch_assoc(),
mysqli_fetch_array(), or mysqli_fetch_object().
● Prepared Statements: Handle user input securely to protect against SQL injection.
● Error Handling: Identify and debug issues with mysqli_error() or mysqli_errno().
● Object-Oriented and Procedural APIs: Offers both programming styles for added
flexibility.
Conclusion
In conclusion, the topics discussed highlight the fundamental steps for building dynamic,
data-driven web applications with PHP and MySQL. These include connecting to a server,
creating databases and tables, inserting data via forms, displaying it in a browser, and performing
updates or deletions. The MySQLi extension in PHP streamlines these processes with features
like prepared statements, robust error handling, and versatile data-fetching methods. Gaining
proficiency in these essential techniques establishes a solid foundation for developing interactive
and efficient web applications.
