If you see a WordPress site, you will notice that at the bottom of the blog listing page, there will be links to next and previous pages.
They are useful to move the user to next or previous page.
If user is at the last page, then the next button will not be displayed.
Similarly, if user is at the first page, then the previous button will be hidden.
We are achieve this in PHP and MySQL. Following is the code to do that:
<?php
// Create a new PDO connection to the MySQL database
$pdo = new PDO("mysql:host=localhost; dbname=test", "root", "root", [
PDO::ATTR_PERSISTENT => true // Enables persistent connection for better performance
]);
// Define the number of records to show per page
$per_page = 2;
// Get the current page number from the query string, default to 1 if not set
$page = $_GET["page"] ?? 1;
// Calculate the offset for the SQL query
$skip = ($page - 1) * $per_page;
// Fetch the users for the current page, ordered by ID in descending order
$sql = "SELECT * FROM users ORDER BY id DESC LIMIT $skip, $per_page";
$stmt = $pdo->prepare($sql); // Prepare the SQL statement
$stmt->execute([]); // Execute the query
$users = $stmt->fetchAll(PDO::FETCH_OBJ); // Fetch all results as objects
// Query to get the total number of users in the database
$sql = "SELECT COUNT(*) AS total_count FROM users";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_OBJ);
$total_count = $row->total_count ?? 0; // Get the total count or default to 0
// Calculate the total number of pages required
$total_pages = ceil($total_count / $per_page);
// Determine if there are more pages
$has_more_pages = ($page * $per_page) < $total_count;
// Initialize previous and next page variables
$previous_page = $next_page = null;
// Determine the previous page number
if ($page > 1)
{
$previous_page = $page - 1;
}
// Determine the next page number
if ($page < $total_pages)
{
$next_page = $page + 1;
}
?>
<!-- Loop through and display each user -->
<?php foreach ($users as $user): ?>
<p><?php echo $user->name; ?></p>
<?php endforeach; ?>
<!-- Pagination controls -->
<?php if ($next_page > 0): ?>
<a href="?page=<?php echo $next_page; ?>">Previous</a>
<?php endif; ?>
<?php if ($previous_page > 0): ?>
<a href="?page=<?php echo $previous_page; ?>">Next</a>
<?php endif; ?>
Comments has been added with each line for explanation.
Even if you are working in any other language like Node.js and MongoDB, you can still achieve this because now you have the algorithm to do that.
From there, you will get the idea how you can render next and previous links if you are working in Node.js and MongoDB as backend and React as frontend.
Learn how to do case-sensitive search in MySQL. By default, MySQL search text-based column values without case sensitivity. For example, if your database looks like this:
Table: users
id
email
1
support@adnan-tech.com
2
support@Adnan-tech.com
And if you run the following query:
SELECT * FROM users WHERE email = "support@Adnan-tech.com";
It will return both records. However, it should have return only the 2nd record. The reason why, is because, usually the collation of text-based columns in MySQL is utf8_general_ci which is case insensitive.
So you just need to change the collation of “email” column in “users” table by running the following command:
ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_bin;
If you run the query again, you will only see the 2nd row. Even though the 1st row value is also same, it is in lower-case and the query is in upper case “A”.
This is because utf8mb4_bin, it ensures that the search and sort on this column should be case sensitive.
That is how you can do case-sensitive search in MySQL using utf8mb4_bin collation. It is really helpful specially if you are searching on column that has UUID values.
I was looking for a tool that allows me to write API documentation so I can provide it to the frontend developers and also for myself. I checked many but didn’t find any that fits to all of my requirements. So I decided to create one of my own. Creating my own tool gives me flexibility to modify it as much as I want.
I also wanted it to share it with other developers who might be having problem in finding the tool to write documentation for their API. So I uploaded it on Github and it is available for anyone for free.
How to write API documentation
A tool is created in PHP and MySQL that allows developers to write API documentation, and this tool is available for free. You can create multiple sections to group the APIs based on modules. For example, user authentication, user posts, comments, replies can be separate sections.
To write each API, you need to tell:
The section where it goes.
The name of the endpoint. It can be the URL of API.
The method, it can be either GET or POST. But since you will have the code, you can add more methods as per your needs.
Add a little description about the API, for example what this API does.
Headers:
You need to tell the key of header, whether it is required or optional. And a little description about the header, for example, it’s possible values.
Parameters:
Parameters are usually send in the URL. You can define them along with their key, and value and whether they are optional or not.
Arguments:
For defining the arguments, you need to specify it’s type too. Whether it can be an integer, a string, boolean value or a file object.
Example request body. You can write the CURL code inside it to give an example.
Status codes and their responses.
I wrote a complete documentation of a project using this tool. You can check that from here.
Suppose you have a database where data of all players of any sports team is stored. Every player has a name and a shirt number. We will create a program that will generate a new number for new player. We will make sure the new number is not already assigned to any player. We will keep generating new numbers until a unique number is found.
First, create a table “players” in your database by running the following SQL query.
CREATE TABLE IF NOT EXISTS players2 (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
shirt_number INTEGER(11) NOT NULL
);
Then, create a file named “assign-shirt-number-to-new-player.php” and write the following code in it:
// connect with database
$pdo = new PDO("mysql:host=localhost; dbname=test;", "root", "", [
PDO::ATTR_PERSISTENT => true
]);
This will make a persistent connection with the database. We are using PHP PDO because it has built-in support for SQL injection. After that, you need to fetch all the players already saved in the database.
// fetch all the players from the database
$statement = $pdo->prepare("SELECT * FROM players");
$statement->execute([]);
$rows = $statement->fetchAll(PDO::FETCH_OBJ);
Then we will set the range of numbers in which we want to generate a new number.
// set number of range for shirt numbers
$number_range = 10;
This will generate numbers between 0 and 10. After that, we will create a recursive function that will keep generating a new number unitl a unique shirt number is found.
// recursive function to return the new shirt number
function get_code()
{
// define global variables to access them inside function
global $rows, $number_range;
// if all the numbers are taken, then return -1
if (count($rows) >= $number_range)
return -1;
// generate a random number
$code = rand(0, $number_range);
// check if it already exists in the database
foreach ($rows as $row)
{
if ($row->shirt_number == $code) // if number already exists, then start recursion i.e. generate another number
return get_code();
}
// if number not already exists, then return it
return $code;
}
We need to call this function once to start checking for new unique shirt number.
// initial call to recursive function
$code = get_code();
There is a possibility that all the numbers from 0 to 10 are already taken, in this case, we will display an error message.
// if all numbers are taken, display an error
if ($code == -1)
{
echo "<p>All numbers are taken.</p>";
exit;
}
If everything worked fine, we will have a new unique shirt number in our $code variable. We can simply insert it in our database.
// name of new player
$name = "Adnan";
// if shirt number not taken, then insert in database
$statement = $pdo->prepare("INSERT INTO players (name, shirt_number) VALUES (:name, :code)");
$statement->execute([
":name" => $name,
":code" => $code
]);
// display the new shirt number
echo "<p>New code: " . $code . "</p>";
Complete code
<?php
// connect with database
$pdo = new PDO("mysql:host=localhost; dbname=test;", "root", "root", [
PDO::ATTR_PERSISTENT => true
]);
// fetch all the players from the database
$statement = $pdo->prepare("SELECT * FROM players");
$statement->execute([]);
$rows = $statement->fetchAll(PDO::FETCH_OBJ);
// set number of range for shirt numbers
$number_range = 10;
// recursive function to return the new shirt number
function get_code()
{
// define global variables to access them inside function
global $rows, $number_range;
// if all the numbers are taken, then return -1
if (count($rows) >= $number_range)
return -1;
// generate a random number
$code = rand(0, $number_range);
// check if it already exists in the database
foreach ($rows as $row)
{
if ($row->shirt_number == $code) // if number already exists, then start recursion i.e. generate another number
return get_code();
}
// if number not already exists, then return it
return $code;
}
// initial call to recursive function
$code = get_code();
// if all numbers are taken, display an error
if ($code == -1)
{
echo "<p>All numbers are taken.</p>";
exit;
}
// name of new player
$name = "Adnan";
// if shirt number not taken, then insert in database
$statement = $pdo->prepare("INSERT INTO players (name, shirt_number) VALUES (:name, :code)");
$statement->execute([
":name" => $name,
":code" => $code
]);
// display the new shirt number
echo "<p>New code: " . $code . "</p>";
I created an online free FTP manager in PHP that allows developers to work on their projects from anywhere. I created this tool in PHP and MySQL using Laravel framework. The frontend is designed in Bootstrap and React JS.
Let’s discuss each feature and I will also show you how I built this.
What you will learn:
Connect with FTP.
List files from FTP directory.
Fetch file content.
Edit FTP file.
Create a new file.
Create a new folder.
Upload files.
Download files.
Rename file.
Delete file.
Delete folder.
We created a PHP class named “FTP” in a file called “FTP.php”. All our functions will go in that class.
1. Connect with FTP
The first step is to connect with FTP server. Following code will create a function that will connect with your FTP server (we are not calling it yet).
class FTP
{
private $conn_id = null;
private $server = "";
private $username = "";
private $password = "";
private function do_connect()
{
try
{
// Establishing connection
$this->conn_id = ftp_connect($this->server);
if (!$this->conn_id)
{
echo json_encode([
"status" => "error",
"message" => "Could not connect to " . $this->server
]);
exit();
}
// Login with username and password
$login_result = ftp_login($this->conn_id, $this->username, $this->password);
if (!$login_result)
{
echo json_encode([
"status" => "error",
"message" => "Wrong password for '" . $this->username . "'."
]);
exit();
}
// Enable passive mode for better compatibility
ftp_pasv($this->conn_id, true);
}
catch (\Exception $exp)
{
echo json_encode([
"status" => "error",
"message" => "Could not connect to " . $this->server
]);
exit();
}
}
}
Comments has been added with each line for explanation. Make sure to enter your correct FTP server address, FTP account’s username and its password. In the next step, we will call this function to connect with FTP server.
2. List files from FTP directory
The next step is to list all files from FTP directory. Create the following function in your FTP class.
public function fetch_files()
{
$this->do_connect();
$path = "/directory-name";
// Get file listing
$file_list = ftp_nlist($this->conn_id, $path);
if ($file_list === false)
{
echo json_encode([
"status" => "error",
"message" => "Error retrieving file list."
]);
exit();
}
$files = [];
foreach ($file_list as $file)
{
$obj = [
"file" => $file,
"size" => ftp_size($this->conn_id, $file)
];
if ($obj["file"] != "." && $obj["file"] != "..")
{
array_push($files, $obj);
}
}
// Close the connection
ftp_close($this->conn_id);
echo json_encode([
"status" => "success",
"message" => "Data has been fetched.",
"files" => $files
]);
exit();
}
This function will first connect with the FTP server.
Then it will fetch all the files from $path variable.
If the directory does not exists or if it does not have read permission, then it will return an error.
All files from that directory will be put in an array along with the size of the file in bytes.
If it is a directory, then it’s size will be -1.
Finally, it returns the array with the response.
3. Fetch file content
Reading the content of FTP file is necessary because in order to edit the file, we need to first view the file. Following function will fetch the content of file and return it in response.
public function fetch_content()
{
$this->do_connect();
$file = "/directory-name/file-name.php";
// Local path for downloading the file
$local_file = tempnam(sys_get_temp_dir(), 'ftp_download');
// Download the file from FTP server to local file
if (!ftp_get($this->conn_id, $local_file, $file, FTP_BINARY))
{
echo json_encode([
"status" => "error",
"message" => "Failed to read '" . $file . "'"
]);
exit();
}
// Read contents of the local file
$file_content = file_get_contents($local_file);
// Display or process the file content as needed
if ($file_content === false)
{
echo json_encode([
"status" => "error",
"message" => "Failed to read file content."
]);
exit();
}
// Clean up: Delete the temporary local file
unlink($local_file);
echo json_encode([
"status" => "success",
"message" => "Data has been fetched.",
"content" => $file_content
]);
exit();
}
It will first download the file in a temporary directory to your server.
If the file does not exists, then it will return an error.
It will return an error if it fails to read the FTP file.
Usually it is because if the $path variable is a directory or an image or document file.
After the file’s content is fetched, we will remove the temporary file from our server.
4. Edit FTP file
After the file’s content is successfully fetched, the next step is to update the file. You are working in FTP, editing the code is what you will be doing most of the time.
Updating the content of file requires 2 steps:
Saving the file in temporary folder.
Uploading the temporary file on FTP server.
public function update_content()
{
$this->do_connect();
$file = "/directory-name/file-name.php";
$content = "console.log(\"Hello world\")";
// Local path for downloading the file
$local_file = tempnam(sys_get_temp_dir(), 'ftp_download');
// Write the new content to a local temporary file
file_put_contents($local_file, $content);
if (!ftp_put($this->conn_id, $remote_file, $local_file, FTP_ASCII))
{
echo json_encode([
"status" => "error",
"message" => "There was a problem updating '" . $remote_file . "'"
]);
exit();
}
unlink($local_file);
// Close the FTP connection
ftp_close($this->conn_id);
echo json_encode([
"status" => "success",
"message" => "File has been updated."
]);
exit();
}
5. Create a new file
Creating a new file in FTP in PHP is as simple as updating the file. We just need to set the content of file as empty string. So we will be uploading an empty file to FTP server.
public function create_file()
{
$this->do_connect();
$path = "/directory-name";
$name = "file-name.php";
// Local file content to be uploaded
$file_content = "";
// Local path for downloading the file
$local_file = tempnam(sys_get_temp_dir(), 'ftp_download');
// Write the content to a temporary local file
file_put_contents($local_file, $file_content);
// Remote file path where the file will be created
$remote_file = $path . "/" . $name; // Replace with the desired remote file path
// Upload the local file to the FTP server
if (!ftp_put($this->conn_id, $remote_file, $local_file, FTP_ASCII))
{
echo json_encode([
"status" => "error",
"message" => "There was a problem while creating '" . $name . "'"
]);
exit();
}
unlink($local_file);
// Close the FTP connection
ftp_close($this->conn_id);
echo json_encode([
"status" => "success",
"message" => "Successfully created '" . $name . "'"
]);
exit();
}
Create a temporary empty file on local directory.
Upload it to FTP.
Remove the file from local directory.
6. Create a new folder
Creating a folder is must easier than creating a file on FTP server.
public function create_folder()
{
$this->do_connect();
$path = "/directory-name";
$name = "folder-name";
$folder = $path . "/" . $name;
if (!ftp_mkdir($this->conn_id, $folder))
{
echo json_encode([
"status" => "error",
"message" => "There was a problem while creating '" . $name . "'"
]);
exit();
}
echo json_encode([
"status" => "success",
"message" => "Successfully created '" . $name . "'"
]);
exit();
}
Note: It will return an error if the directory already exists.
7. Upload files
To upload the file from local computer to FTP server, you need to first save it in your local server. Then upload the file to FTP server. After it is successfully uploaded, you can remove the file from local server.
public function upload()
{
$this->do_connect();
$path = "/directory-name";
$file_name = basename($_FILES["file"]["name"]);
$target_file = "uploads/" . $file_name;
if (!move_uploaded_file($_FILES["file"]["tmp_name"], $target_file))
{
echo json_encode([
"status" => "error",
"message" => "The file " . htmlspecialchars(basename( $_FILES["file"]["name"])) . " has been uploaded."
]);
exit();
}
if (!ftp_put($this->conn_id, $file_name, $target_file, FTP_ASCII))
{
echo json_encode([
"status" => "error",
"message" => "There was a problem updating '" . $file_name . "'"
]);
exit();
}
// Close the FTP connection
ftp_close($this->conn_id);
echo json_encode([
"status" => "success",
"message" => "File(s) has been uploaded."
]);
exit();
}
If you are working in Laravel, your upload function should be like this:
public function upload()
{
$this->do_connect();
$path = "/directory-name";
$files = request()->file("files");
foreach ($files as $file)
{
$remote_file = $file->getClientOriginalName();
$file_path = "ftp/" . $file->getClientOriginalName();
$file->storeAs("/private", $file_path);
if (!ftp_put($this->conn_id, $remote_file, storage_path("app/private/" . $file_path), FTP_ASCII))
{
return response()->json([
"status" => "error",
"message" => "There was a problem updating '" . $remote_file . "'"
]);
}
Storage::delete("private/" . $file_path);
}
// Close the FTP connection
ftp_close($this->conn_id);
return response()->json([
"status" => "success",
"message" => "File(s) has been uploaded."
]);
}
8. Download files
Downloading the file to local computer requires 2 steps.
First is to download the file on local server from FTP server.
Then download it to local computer from local server.
Download file from FTP to local server
Following code will be used to download the file on local server.
public function download()
{
$this->do_connect();
$path = "/directory-name";
$file = "file-name.php";
// Local path for downloading the file
$local_file = tempnam(sys_get_temp_dir(), 'ftp_download');
$remote_file = $path . "/" . $file;
// Download the file from FTP server to local file
if (!ftp_get($this->conn_id, $local_file, $remote_file, FTP_BINARY))
{
echo json_encode([
"status" => "error",
"message" => "Failed to download '" . $remote_file . "'"
]);
exit();
}
// Create a unique download URL for the file
$download_url = url('download.php?file=' . urlencode($local_file) . '&name=' . urlencode($file));
// Close the FTP connection
ftp_close($this->conn_id);
echo json_encode([
"status" => "success",
"message" => "File has been downloaded.",
"download_url" => $download_url
]);
exit();
}
It will also return the “download_url” variable. That will be link to file from where you can download the file.
Download from server to local computer
Create a new file named “download.php” in your server and write the following code in it:
if (isset($_GET['file']) && isset($_GET['name']))
{
$temp_file = $_GET['file'];
$file_name = $_GET['name'];
if (file_exists($temp_file))
{
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="' . $file_name . '"');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($temp_file));
// Read the file and output it to the browser
readfile($temp_file);
// Delete the temporary file
unlink($temp_file);
exit();
}
echo "File does not exist.";
exit();
}
echo "Invalid request.";
exit();
9. Rename file
Renaming the file on FTP using PHP is very easy. You just have to provide 3 things:
Folder name
Old file name
New file name
public function rename()
{
$this->do_connect();
$path = "/directory-name";
$name = "new-file-name.php";
$file = "old-file-name.php";
// File to be renamed on the FTP server
$remote_file = $path . "/" . $file;
$remote_new_file = $path . "/" . $name;
// Try to rename the file or folder
if (!ftp_rename($this->conn_id, $remote_file, $remote_new_file))
{
echo json_encode([
"status" => "error",
"message" => "There was a problem renaming " . $file . " to " . $name
]);
exit();
}
echo json_encode([
"status" => "success",
"message" => "Successfully renamed " . $file . " to " . $name
]);
exit();
}
10. Delete file
Deleting a file from FTP in PHP is very simply. You just need to enter the full path of the file and call the PHP built-in ftp_delete function. Pass the FTP connection ID and file path as arguments.
public function delete_file()
{
$this->do_connect();
$path = "/directory-name/file-name.php";
// Delete the file on the FTP server
if (!ftp_delete($this->conn_id, $path))
{
echo json_encode([
"status" => "error",
"message" => "There was a problem while deleting " . $path
]);
exit();
}
echo json_encode([
"status" => "success",
"message" => "Successfully deleted " . $path
]);
exit();
}
11. Delete folder
Deleting a folder is NOT as simple as deleting a single file. There are 2 steps involved in deleting a folder from FTP in PHP.
Recursively delete all files and folders inside that folder.
Delete the folder itself.
So first we will create a function that will initialize the call to recursive function.
public function delete_folder()
{
// Set maximum execution time to unlimited (0 means no limit)
set_time_limit(0);
$path = "/directory-path/folder-name";
$this->do_connect();
$this->ftp_delete_dir($path);
echo json_encode([
"status" => "success",
"message" => "Folder has been deleted."
]);
exit();
}
Our recursive function ftp_delete_dir will look like this:
// Function to recursively delete a directory
private function ftp_delete_dir($path)
{
// Get the list of files in the directory
$files = ftp_nlist($this->conn_id, $path);
// Loop through each file
foreach ($files as $file)
{
$file = basename($file);
if ($file == '.' || $file == '..')
{
continue;
}
$file_path = "$path/$file";
// If it's a directory, delete recursively
if (@ftp_chdir($this->conn_id, $file_path))
{
ftp_chdir($this->conn_id, '..');
ftpDelete($this->conn_id, $file_path);
}
else
{
// If it's a file, delete it
ftp_delete($this->conn_id, $file_path);
}
}
// Finally, remove the directory itself
ftp_rmdir($this->conn_id, $path);
}
It does the following things:
Loops through all the files inside that folder.
Skip the “.” and “..” folders (they are just pointing towards current and parent directory respectively).
If the file is a directory, start the recursion.
Else delete the file.
Finally at line #33, we are deleting the current directory itself.
So these are all the features we have in FTP manager I created in PHP and MySQL using Laravel framework. If you have any quetions regarding FTP or if you are having any problem, feel free to contact me.
Real estate business that requires a website for their business, we are providing a free website for them in PHP and MySQL (Laravel framework).
Screenshots
Features
Following are the key features in this project. More features can be added on-demand.
Admin panel
There is a dedicated admin panel from where admin can manage all properties, set business email, phone and address. He can also manage the banners on home page. Administrator can also see all the messages received from “contact us” form from use side.
Properties
In this free real estate website, you can mention the properties available. From admin panel, you can add, edit or delete any property you want. You just need to enter the property name and its price. You can also upload an image of the property. For more information, you can write the detail on the “description” box.
Apart from these, you can also mention the following attributes of the property:
Area of property.
Number of floors.
Number of rooms.
Number of bedrooms.
Number of bathrooms.
Parking space.
And payment process (Cash, Bank, Cheque etc.).
Home banners
You can upload banners for home page. When user lands on your real estate website, he will see those banners right infront of him. You can upload as many banners as you want, but the ideal number is 3. You can delete any banner whenever you want.
Business information
To update the business information like phone, email and address. You don’t need to go into source code and update these on all places. On admin panel, you can goto “settings” and enter their values and they will be updated on all places in the website. You can also set the name of website from settings page.
Social network
Same as you can update your business information, you can enter your social media links from admin panel. Right now you can set the following social network links:
Facebook
Twitter
Instagram
LinkedIn
But more can be added on-demand.
Contact us
On user-side, we have a “contact us” form from where user can enter his name and email and send a message. You can see all user’s sent messages on admin panel. You can see their name and email, so if you want to contact them back, you can do easily.
Installation
Create a database “real_estate” in your phpMyAdmin.
Previously we did password less authentication in Node JS and Mongo DB, in this article we will do it in PHP and MySQL.
Suppose you are trying to login to someone’s computer. You might open an incognito window in order to prevent the password from being saved in the browser. But what if he has installed a keylogger in his computer ? He can track your keystrokes and will know your email and password.
You can try typing your password from virtual keyboard, but what if he has also installed a screen recroding software that quitely records the screen ?
So the website developers must provide a way to secure their users from such vulnerabilities.
The best way is to allow users to login without entering their password. In-fact, there will be no password. User just have to enter his email address. We will send him a verification code. He needs to enter that code to verify.
That code will be auto-generated and will not be used again once user is logged-in. So even if some keylogging or screen recording software knows the verification code, it will be of no use. Because the code will not work next time someone tries with that user’s email address.
Create a Table
First I am going to create users table. If you already have one, you just need to add another column code in it.
<?php
$db_name = "test";
$username = "root";
$password = "";
$conn = new PDO("mysql:host=localhost;dbname=" . $db_name, $username, $password);
$sql = "CREATE TABLE IF NOT EXISTS users(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
email TEXT NULL,
code TEXT NULL
)";
$result = $conn->prepare($sql);
$result->execute();
I am using PDO to prevent SQL injection. If you want to know more about PHP PDO, you can check our guide here. If you refresh the page now and check your phpMyAdmin, you will have users table created in your database.
users-table-password-less-authentication-php
Login Form
Next we need to create a form to ask for user’s email address. It will also have a submit button.
This will create an input field and a submit button.
Send email in PHP
Then we need to generate a random code and send an email with that verification code. For sending email, we will be using a library PHPMailer. You can download it and extract the folder in your project’s root directory.
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
require 'PHPMailer/src/Exception.php';
require 'PHPMailer/src/PHPMailer.php';
require 'PHPMailer/src/SMTP.php';
function send_mail($to, $subject, $body)
{
//Create an instance; passing `true` enables exceptions
$mail = new PHPMailer(true);
try {
//Server settings
$mail->SMTPDebug = 0; //Enable verbose debug output
$mail->isSMTP(); //Send using SMTP
$mail->Host = 'mail.adnan-tech.com'; //Set the SMTP server to send through
$mail->SMTPAuth = true; //Enable SMTP authentication
$mail->Username = 'support@adnan-tech.com'; //SMTP username
$mail->Password = ''; //SMTP password
$mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS; //Enable implicit TLS encryption
$mail->Port = 465; //TCP port to connect to; use 587 if you have set `SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS`
//Recipients
$mail->setFrom('support@adnan-tech.com', 'Adnan Afzal');
$mail->addAddress($to); //Name is optional
//Content
$mail->isHTML(true); //Set email format to HTML
$mail->Subject = $subject;
$mail->Body = $body;
$mail->AltBody = $body;
$mail->send();
// echo 'Message has been sent';
} catch (Exception $e) {
// echo "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
}
}
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
$email = $_POST["email"];
$str = "qwertyuiopasdfghjklzxcvbnm1234567890";
$code = "";
for ($a = 1; $a <= 6; $a++)
{
$code .= $str[rand(0, strlen($str) - 1)];
}
$subject = "Login";
$body = "Your verification code is " . $code;
$sql = "SELECT * FROM users WHERE email = :email";
$result = $conn->prepare($sql);
$result->execute([
":email" => $email
]);
$user = $result->fetch();
if ($user == null)
{
$sql = "INSERT INTO users(email, code) VALUES (:email, :code)";
$result = $conn->prepare($sql);
$result->execute([
":email" => $email,
":code" => $code
]);
send_mail($email, $subject, $body);
header("Location: verify.php?email=" . $email);
exit();
}
$sql = "UPDATE users SET code = :code WHERE email = :email";
$result = $conn->prepare($sql);
$result->execute([
":email" => $email,
":code" => $code
]);
send_mail($email, $subject, $body);
header("Location: verify.php?email=" . $email);
exit();
}
Here, first I am generating a random 6 character code. Then I am setting the body of the email in a $body variable.
Then I am checking if the user already exists in the database. If not, then insert a new row in database. Sends an email and redirect the user to the verification page.
If user already exists, then I am simply updating his code column. Sends an email and redirect to a new page to verify the code.
Note: You need to enter your correct SMTP credentials (email and password) in order to send the email.
Verify the Code
Create a new file verify.php and inside it, first get the email from the URL. Then create a form with an hidden input field for email. One input field for user to enter the code sent at email, and a submit button.
We are creating a hidden input field so that it will be sent along with form. Now when the form submits, we need to check if user has provided correct code.
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
$email = $_POST["email"] ?? "";
$code = $_POST["code"] ?? "";
$sql = "SELECT * FROM users WHERE email = :email AND code = :code";
$result = $conn->prepare($sql);
$result->execute([
":email" => $email,
":code" => $code
]);
$user = $result->fetch();
if ($user == null)
{
die("Invalid code.");
}
$sql = "UPDATE users SET code = NULL WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":id" => $user["id"]
]);
die("Logged-in");
}
If the user provides an in-valid code, then we are simply displaying him an error message. If he has provided the correct code, then we are setting his code column to NULL so it won’t be used again.
You can also try sending an AJAX and pass the code value as null, it still won’t be logged-in.
// for testing only
const ajax = new XMLHttpRequest()
ajax.open("POST", "verify.php", true)
const formData = new FormData()
formData.append("email", "support@adnan-tech.com")
formData.append("code", null)
ajax.send(formData)
So that’s it. That’s how you can add password-less authentication system in your application using PHP and MySQL. If you face any problem in following this, kindly do let me know.
If you are working in Laravel, you might have noticed that majority of the developers save sensitive information like database credentials, SMTP settings and API keys in the .env file. This file exists at the root level of your project.
I was also saving my credentials in .env files for a long time until I found some problems in it. So I had to find a better way to save sensitive credentials securely.
Problem with .env file
The problems I found while saving sensitive credentials in .env file in Laravel are:
Caches
The variables you set in .env file will be cached by Laravel. You might change the value of variable but the Laravel will be picking the old cached value.
However, you can refresh cache by running the following commands but it still an extra work:
Imagine you are using PayPal or Stripe in your Laravel application and you have saved their API keys in .env file. You have delivered the project but now the client has to change his account again and again. Instead of him messaging you everytime he needs to change his API keys, you can provide him a simple admin panel from where he can set those values dynamically.
Might be exposed
Wrong deployment or 1 bad configuration in .htaccess might lead to exposing your .env file to the public. Hence exposing all your sensitive credentials to everyone.
Database Credentials
Probably the first credential that developers set are the database credentials. Because without them, migrations and seeders won’t run.
I have commented out the database credentials in .env file:
Then in Javascript, I created a function that will be called when this form submits. This function will call an AJAX to save these credentials in the database:
This will save credentials in settings table if does not exists. If exists, then it will update its value.
Following is the schema of the settings table:
Schema::create('settings', function (Blueprint $table) {
$table->id();
$table->string("key")->nullable();
$table->longText("value")->nullable();
$table->timestamps();
});
settings-table
Now that you have saved the values in database, I will show you how to fetch the credentials from database.
$settings = DB::table("settings")->get();
$settings_obj = new \stdClass();
foreach ($settings as $setting)
{
$settings_obj->{$setting->key} = $setting->value;
}
echo $settings_obj->smtp_host;
This will fetch all the credentials from database and convert them into object using key value pairs. So you can access your values just like a normal object.
Hard-coded strings
There might be some scenarios where you do want to save some hard-coded strings. In that case, I have created a new file named “config.php” inside config folder and wrote all my hard-coded variables there:
config is a built-in function in Laravel. In “config.app_name”, config is the name of the file I created and app_name is the variable created inside it.
A Job Portal website is created in PHP and MySQL using MVC architecture. MVC stands for Model-View-Controller and this architectural design is used by many websites for scalability.
✅ Compatible with almost every shared/dedicated/VPS hosting.
✅ Free support.
New features:
✅ Optimized sending bulk emails.
Files included:
.php
.css
.js
Tech stack:
PHP +7.0
MySQL +5.0
Bootstrap 4
Vue JS 3
Recruiter can post a job
Recruiter posted jobs
Change status of applicant
Edit/delete job
A recruiter can edit or delete any of his posted jobs at any time. This helps if the recruiter needs to change the requirements for a job or delete if the vacancy is already been filled.
Jobs Listing
Users will get a listing of all the jobs posted by recruiters.
Job Detail
They can view the job details by clicking the job title.
Filter Jobs
On the jobs listing page, users can filter jobs by the type of job they want, like developer, designer, etc. By the location to see if the job is in their city or if they can relocate to that city. Or by the nature of the job i.e. is the job part-time, full-time, or remote. Users can also search the jobs in their salary range. This way they can find jobs that pay them according to their needs.
Real-time updates on new jobs
Users will get real-time updates whenever a new job is posted by the recruiter. They do not have to refresh the page to check if there is any new job. To develop this feature, I have used sockets. You need to install Node JS in your system or server to make this feature work. Even if you do not have Node JS installed, all other features will work except for real-time job updates.
Email notifications of new jobs
Users can turn on notifications from recruiters. Whenever that recruiter posts a new job, all the users who have turned on their notifications will receive an email.
Admin can see all the stats
The admin of the website can see the total number of users registered. The total number of jobs that have been posted by recruiters. And the total number of applications that have been submitted for those jobs. The admin can see all this information on his dashboard.
Manage users
Admin will have the option to add new users to the website. While adding, he/she can select if the user be an applicant or a recruiter. Admin can also manage existing users. Admin can edit the user and can also delete the user if required. Admin can change the password of the user as well. This is helpful if the user is finding it difficult to receive the password reset email. Or if you want to prevent the user from logging in.
Deployment
This Job Portal website can be deployed on any server that supports PHP and MySQL.
Hello. In this article, we are going to show you some MongoDB and MySQL equivalent queries. This will help you greatly if you want to convert a MySQL project into MongoDB or vice-versa.
Video tutorial:
Introduction
First, let’s give a small introduction to both of these databases.
MongoDB
MongoDB is schema-less database architecture. Means that the schema or structure of the database does not needs to be defined. Schema will automatically gets created as data comes in. It is used where data needs to be loosely-coupled.
MySQL
MySQL is a structured query language. The structure of the database and its tables needs to be defined before creating them. It is used where data needs to be tightly-coupled.
MongoDB
MySQL
It is schema-less.
Schema needs to be defined.
Non-relational database.
Relational database.
It has collections.
It has tables.
It has documents.
It has rows.
Used for loosely-coupled data.
Used for tightly-coupled data.
Horizontal scaling.
Vertical scaling.
Each document can have different structure.
Each row must have same structure.
Data is not dependent on other collections.
Data might be dependent on other tables.
Uses nested objects and arrays.
Uses separate tables and joins them using foreign keys.
1. Creating collections/tables
MongoDB
As mentioned above, MongoDB collections does not needs to be created. They will be created automatically once a document is inserted in it.
MySQL
To create a table in MySQL database, you can run the following query:
CREATE TABLE IF NOT EXISTS users(
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name TEXT NOT NULL,
age INTEGER DEFAULT 0
);
This will create a table named “users” having 3 columns “id”, “name” and “age”. ID will be a unique key that will automatically be incremented once a new row is inserted. While “name” will be a text string field and “age” will be an integer number.
2. Inserting documents/rows
MongoDB
To insert a document in a MongoDB collection, you can run the following query:
db.users.insertOne({
name: "Adnan",
age: 30
});
This will insert a new document in “users” collection. It will automatically assign unique ObjectId to it named “_id”.
This will sort the users documents by name in descending order, skip 1 record and return only 1 record.
Similar query can be run on MySQL in the following way:
SELECT * FROM users WHERE age = 30 ORDER BY id DESC LIMIT 1, 1
LIMIT {skip}, {limit} this is the syntax of LIMIT command in SQL.
3.4 Less or greater than
In MongoDB, you can use $lt (less than) and $gt (greater than) operators like this:
db.users.find({
age: {
$lt: 30
}
}).toArray()
This will return the users whose age is less than 30. Following query will return the users whose age is greater than 30:
db.users.find({
age: {
$gt: 30
}
}).toArray()
You can also use $lte and $gte operators for “less than and equal to” and “greater than and equal to” conditions respectively.
Above are the MongoDB queries, following are their equivalent MySQL queries:
/* less than */
SELECT * FROM users WHERE age < 30;
/* less than and equal to */
SELECT * FROM users WHERE age <= 30;
/* greater than */
SELECT * FROM users WHERE age > 30;
/* greater than and equal to */
SELECT * FROM users WHERE age >= 30;
4. Updating data
To update a document in MongoDB collection, you would do the following:
UPDATE users SET age = age + 3 WHERE name = "Adnan"
5. Delete data
To delete a document from MongoDB collection, you can run the following query:
db.users.deleteOne({
name: "Adnan"
})
This will delete one document from users collection whose name is “Adnan”. To delete multiple, you can use deleteMany() function instead.
In MySQL, you can do:
DELETE FROM users WHERE name = "Adnan" LIMIT 1
6. Relationships
MongoDB
MongoDB is not a relational database. Data saved in one collection is not dependent on another collection’s data.
For example, if you want to save job history of each user. You do not have to create a separate collection for that. You can simply push a new job in document’s array.
Whereas, MySQL is a relational database. Data saved in one table might be dependent on another table’s data.
If you want to achieve the above in MySQL, you would have to create a separate table for that and create user_id as foreign key and reference it to your “users” table.
CREATE TABLE IF NOT EXISTS jobs(
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
title TEXT NOT NULL,
company TEXT NOT NULL,
period TEXT NOT NULL,
user_id INTEGER NOT NULL,
CONSTRAINT fk_user_id_jobs FOREIGN KEY (user_id) REFERENCES users(id)
)
After that, you can insert a new job of that user using the following query:
INSERT INTO jobs (title, company, period, user_id) VALUES ("Developer", "adnan-tech.com", "3 years", 1)
This will insert a new row in “jobs” table and link it with “users” table using its foreign ID “user_id”.
jobs-table
To fetch the data both from users and jobs table, you have to perform a join operation.
SELECT * FROM users INNER JOIN jobs ON users.id = jobs.user_id
This will return all the users along with their jobs.
jobs-table-join-users-table
So you have learned to create many MongoDB queries to their equivalent MySQL queries and vice-versa. If you have any question related to this, feel free to comment it in the section below. You can also check our more tutorials on MongoDB to learn more.
