Scribd
Upload
2 views68 pages

Web Development Using Open Source Program Op

Uploaded by

Premkumar Venkatesan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views68 pages

Web Development Using Open Source Program Op

Uploaded by

Premkumar Venkatesan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

CREATING A SIMPLE WEBPAGE USING PHP

<html>
<head>
<title>My First PHP Webpage</title>
</head>
<body>
<?php
echo "Hello, World!";
?>
</body>
</html>
2. USE OF DIFFERENT CONDITIONAL STATEMENTS IN PHP

// If Statement
<br>
<?php
$x = 5;
if ($x > 10) {
echo "x is greater than 10";
} else {
echo "x is less than or equal to 10";
}
?>
<br><br>

// If-Else Statement
<br>
<?php
$x = 5;
if ($x > 10) {
echo "x is greater than 10";
} elseif ($x == 5) {
echo "x is equal to 5";
} else {
echo "x is less than 5";
}
?>
<br><br>

//Switch Statement
<br>
<?php
$x = 2;
switch ($x) {
case 1:
echo "x is equal to 1";
break;
case 2:
echo "x is equal to 2";
break;
default:
echo "x is not equal to 1 or 2";
}
?>
<br><br>

//Ternary Operator
<br>
<?php
$x = 5;
echo ($x > 10) ? "x is greater than 10" : "x is less than or equal to 10";
?>
<br><br>

//Nested If Statement
<br>
<?php
$x = 5;
$y = 3;
if ($x > 10) {
if ($y > 2) {
echo "x is greater than 10 and y is greater than 2";
} else {
echo "x is greater than 10 but y is less than or equal to 2";
}
} else {
echo "x is less than or equal to 10";
}
?>
3. WRITE A PHP PROGRAM USE OF LOOPING STATEMENT

<?php
// Use a for loop to print numbers from 1 to 10
echo "For Loop:<br>";
for ($i = 1; $i <= 10; $i++) {
echo "$i<br>";
}

// Use a while loop to print numbers from 1 to 10


echo "<br>While Loop:<br>";
$i = 1;
while ($i <= 10) {
echo "$i<br>";
$i++;
}

// Use a do-while loop to print numbers from 1 to 10


echo "<br>Do-While Loop:<br>";
$i = 1;
do {
echo "$i<br>";
$i++;
} while ($i <= 10);

// Use a foreach loop to print elements of an array


echo "<br>Fore Each Loop:<br>";
$fruits = array("Apple", "Banana", "Cherry");
foreach ($fruits as $fruit) {
echo "$fruit<br>";
}

?>
4. WRITE A PHP PROGRAM CREATING DIFFERENT TYPE OF ARRAY

<?php
// Indexed Array
$indexedArray = array("Apple", "Banana", "Cherry");
echo "Indexed Array:<br>";
print_r($indexedArray);

// Associative Array
$associativeArray = array("fruit1" => "Apple", "fruit2" => "Banana", "fruit3" => "Cherry");
echo "<br>Associative Array:<br>";
print_r($associativeArray);

// Multidimensional Array
$multidimensionalArray = array(
"fruit1" => array("name" => "Apple", "color" => "Red"),
"fruit2" => array("name" => "Banana", "color" => "Yellow"),
"fruit3" => array("name" => "Cherry", "color" => "Red")
);
echo "<br>Multidimensional Array:<br>";
print_r($multidimensionalArray);

// Array with Mixed Data Types


$mixedArray = array("Apple", 123, true, array("name" => "John", "age" => 30));
echo "<br>Array with Mixed Data Types:<br>";
print_r($mixedArray);

// Array with Key-Value Pairs


$keyValueArray = array(
"name" => "John",
"age" => 30,
"city" => "New York"
);
echo "<br>Array with Key-Value Pairs:<br>";
print_r($keyValueArray);
?>
5. WRITE A PHP PROGRAM USE OF ARRAY FUNCTION

<?php
// Create an array
$fruits = array("Apple", "Banana", "Cherry", "Date", "Elderberry");

// Use array_count_values() to count the frequency of each value


$count = array_count_values($fruits);
echo "Count of each value:<br>";
print_r($count);

// Use array_sum() to calculate the sum of the array values


$numbers = array(10, 20, 30, 40, 50);
$sum = array_sum($numbers);
echo "<br>Sum of the array values: $sum<br>";

// Use array_merge() to merge two arrays


$array1 = array("a" => "Apple", "b" => "Banana");
$array2 = array("c" => "Cherry", "d" => "Date");
$mergedArray = array_merge($array1, $array2);
echo "Merged Array:<br>";
print_r($mergedArray);

// Use array_search() to search for a value in the array


$searchValue = "Banana";
$key = array_search($searchValue, $fruits);
echo "<br>Key of '$searchValue': $key<br>";

// Use array_push() to add a new value to the end of the array


array_push($fruits, "Fig");
echo "Updated Array:<br>";
print_r($fruits);

// Use array_pop() to remove the last value from the array


$lastValue = array_pop($fruits);
echo "<br>Last Value: $lastValue<br>";

// Use array_shift() to remove the first value from the array


$firstValue = array_shift($fruits);
echo "First Value: $firstValue<br>";
// Use array_unshift() to add a new value to the beginning of the array
array_unshift($fruits, "Apricot");
echo "Updated Array:<br>";
print_r($fruits);

// Use array_unique() to remove duplicate values from the array


$duplicateArray = array("Apple", "Banana", "Apple", "Cherry", "Banana");
$uniqueArray = array_unique($duplicateArray);
echo "Unique Array:<br>";
print_r($uniqueArray);

// Use array_values() to reset the array keys


$resetsArray = array_values($uniqueArray);
echo "Reset Array:<br>";
print_r($resetsArray);

// Use array_keys() to get the array keys


$keysArray = array_keys($uniqueArray);
echo "Keys Array:<br>";
print_r($keysArray);

// Use array_reverse() to reverse the array order


$reversedArray = array_reverse($uniqueArray);
echo "Reversed Array:<br>";
print_r($reversedArray);

// Use array_rand() to get a random key from the array


$randomKey = array_rand($uniqueArray);
echo "<br>Random Key: $randomKey<br>";

// Use array_slice() to get a subset of the array


$sliceArray = array_slice($uniqueArray, 1, 2);
echo "Slice Array:<br>";
print_r($sliceArray);
?>
6. WRITE A PHP PROGRAM CREATING USER DEFINE FUNCTION

<?php
// Function to calculate the area of a rectangle
function calculateArea($length, $width) {
return $length * $width;
}

// Function to calculate the perimeter of a rectangle


function calculatePerimeter($length, $width) {
return 2 * ($length + $width);
}

// Function to convert Celsius to Fahrenheit


function celsiusToFahrenheit($celsius) {
return ($celsius * 9/5) + 32;
}

// Function to convert Fahrenheit to Celsius


function fahrenheitToCelsius($fahrenheit) {
return ($fahrenheit - 32) * 5/9;
}

// Function to check if a number is even or odd


function isEven($number) {
if ($number % 2 == 0) {
return "Even";
} else {
return "Odd";
}
}

// Function to calculate the factorial of a number


function factorial($number) {
$result = 1;
for ($i = 1; $i <= $number; $i++) {
$result *= $i;
}
return $result;
}
// Test the functions
echo "Area of rectangle: " . calculateArea(10, 5) . "<br>";
echo "Perimeter of rectangle: " . calculatePerimeter(10, 5) . "<br>";
echo "Celsius to Fahrenheit: " . celsiusToFahrenheit(30) . "<br>";
echo "Fahrenheit to Celsius: " . fahrenheitToCelsius(86) . "<br>";
echo "Is 10 even or odd? " . isEven(10) . "<br>";
echo "Factorial of 5: " . factorial(5) . "<br>";
?>
7. WRITE A PHP PROGRAM CREATING OF FILE

<?php
// Specify the file name and path
$fileName = "[Link]";
$filePath = "files/";

// Create the file path if it does not exist


if (!is_dir($filePath)) {
mkdir($filePath, 0777, true);
}

// Create the file


$file = fopen($filePath . $fileName, "w");

// Check if the file was created successfully


if ($file) {
echo "File created successfully.";
} else {
echo "Error creating file.";
}

// Write to the file


fwrite($file, "Hello, World!");

// Close the file


fclose($file);

// Read from the file


$file = fopen($filePath . $fileName, "r");
if ($file) {
echo "<br>File contents: " . fread($file, filesize($filePath . $fileName));
fclose($file);
} else {
echo "Error reading file.";
}

// Delete the file


if (unlink($filePath . $fileName)) {
echo "<br>File deleted successfully.";
} else {
echo "Error deleting file.";
}

// Delete the file path


if (rmdir($filePath)) {
echo "<br>File path deleted successfully.";
} else {
echo "Error deleting file path.";
}
?>
8. WRITE A PHP PROGRAM USING FILE MANIPULATION

<?php
// Specify the file name and path
$fileName = "[Link]";
$filePath = "files/";

// Create the file path if it does not exist


if (!is_dir($filePath)) {
mkdir($filePath, 0777, true);
}

// Create the file


$file = fopen($filePath . $fileName, "w");
fwrite($file, "Hello, World!");
fclose($file);

// Copy the file


copy($filePath . $fileName, $filePath . "copy_" . $fileName);
echo "File copied successfully.<br>";

// Rename the file


rename($filePath . $fileName, $filePath . "renamed_" . $fileName);
echo "File renamed successfully.<br>";

// Delete the copied file


unlink($filePath . "copy_" . $fileName);
echo "Copied file deleted successfully.<br>";

// Check if the file exists


if (file_exists($filePath . "renamed_" . $fileName)) {
echo "File exists.<br>";
} else {
echo "File does not exist.<br>";
}

// Get the file size


$fileSize = filesize($filePath . "renamed_" . $fileName);
echo "File size: $fileSize bytes.<br>";
// Get the file type
$fileType = mime_content_type($filePath . "renamed_" . $fileName);
echo "File type: $fileType.<br>";

// Read the file contents


$fileContents = file_get_contents($filePath . "renamed_" . $fileName);
echo "File contents: $fileContents.<br>";

// Append to the file


$file = fopen($filePath . "renamed_" . $fileName, "a");
fwrite($file, " This is appended text.");
fclose($file);

// Read the updated file contents


$fileContents = file_get_contents($filePath . "renamed_" . $fileName);
echo "Updated file contents: $fileContents.<br>";
?>
9. WRITE A PHP PROGRAM CREATION OF SESSIONS

<?php
// Start the session
session_start();
// Check if the session is already set
if (isset($_SESSION['username'])) {
echo "Welcome, " . $_SESSION['username'] . "!<br>";
} else {
// Set the session variable
$_SESSION['username'] = 'JohnDoe';
echo "Session set. Welcome, " . $_SESSION['username'] . "!<br>";
}
// Store some data in the session
$_SESSION['email'] = 'johndoe@[Link]';
$_SESSION['age'] = 30;

// Retrieve the data from the session


echo "Email: " . $_SESSION['email'] . "<br>";
echo "Age: " . $_SESSION['age'] . "<br>";
// Unset the session variable
unset($_SESSION['email']);
// Check if the session variable is set
if (isset($_SESSION['email'])) {
echo "Email is set.<br>";
} else {
echo "Email is not set.<br>";
}

// Destroy the session


session_destroy();
echo "Session destroyed.<br>";

// Try to access a session variable after destroying the session


if (isset($_SESSION['username'])) {
echo "Username is set.<br>";
} else {
echo "Username is not set.<br>";
}
?>
10. WRITE A PHP PROGRAM CREATION OF COOKIES

<?php
// Set a cookie
$cookie_name = "username";
$cookie_value = "JohnDoe";
setcookie($cookie_name, $cookie_value, time() + (86400 * 30)); // 86400 = 1 day, 30 days
expiration

// Check if the cookie is set


if (isset($_COOKIE[$cookie_name])) {
echo "Cookie is set.<br>";
echo "Cookie value: " . $_COOKIE[$cookie_name] . "<br>";
} else {
echo "Cookie is not set.<br>";
}

// Update the cookie value


$cookie_value = "JaneDoe";
setcookie($cookie_name, $cookie_value, time() + (86400 * 30)); // 86400 = 1 day, 30 days
expiration

// Check if the cookie value has been updated


if (isset($_COOKIE[$cookie_name])) {
echo "Updated cookie value: " . $_COOKIE[$cookie_name] . "<br>";
} else {
echo "Cookie is not set.<br>";
}

// Delete the cookie


setcookie($cookie_name, "", time() - 3600); // empty value and expired time

// Check if the cookie has been deleted


if (isset($_COOKIE[$cookie_name])) {
echo "Cookie is still set.<br>";
} else {
echo "Cookie has been deleted.<br>";
}
?>
11. WRITE A PHP PROGRAM USING SIMPLE APPLICATION

<!-- [Link] -->

<!DOCTYPE html>
<html>
<head>
<title>Simple PHP Form Application</title>
</head>
<body>
<h1>Simple PHP Form Application</h1>
<form action="[Link]" method="post">
<label for="name">Enter your name:</label>
<input type="text" id="name" name="name"><br><br>
<input type="submit" value="Submit">
</form>
</body>
</html>

<!-- [Link] -->


<!DOCTYPE html>
<html>
<head>
<title>Greeting Message</title>
</head>
<body>
<h1>Greeting Message</h1>
<?php
// Check if the form has been submitted
if ($_SERVER["REQUEST_METHOD"] == "POST") {
// Get the user's name from the form input
$name = $_POST["name"];

// Display a greeting message


echo "Hello, $name!";
} else {
// Display an error message if the form has not been submitted
echo "Error: Form not submitted.";
}
?></body></html>
12. CREATING SIMPLE TABLE WITH CONSTRAINTS

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

$conn = new mysqli($servername, $username, $password, $dbname);

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

echo "Connected to the database successfully.<br>";

$sql = "CREATE TABLE IF NOT EXISTS users (


id INT(11) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {


echo "Table 'users' created successfully!<br>";
} else {
echo "Error creating table: " . $conn->error . "<br>";
}

$conn->close();
?>
13. INSERTION,UPDATION AND DELECTION OF ROW IN MUSQL TABLE

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Inserting a new record into the 'users' table


$sql_insert = "INSERT INTO users (username, email) VALUES ('john_doe',

'john2@[Link]')";
if ($conn->query($sql_insert) === TRUE) {
echo "New record created successfully!<br>";
} else {
echo "Error inserting record: " . $conn->error . "<br>";
}

// Updating a record in the 'users' table (e.g., change username for id=1)
$sql_update = "UPDATE users SET username='john_updated' WHERE id=1";
if ($conn->query($sql_update) === TRUE) {
echo "Record updated successfully!<br>";
} else {
echo "Error updating record: " . $conn->error . "<br>";
}

// Deleting a record from the 'users' table (e.g., delete record where id=1)
$sql_delete = "DELETE FROM users WHERE id=1";
if ($conn->query($sql_delete) === TRUE) {
echo "Record deleted successfully!<br>";
} else {
echo "Error deleting record: " . $conn->error . "<br>";
}

// Displaying all records from the 'users' table


$sql_select = "SELECT id, username, email, created_at FROM users";
$result = $conn->query($sql_select);

if ($result->num_rows > 0) {
echo "<h2>Users Table:</h2>";
echo "<table

border='1'><tr><th>ID</th><th>Username</th><th>Email</th><th>Created

At</th></tr>";

// Output data of each row


while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["id"] . "</td><td>" . $row["username"] .

"</td><td>" . $row["email"] . "</td><td>" . $row["created_at"] . "</td></tr>";


}
echo "</table>";
} else {
echo "0 results<br>";
}

// Close the connection


$conn->close();
?>
[Link] OF DATA BY DIFFERENT CRITERIA

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Handle search form submission


$search_query = "";
if (isset($_POST['search'])) {
// Get search input
$search_term = $_POST['search_term'];
$search_by = $_POST['search_by'];

// Build search query based on user selection


if ($search_by == 'username') {
$search_query = "WHERE username LIKE '%$search_term%'";
} elseif ($search_by == 'email') {
$search_query = "WHERE email LIKE '%$search_term%'";
} elseif ($search_by == 'id') {
$search_query = "WHERE id = $search_term";
}
}

// Insert a new record (as before)


$sql_insert = "INSERT INTO users (username, email) VALUES ('john_doe',

'john4@[Link]')";
if ($conn->query($sql_insert) === TRUE) {
echo "New record created successfully!<br>";
} else {
echo "Error inserting record: " . $conn->error . "<br>";
}

// Update a record (as before)


$sql_update = "UPDATE users SET username='john_updated' WHERE id=1";
if ($conn->query($sql_update) === TRUE) {
echo "Record updated successfully!<br>";
} else {
echo "Error updating record: " . $conn->error . "<br>";
}

// Delete a record (as before)


$sql_delete = "DELETE FROM users WHERE id=1";
if ($conn->query($sql_delete) === TRUE) {
echo "Record deleted successfully!<br>";
} else {
echo "Error deleting record: " . $conn->error . "<br>";
}

// Search query: SELECT with dynamic search


$sql_select = "SELECT id, username, email, created_at FROM users

$search_query";
$result = $conn->query($sql_select);

echo "<h2>Search Users:</h2>";

// Display search form


echo '<form method="POST" action="">
<label for="search_term">Search Term:</label>
<input type="text" name="search_term" id="search_term" required>
<label for="search_by">Search By:</label>
<select name="search_by" id="search_by">
<option value="username">Username</option>
<option value="email">Email</option>
<option value="id">ID</option>
</select>
<button type="submit">Search</button>
</form>';
echo "<h2>Users Table:</h2>";

if ($result->num_rows > 0) {
echo "<table

border='1'><tr><th>ID</th><th>Username</th><th>Email</th><th>Created

At</th></tr>";

// Output data of each row


while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["id"] . "</td><td>" . $row["username"] .

"</td><td>" . $row["email"] . "</td><td>" . $row["created_at"] . "</td></tr>";


}
echo "</table>";
} else {
echo "0 results found based on your search criteria.<br>";
}

// Close the connection


$conn->close();
?>
[Link] OF DATA

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Handle search form submission


$search_query = "";
$sort_query = "";
$order_by = "ASC"; // Default order (ascending)

// Check if search is submitted


if (isset($_POST['search'])) {
// Get search input
$search_term = $_POST['search_term'];
$search_by = $_POST['search_by'];

// Build search query based on user selection


if ($search_by == 'username') {
$search_query = "WHERE username LIKE '%$search_term%'";
} elseif ($search_by == 'email') {
$search_query = "WHERE email LIKE '%$search_term%'";
} elseif ($search_by == 'id') {
$search_query = "WHERE id = $search_term";
}
}

// Handle sorting form submission


if (isset($_POST['sort_by']) && isset($_POST['sort_order'])) {
$sort_by = $_POST['sort_by'];
$order_by = $_POST['sort_order']; // ASC or DESC

$sort_query = "ORDER BY $sort_by $order_by";


}

// Insert a new record (as before)


$sql_insert = "INSERT INTO users (username, email) VALUES ('john_doe',

'john5@[Link]')";
if ($conn->query($sql_insert) === TRUE) {
echo "New record created successfully!<br>";
} else {
echo "Error inserting record: " . $conn->error . "<br>";
}

// Update a record (as before)


$sql_update = "UPDATE users SET username='john_updated' WHERE id=1";
if ($conn->query($sql_update) === TRUE) {
echo "Record updated successfully!<br>";
} else {
echo "Error updating record: " . $conn->error . "<br>";
}

// Delete a record (as before)


$sql_delete = "DELETE FROM users WHERE id=1";
if ($conn->query($sql_delete) === TRUE) {
echo "Record deleted successfully!<br>";
} else {
echo "Error deleting record: " . $conn->error . "<br>";
}

// Search query: SELECT with dynamic search and sorting


$sql_select = "SELECT id, username, email, created_at FROM users

$search_query $sort_query";
$result = $conn->query($sql_select);

echo "<h2>Search and Sort Users:</h2>";

// Display search form


echo '<form method="POST" action="">
<label for="search_term">Search Term:</label>
<input type="text" name="search_term" id="search_term" required>
<label for="search_by">Search By:</label>
<select name="search_by" id="search_by">
<option value="username">Username</option>
<option value="email">Email</option>
<option value="id">ID</option>
</select>
<button type="submit" name="search">Search</button>
</form>';

echo "<h2>Sort Users:</h2>";

// Display sorting form


echo '<form method="POST" action="">
<label for="sort_by">Sort By:</label>
<select name="sort_by" id="sort_by">
<option value="id">ID</option>
<option value="username">Username</option>
<option value="email">Email</option>
</select>
<label for="sort_order">Order:</label>
<select name="sort_order" id="sort_order">
<option value="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>
<button type="submit" name="sort_by">Sort</button>
</form>';

echo "<h2>Users Table:</h2>";

if ($result->num_rows > 0) {
echo "<table

border='1'><tr><th>ID</th><th>Username</th><th>Email</th><th>Created

At</th></tr>";

// Output data of each row


while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["id"] . "</td><td>" . $row["username"] .

"</td><td>" . $row["email"] . "</td><td>" . $row["created_at"] . "</td></tr>";


}
echo "</table>";
} else {
echo "0 results found based on your search and sorting criteria.<br>";
}

// Close the connection


$conn->close();
?>
[Link] OF JOINING TABLES

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Handle search form submission


$search_query = "";
$sort_query = "";
$order_by = "ASC"; // Default order (ascending)

// Check if search is submitted


if (isset($_POST['search'])) {
// Get search input
$search_term = $_POST['search_term'];
$search_by = $_POST['search_by'];

// Build search query based on user selection


if ($search_by == 'username') {
$search_query = "WHERE [Link] LIKE '%$search_term%'";
} elseif ($search_by == 'email') {
$search_query = "WHERE [Link] LIKE '%$search_term%'";
} elseif ($search_by == 'id') {
$search_query = "WHERE [Link] = $search_term";
}
}

// Handle sorting form submission


if (isset($_POST['sort_by']) && isset($_POST['sort_order'])) {
$sort_by = $_POST['sort_by'];
$order_by = $_POST['sort_order']; // ASC or DESC

$sort_query = "ORDER BY u.$sort_by $order_by";


}

// Search query: SELECT with dynamic search and sorting


$sql_select = "
SELECT [Link], [Link], [Link], u.created_at, [Link] AS post_id, [Link], [Link],
p.created_at AS post_created_at
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id
$search_query
$sort_query
";

$result = $conn->query($sql_select);

echo "<h2>Search, Sort, and Join Users with Posts:</h2>";

// Display search form


echo '<form method="POST" action="">
<label for="search_term">Search Term:</label>
<input type="text" name="search_term" id="search_term" required>
<label for="search_by">Search By:</label>
<select name="search_by" id="search_by">
<option value="username">Username</option>
<option value="email">Email</option>
<option value="id">ID</option>
</select>
<button type="submit" name="search">Search</button>
</form>';

echo "<h2>Sort Users:</h2>";

// Display sorting form


echo '<form method="POST" action="">
<label for="sort_by">Sort By:</label>
<select name="sort_by" id="sort_by">
<option value="id">ID</option>
<option value="username">Username</option>
<option value="email">Email</option>
</select>
<label for="sort_order">Order:</label>
<select name="sort_order" id="sort_order">
<option value="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>
<button type="submit" name="sort_by">Sort</button>
</form>';

echo "<h2>Users and Their Posts:</h2>";

if ($result->num_rows > 0) {
echo "<table border='1'>
<tr><th>ID</th><th>Username</th><th>Email</th><th>Created At</th><th>Post
ID</th><th>Post Title</th><th>Post Content</th><th>Post Created At</th></tr>";

// Output data of each row


while($row = $result->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"] . "</td>
<td>" . $row["username"] . "</td>
<td>" . $row["email"] . "</td>
<td>" . $row["created_at"] . "</td>
<td>" . $row["post_id"] . "</td>
<td>" . $row["title"] . "</td>
<td>" . $row["content"] . "</td>
<td>" . $row["post_created_at"] . "</td>
</tr>";
}
echo "</table>";
} else {
echo "0 results found based on your search and sorting criteria.<br>";
}

// Close the connection


$conn->close();
?>
17. USAGE OF SUB QUERIES
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Handle search form submission


$search_query = "";
$sort_query = "";
$order_by = "ASC"; // Default order (ascending)

// Check if search is submitted


if (isset($_POST['search'])) {
// Get search input
$search_term = $_POST['search_term'];
$search_by = $_POST['search_by'];

// Build search query based on user selection


if ($search_by == 'username') {
$search_query = "WHERE [Link] LIKE '%$search_term%'";
} elseif ($search_by == 'email') {
$search_query = "WHERE [Link] LIKE '%$search_term%'";
} elseif ($search_by == 'id') {
$search_query = "WHERE [Link] = $search_term";
}
}

// Handle sorting form submission


if (isset($_POST['sort_by']) && isset($_POST['sort_order'])) {
$sort_by = $_POST['sort_by'];
$order_by = $_POST['sort_order']; // ASC or DESC
$sort_query = "ORDER BY u.$sort_by $order_by";
}

// Search query: SELECT with dynamic search, sorting, and subqueries


$sql_select = "
SELECT [Link], [Link], [Link], u.created_at,
(SELECT COUNT(*) FROM posts p WHERE p.user_id = [Link]) AS post_count,
(SELECT title FROM posts p WHERE p.user_id = [Link] ORDER BY p.created_at DESC
LIMIT 1) AS latest_post_title,
(SELECT content FROM posts p WHERE p.user_id = [Link] ORDER BY p.created_at
DESC LIMIT 1) AS latest_post_content
FROM users u
$search_query
$sort_query
";

$result = $conn->query($sql_select);

echo "<h2>Search, Sort, and Join Users with Posts (Including Subqueries):</h2>";

// Display search form


echo '<form method="POST" action="">
<label for="search_term">Search Term:</label>
<input type="text" name="search_term" id="search_term" required>
<label for="search_by">Search By:</label>
<select name="search_by" id="search_by">
<option value="username">Username</option>
<option value="email">Email</option>
<option value="id">ID</option>
</select>
<button type="submit" name="search">Search</button>
</form>';

echo "<h2>Sort Users:</h2>";

// Display sorting form


echo '<form method="POST" action="">
<label for="sort_by">Sort By:</label>
<select name="sort_by" id="sort_by">
<option value="id">ID</option>
<option value="username">Username</option>
<option value="email">Email</option>
</select>
<label for="sort_order">Order:</label>
<select name="sort_order" id="sort_order">
<option value="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>
<button type="submit" name="sort_by">Sort</button>
</form>';

echo "<h2>Users and Their Post Counts and Latest Post:</h2>";

if ($result->num_rows > 0) {
echo "<table border='1'>
<tr>
<th>ID</th><th>Username</th><th>Email</th><th>Created At</th>
<th>Post Count</th><th>Latest Post Title</th><th>Latest Post Content</th>
</tr>";

// Output data of each row


while($row = $result->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"] . "</td>
<td>" . $row["username"] . "</td>
<td>" . $row["email"] . "</td>
<td>" . $row["created_at"] . "</td>
<td>" . $row["post_count"] . "</td>
<td>" . $row["latest_post_title"] . "</td>
<td>" . $row["latest_post_content"] . "</td>
</tr>";
}
echo "</table>";
} else {
echo "0 results found based on your search and sorting criteria.<br>";
}
// Close the connection
$conn->close();
?>
18. USAGE OF AGGREGATE FUNCTION

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Aggregate functions query


$sql_aggregate = "
SELECT
(SELECT COUNT(*) FROM users) AS total_users,
(SELECT COUNT(*) FROM posts) AS total_posts,
(SELECT AVG(post_count) FROM (SELECT COUNT(*) AS post_count FROM posts
GROUP BY user_id) AS post_avg) AS avg_posts_per_user,
(SELECT username FROM users u LEFT JOIN posts p ON [Link] = p.user_id GROUP BY
[Link] ORDER BY COUNT([Link]) DESC LIMIT 1) AS user_with_most_posts,
(SELECT username FROM users ORDER BY created_at ASC LIMIT 1) AS oldest_user,
(SELECT username FROM users ORDER BY created_at DESC LIMIT 1) AS newest_user
";

$result = $conn->query($sql_aggregate);

echo "<h2>Aggregate Information:</h2>";

if ($result->num_rows > 0) {
$row = $result->fetch_assoc();

echo "<table border='1'>


<tr>
<th>Total Users</th>
<th>Total Posts</th>
<th>Average Posts Per User</th>
<th>User with Most Posts</th>
<th>Oldest User</th>
<th>Newest User</th>
</tr>
<tr>
<td>" . $row["total_users"] . "</td>
<td>" . $row["total_posts"] . "</td>
<td>" . number_format($row["avg_posts_per_user"], 2) . "</td>
<td>" . $row["user_with_most_posts"] . "</td>
<td>" . $row["oldest_user"] . "</td>
<td>" . $row["newest_user"] . "</td>
</tr>
</table>";
} else {
echo "No data available.<br>";
}

// Close the connection


$conn->close();
?>
19. WORKING WITH SET OPERATION

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Union query: Combine users and posts (excluding duplicates)


$sql_union = "
(SELECT id, username, 'user' AS type FROM users)
UNION
(SELECT user_id, title AS username, 'post' AS type FROM posts)
";

// Union All query: Combine users and posts (including duplicates)


$sql_union_all = "
(SELECT id, username, 'user' AS type FROM users)
UNION ALL
(SELECT user_id, title AS username, 'post' AS type FROM posts)
";

// Intersect query: Find users who have made posts (users present in both tables)
$sql_intersect = "
(SELECT id, username FROM users)
INTERSECT
(SELECT user_id, title FROM posts)
";

// Except query: Find users who have not made any posts (users present in the users table but not
in posts)
$sql_except = "
(SELECT id, username FROM users)
EXCEPT
(SELECT user_id, title FROM posts)
";

// Execute the Union query


echo "<h2>Union Query (Users and Posts, no duplicates):</h2>";
$result_union = $conn->query($sql_union);

if ($result_union->num_rows > 0) {
echo "<table border='1'>
<tr>
<th>ID</th><th>Username</th><th>Type</th>
</tr>";
while($row = $result_union->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"] . "</td>
<td>" . $row["username"] . "</td>
<td>" . $row["type"] . "</td>
</tr>";
}
echo "</table>";
} else {
echo "No results found for Union query.<br>";
}

// Execute the Union All query


echo "<h2>Union All Query (Users and Posts, including duplicates):</h2>";
$result_union_all = $conn->query($sql_union_all);

if ($result_union_all->num_rows > 0) {
echo "<table border='1'>
<tr>
<th>ID</th><th>Username</th><th>Type</th>
</tr>";
while($row = $result_union_all->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"] . "</td>
<td>" . $row["username"] . "</td>
<td>" . $row["type"] . "</td>
</tr>";
}
echo "</table>";
} else {
echo "No results found for Union All query.<br>";
}

// Execute the Intersect query


echo "<h2>Intersect Query (Users who have made posts):</h2>";
$result_intersect = $conn->query($sql_intersect);

if ($result_intersect->num_rows > 0) {
echo "<table border='1'>
<tr>
<th>ID</th><th>Username</th>
</tr>";
while($row = $result_intersect->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"] . "</td>
<td>" . $row["username"] . "</td>
</tr>";
}
echo "</table>";
} else {
echo "No results found for Intersect query.<br>";
}

// Execute the Except query


echo "<h2>Except Query (Users without posts):</h2>";
$result_except = $conn->query($sql_except);

if ($result_except->num_rows > 0) {
echo "<table border='1'>
<tr>
<th>ID</th><th>Username</th>
</tr>";
while($row = $result_except->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"] . "</td>
<td>" . $row["username"] . "</td>
</tr>";
}
echo "</table>";
} else {
echo "No results found for Except query.<br>";
}

// Close the connection


$conn->close();
?>
20. WORKING WITH STRING,NUMERIC AND DATE FUNCTION

<?php
// String Functions
$string = "Hello, world!";
echo "Original String: " . $string . "<br>";

// Length of string
echo "String Length: " . strlen($string) . "<br>";

// Convert to uppercase
echo "Uppercase: " . strtoupper($string) . "<br>";

// Convert to lowercase
echo "Lowercase: " . strtolower($string) . "<br>";

// Extract substring
echo "Substring (7, 5): " . substr($string, 7, 5) . "<br>";

// Find position of "world"


echo "Position of 'world': " . strpos($string, "world") . "<br>";

// Replace "world" with "PHP"


echo "Replaced String: " . str_replace("world", "PHP", $string) . "<br><br>";

// Numeric Functions
$number = -10.25;
echo "Original Number: " . $number . "<br>";

// Absolute value
echo "Absolute Value: " . abs($number) . "<br>";

// Round to nearest integer


echo "Rounded Value: " . round($number) . "<br>";

// Ceil (round up)


echo "Ceil Value: " . ceil($number) . "<br>";

// Floor (round down)


echo "Floor Value: " . floor($number) . "<br>";
// Random number between 1 and 100
echo "Random Number (1-100): " . rand(1, 100) . "<br>";

// Maximum of numbers
echo "Maximum Value: " . max(1, 2, 3, 4, 5) . "<br>";

// Minimum of numbers
echo "Minimum Value: " . min(1, 2, 3, 4, 5) . "<br><br>";

// Date Functions
echo "Current Date and Time: " . date("Y-m-d H:i:s") . "<br>";

// Current timestamp
echo "Current Timestamp: " . time() . "<br>";

// Convert string to timestamp


echo "Timestamp for '2025-01-31': " . strtotime("2025-01-31") . "<br>";

// Create a timestamp for a specific date and time


echo "Timestamp for '2025-01-31 14:30:00': " . mktime(14, 30, 0, 1, 31, 2025) . "<br>";

// Difference between two dates


$date1 = new DateTime("2025-01-01");
$date2 = new DateTime("2025-01-31");
$diff = $date1->diff($date2);
echo "Difference between dates: " . $diff->format("%a days") . "<br>";

// Create DateTime object


$date = date_create("2025-01-31");
echo "Created Date: " . $date->format("Y-m-d") . "<br>";

// Get date details for current time


echo "Current Date Details: <br>";
print_r(getdate(time()));

?>
21. WRITE A PHP PROGRAM USING DATABASE CONNECTIVITY WITH MYSQL

<?php
// Database configuration
$servername = "localhost"; // MySQL server address
$username = "root"; // MySQL username
$password = ""; // MySQL password
$dbname = "test_db"; // MySQL database name

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
// If connection fails
die("Connection failed: " . $conn->connect_error);
} else {
echo "<h2>Connected successfully to the database: " . $dbname . "</h2>";
}

// Display a list of tables in the database


echo "<h3>List of Tables in the Database:</h3>";
$sql_show_tables = "SHOW TABLES";
$result = $conn->query($sql_show_tables);

if ($result->num_rows > 0) {
echo "<ul>";
while ($row = $result->fetch_assoc()) {
$table_name = $row["Tables_in_$dbname"];
echo "<li><strong>$table_name</strong></li>";

// Display details of each table


echo "<ul>";

// Get columns of the table


$sql_show_columns = "DESCRIBE $table_name";
$columns_result = $conn->query($sql_show_columns);

if ($columns_result->num_rows > 0) {
while ($column = $columns_result->fetch_assoc()) {
echo "<li><strong>" . $column["Field"] . "</strong> - " .
$column["Type"] . " (" . $column["Null"] . ")</li>";
}
} else {
echo "<li>No columns found for the table.</li>";
}

echo "</ul>";
}
echo "</ul>";
} else {
echo "<p>No tables found in the database.</p>";
}

// Close the connection


$conn->close();
?>
22. VALIDATING INPUT

<?php
// Define variables and set to empty values
$name = $email = $message = "";
$nameErr = $emailErr = $messageErr = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
if (empty($_POST["name"])) {
$nameErr = "Name is required";
} else {
$name = test_input($_POST["name"]);
if (!preg_match("/^[a-zA-Z-' ]*$/", $name)) {
$nameErr = "Only letters and spaces allowed";
}
}

if (empty($_POST["email"])) {
$emailErr = "Email is required";
} else {
$email = test_input($_POST["email"]);
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$emailErr = "Invalid email format";
}
}

if (empty($_POST["message"])) {
$messageErr = "Message is required";
} else {
$message = test_input($_POST["message"]);
}
}

function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP Input Validation</title>
</head>
<body>
<h2>PHP Form Validation Example</h2>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);
?>">
Name: <input type="text" name="name" value="<?php echo $name; ?>">
<span style="color: red;">* <?php echo $nameErr; ?></span>
<br><br>
Email: <input type="text" name="email" value="<?php echo $email; ?>">
<span style="color: red;">* <?php echo $emailErr; ?></span>
<br><br>
Message: <textarea name="message" rows="5" cols="40"><?php echo $message;
?></textarea>
<span style="color: red;">* <?php echo $messageErr; ?></span>
<br><br>
<input type="submit" value="Submit">
</form>
</body>
</html>
23. FORMATTING THE OUTPUT

<?php
// Sample data
$name = "John Doe";
$price = 1234.567;
$date = "2025-02-03";

// Formatting
$formatted_price = number_format($price, 2, '.', ',');
$formatted_date = date("l, F j, Y", strtotime($date));
$uppercase_name = strtoupper($name);

?>

<!DOCTYPE html>
<html>
<head>
<title>PHP Output Formatting</title>
<style>
body { font-family: Arial, sans-serif; }
.output { font-weight: bold; color: blue; }
</style>
</head>
<body>
<h2>Formatted Output</h2>
<p>Name: <span class="output"><?php echo $uppercase_name; ?></span></p>
<p>Price: <span class="output">$<?php echo $formatted_price; ?></span></p>
<p>Date: <span class="output"><?php echo $formatted_date; ?></span></p>
</body>
</html>

You might also like