An email marketing tool is created in Laravel and Vue JS. It is a single-page application. It allows you to send bulk emails to a large group of people. The database used for this project is MySQL.
The first step is to add an SMTP account. You can add your hosting SMTP account or even your personal Gmail account as well. To add an SMTP account, you need to enter your hostname, usually, it is like this mail.yourdomain.com. But you need to visit your hosting cPanel and go to the “Email Accounts” to confirm.
Then you need to enter the “Display name“. This is the name that will be displayed to the recipients when you start sending emails through campaigns. And finally, the email and password of your email account. Your credentials are stored securely in our server and you can delete the SMTP account at any time and it will be deleted permanently (no trash can or backup).
Contact us to get help in this step.
Note: If you delete the SMTP account, all campaigns where that account is used, will be deleted as well.
2. Create a list
Then you need to create a list. In this list, you can add as many people as you want. You only need to enter the name of the list, for example, “USA Subscribers” etc. You can add multiple lists as well. Then while running the campaign, you can simply select the list and it will automatically send emails to all the users in that list.
3. Add subscribers in a list
Now that you have created a list, you can start adding subscribers to it. Adding a subscriber is super easy, simply enter the name and email of the person. Then select the list where he wanted to be added. You can add the same subscriber in multiple lists as well. There is no such restriction.
4. Create a campaign for email marketing
Creating a campaign is also super simple. You just need to enter the name of the campaign like “Black Friday Sales” etc. Then you need to write the content of the email that needs to be sent. The content of the email can be HTML too so you can use HTML tags and CSS properties as well.
Then you need to select the list for that campaign. The campaign will send emails to all the subscribers that are added to that list. And finally, you need to select the SMTP account from whom the email should be sent. Since you might have multiple SMTP accounts like:
sales@yourwebsite.com
support@yourwebsite.com
info@yourwebsite.com
no-reply@yourwebsite.com
You can select the SMTP account of your choice to use as a “Sender” of that particular campaign.
5. Run an email marketing campaign
The final step is to run the campaign and sit and relax. You will see all your added campaigns with a status. We have 3 statuses for each campaign:
draft: The campaign is created and ready to start sending emails.
active: The campaign has been started and is sending emails to the selected list of subscribers. You can check every 30 minutes to check the progress.
done: The campaign has sent emails to all the subscribers added to that list.
Dynamic variables in email
Since in email marketing you will be sending emails in bulk, you might want to address each user differently. For example, it is better to have an email that starts with “Hello Mr. John” rather than just “Hello”. Personalizing your email has a higher chance of getting more sales.
Track each email
The email will be sent to a large number of people and the content of each email can be different like the recipient name etc. So you can see the content of each email sent to subscribers so you can know if your users have received the email correctly.
Send single email
Campaigns are used to send bulk emails, but sometimes you just want to send an email to a single person only. We have added a feature that allows you to do that. You can simply enter the name and email address of the recipient, and enter the subject and body of the email. Again the body of the email can be HTML too.
Email with attachments
When sending single emails, you can also attach multiple documents to them. The documents will be attached to the email and the recipient will be able to download it directly from their mail client, for example, Gmail, Outlook, Webmail, Thunderbird, etc.
Business model
If you buy this project, you can also make money from it. Just give a trial period of 3 days with all features to your users, after that you can charge per day. You can see the pricing from here, you can set the pricing as you desire. Check the below video to find out how the trial period works:
Google one-tap sign in and sign up
You can sign in or sign-up with your Google account with just one click. If the user’s account is not created, then it will be created automatically and the user will be logged-in. Otherwise, it will simply log in to the user.
In this article, we are going to show you, how you can change title text of an HTML page using simple Javascript. We will show a real-time notification to your users in their title bar using PHP, MySQL, and Node JS. You can show a notification in a title bar with just PHP and MySQL. But the “real-time” effect can only be achieved with Node JS.
We can change title text using Javascript’s document.title object, but we will show you with a real-world example.
What we will do:
Save notification in the database
Display counter in the title bar
Show all unread notifications to the user
Mark notification as read
Make notifications real-time
1. Save notifications in the MySQL database
First, we need to save the notifications in the database. Notifications can be of any type, for example, “a new message is received” or “your account has been activated” etc. We will create a new file named “send-notification.php“.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// create table if not exists
$sql = "CREATE TABLE IF NOT EXISTS notifications (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER,
message TEXT,
is_read BOOLEAN,
created_at DATETIME
)";
// prepare the query
$statement = $conn->prepare($sql);
// execute the statement
$statement->execute();
// user who needs to receive the notification
$user_id = 565; // replace this your user ID
$message = "You have received a new message " . time() . ".";
$is_read = 0;
// insert in notifications table
$sql = "INSERT INTO notifications (user_id, message, is_read, created_at) VALUES (?, ?, ?, NOW())";
$statement = $conn->prepare($sql);
$statement->execute([
$user_id, $message, $is_read
]);
Comments have been added with each line for an explanation. Run the above code and check your database, you will see a new table named “notifications” and also a new row is inserted in that table. Now we need to display this on the user side.
2. Display the counter in the title bar
We just need to fetch the total number of unread notifications from the database and display them in the user’s title bar. Create a new file named “index.php” and it will have the following code:
<?php
// start session and login the user
session_start();
// you might be saving the session during login,
// I am hard-coding the value for testing purpose
$_SESSION["user_id"] = 565;
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// get number of unread notifications
$sql = "SELECT COUNT(*) AS total_unread_notifications FROM notifications WHERE user_id = ? AND is_read = 0";
$statement = $conn->prepare($sql);
$statement->execute([
$_SESSION["user_id"]
]);
$row = $statement->fetch();
$total_unread_notifications = $row["total_unread_notifications"];
Then we need to fetch this variable in Javascript and prepend it in the title bar. First, we need to create a title tag and a hidden input field where we will put the above variable value.
<!-- title where notification number will be displayed -->
<title>My Website</title>
<!-- save variables in hidden input field to access in Javascript -->
<input type="hidden" id="total-unread-notifications" value="<?php echo $total_unread_notifications; ?>" />
Then we need to get this input field value in Javascript and render it in the title tag. We will create a separate Javascript function to render the title bar because we will need it in multiple situations when:
The page is loaded
There is a new notification (increment)
A notification is read by the user (decrement)
<script>
// get variables in Javascript
var totalUnreadNotifications = document.getElementById("total-unread-notifications").value;
totalUnreadNotifications = parseInt(totalUnreadNotifications);
// show count in title bar
showTitleBarNotifications();
function showTitleBarNotifications() {
// pattern to check if there is any counter number at the start of title bar
var pattern = /^\(\d+\)/;
if (totalUnreadNotifications == 0) {
document.title = document.title.replace(pattern, "");
return;
}
if (pattern.test(document.title)) {
// update the counter
document.title = document.title.replace(pattern, "(" + totalUnreadNotifications + ")");
} else {
// prepend the counter
document.title = "(" + totalUnreadNotifications + ") " + document.title;
}
}
</script>
This will first check if the value of variable totalUnreadNotifications is greater than 0. If it is 0, then we will not show any number at all (not even 0). Then we will check if there is already any value in the title bar. If there is any value, then we will simply increment the counter value and re-render the title bar.
If there isn’t any value already in the title bar, then we will simply prepend the counter before the title tag content.
Here, the document.title is the only line that change title text.
This does change title text because initially, it was only “My Website”. After a notification is fetched from database, it prepends the value in the <title> tag.
At this point, you are seeing the number of unread notifications in your title bar. Now you need to find a way to mark notifications as “read” and decrement the counter.
3. Show all unread notifications to the user
To mark notifications as “read”, we first must show all the notifications to the user which when clicked will be marked as “read”. After that, the counter will be decremented and the title tag will be re-rendered. We will create a new file named “notifications.php” to display all notifications to the user.
<?php
// start session
session_start();
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// get all notifications sorting by unread goes first
$sql = "SELECT * FROM notifications WHERE user_id = ? ORDER BY is_read ASC";
$statement = $conn->prepare($sql);
$statement->execute([
$_SESSION["user_id"]
]);
$notifications = $statement->fetchAll();
This will fetch all the notifications of the logged-in user. Now we need to display them in an HTML table. We will simply by displaying a notification message and a button to mark that notification as read.
<!-- show all notifications in a table -->
<table>
<tr>
<th>Message</th>
<th>Action</th>
</tr>
<?php foreach ($notifications as $notification): ?>
<tr>
<td><?php echo $notification['message']; ?></td>
<td>
<!-- show 'read' button only if the notification is un-read -->
<?php if (!$notification['is_read']): ?>
<form onsubmit="return markAsRead();">
<input type="hidden" name="id" value="<?php echo $notification['id']; ?>" />
<input type="hidden" name="user_id" value="<?php echo $notification['user_id']; ?>" />
<input type="submit" value="Read" />
</form>
<?php endif; ?>
</td>
</tr>
<?php endforeach; ?>
</table>
If you refresh the page now, you will see a list of all notifications with the button “read”. On clicking that button, we need to call an AJAX request to the server to mark this notification as “read” in the MySQL database. We will be using Vanilla JS to do that, no jQuery or any other external library is being used.
Now we need to create this Javascript function that will actually send the AJAX request. We will be using AJAX because there will be a lot of notifications and it will not be a good idea to keep refreshing the page for each notification to be marked as read.
<script>
// when the read button is clicked
function markAsRead() {
// prevent the form from submitting
event.preventDefault();
// get the form node
var form = event.target;
// create AJAX object
var ajax = new XMLHttpRequest();
// set method and URL of request
ajax.open("POST", "read-notification.php", true);
// when the status of request changes
ajax.onreadystatechange = function () {
// when the response is received from server
if (this.readyState == 4) {
// if the response is successful
if (this.status == 200) {
// convert the JSON string into Javascript object
var data = JSON.parse(this.responseText);
console.log(data);
// if there is no error
if (data.status == "success") {
// remove the 'read' button
form.remove();
// [emit read notification event here]
}
}
}
};
// create form data object with the form
var formData = new FormData(form);
// send the AJAX request with the form data
ajax.send(formData);
}
</script>
This will prevent the form from redirecting and calling our Javascript code. After the notification is successfully marked as read from the server, we will remove the “read” button. The last thing we need is a server-side PHP file that will handle this request.
4. Mark notification as read
Now we need to create a new file named “read-notification.php” that will mark this notification as “read”. In this file, we will also check the user_id along with the notification ID to make sure that the notification we are marking is “read” is actually sent to the logged-in user.
<?php
// start the session
session_start();
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// get ID from AJAX
$id = $_POST["id"];
// mark notification as read
$sql = "UPDATE notifications SET is_read = 1 WHERE id = ? AND user_id = ?";
$statement = $conn->prepare($sql);
$statement->execute([
$id,
$_SESSION["user_id"]
]);
// send the response back to client
echo json_encode([
"status" => "success"
]);
exit();
This file will first check that the notification ID sent from AJAX actually refers to the logged-in user. Because someone can try to tamper with the client-side code and mark any other user’s notification as “read”. So we must do server-side validation like above.
At this point, if you run the code now, you will be able to see all the notifications in an HTML table with a “Read” button. When clicked, will mark the notification as read and also will remove the “read” button.
If you refresh the “index.php” file, you will now see the counter in the title bar will be decremented. One more thing you can do is to make it real-time, so when there is any notification added, the counter will be incremented in the title bar. And also when the notification is read by the user, then the counter should be decremented automatically.
5. Make notifications real-time
Making these notifications in real-time is crucial. You might have used the WhatsApp Web where you will see that the counter in the title bar automatically gets incremented and decremented based on the number of new notifications you received and the notifications have you read.
Now we need to learn how you can implement this functionality in your project. We will be using the Socket IO JS library for this and we will also create a small Node JS server for this purpose. You can download Socket IO JS from their official site.
After downloading, you need to paste the JS file into your project. You also need to download and install Node JS in your system, you can download it from here.
Setting up Node JS server
After Node JS installation, open the command prompt or terminal in your project root directory and run the following command:
npm init
This will ask a couple of questions, you can press “enter” for all questions and it will automatically set the default answer. Then we need to install the required modules for this feature. Run the following command in your terminal:
npm install express http socket.io
This will install the Express, HTTP, and Socket IO module in your Node JS app. To start the server, we need to install another module globally named “nodemon“. So again, run the following command in your terminal:
npm install -g nodemon
Typically when you made changes in your code, you need to manually restart the Node JS server. But it slows down the process during development. So this module automatically restarts the server if there is any change in the server file.
Create a new file named “server.js“, this will be our Node JS server file. Paste the following code in that file, we will explain this in the next step:
// initialize express server
var express = require("express");
var app = express();
// create http server from express instance
var http = require("http").createServer(app);
// include socket IO
var socketIO = require("socket.io")(http, {
cors: {
origin: ["http://localhost"]
}
});
// start the HTTP server at port 3000
http.listen(process.env.PORT || 3000, function () {
console.log("Server started running...");
// an array to save all connected users IDs
var users = [];
// called when the io() is called from client
socketIO.on("connection", function (socket) {
// called manually from client to connect the user with server
socket.on("connected", function (id) {
users[id] = socket.id;
});
});
});
In this file we are:
Initializing the Express framework and also HTTP module.
Including the Socket IO module. You can place your own server URL in the CORS origin array.
Then we are starting the server at port 3000. We use process.env.PORT on deployment.
When a new user is connected with a server, a unique socket ID is generated. We are saving each user’s socket ID in a local array along with his ID from the database.
Now is the time to start the server. Run the following command in your terminal to start the server.
nodemon server.js
If you check the terminal, you will see a message “Server started running…” this means that you are ready to send connect your client-side with this server.
Increment counter on new notification
Whenever a new notification is sent to the server, we need to automatically increment the counter in the title bar. We have already created a function in “index.php” that displays the counter in the title bar.
To add this feature we need to perform the following tasks:
Connect socket IO JS on the “send-notification.php” file.
Then emit (send) the notification event to the Node JS server along with the ID of the user.
On the Node JS server-side, listen to that event and emit the event to the relative user.
Connect socket IO JS on the “index.php” file.
Listener for that new notification event.
When that event is received, increment the counter and re-render the title.
The following code goes in the “send-notification.php” file after the notification is inserted in the database:
<!-- save user id -->
<input type="hidden" id="user-id" value="<?php echo $user_id; ?>" />
<!-- include socket IO JS -->
<script src="socket.io.js"></script>
<script>
// connect with Node JS server
var socketIO = io("http://localhost:3000");
// get user ID
var userId = document.getElementById("user-id").value;
// send notification to the server
socketIO.emit("newNotification", userId);
</script>
We are already creating the $user_id variable in the first step. Then we are simply including the socket IO JS library and connect with the Node JS server. Getting the user ID from the hidden input field. And finally emitting an event to the server with a user ID.
In your “server.js” listen to this event and emit the event to the user with the same ID.
// when a new notification is received
socket.on("newNotification", function (userId) {
// send notification to the selected user
socketIO.to(users[userId]).emit("newNotification", userId);
});
The event has been dispatched from the server to the client. Now the client must listen to this event and when received should increment the counter and re-render the title. The following code goes in your “index.php“:
<!-- include socket IO JS -->
<script src="socket.io.js"></script>
<script>
// connect with Node JS server
var socketIO = io("http://localhost:3000");
// connect user with Node JS server
var userId = document.getElementById("user-id").value;
socketIO.emit("connected", userId);
// when a new notification is received
socketIO.on("newNotification", function (data) {
totalUnreadNotifications++;
showTitleBarNotifications();
});
</script>
Open “index.php” and “send-notification.php” in separate tabs and refresh both of these pages. Every time you refresh the send notification page, you will see the title of the index file gets incremented. Now we need to do the same for reading notifications, except that the title bar will be decremented whenever a notification is marked as read.
Decrement counter on reading notification
In “notifications.php” first, we need to include the socket IO JS library and connect with the server.
<!-- include socket IO JS -->
<script src="socket.io.js"></script>
<script>
// connect with Node JS server
var socketIO = io("http://localhost:3000");
</script>
Following code goes in the [emit read notification event here] section:
// send notification to the server
socketIO.emit("notificationRead", form.user_id.value);
Now we need to create a listener for this in our “server.js” file.
socket.on("notificationRead", function (userId) {
socketIO.to(users[userId]).emit("notificationRead", userId);
});
Similarly, you need to create a listener on the user side too in the index.php file.
socketIO.on("notificationRead", function (data) {
totalUnreadNotifications--;
showTitleBarNotifications();
});
This also change title text, but this time it decrements the counter value.
Run the code now and refresh both of your browsers. And try to read some notifications, as soon as you read them, you will see that the counter in the title bar will be decremented too.
So that’s how you can change title text of an HTML page using vanilla Javascript.
Conclusion
So that’s how you can implement a real-time notification system in your website with an unread notification counter in the title bar using simple PHP, Javascript, and Node JS. There is no PHP or Javascript framework used in this tutorial, so you can work with it with any framework in your existing project.
That’s how you can change the title text of the browser tab. If you face any problems in following this, kindly do let me know in the comments section below.
In this tutorial, we are going to teach you how to create a dynamic FAQ section on your website using PHP and MySQL. We will create simple CRUD (Create, Read, Update, Delete) operation using PHP PDO. FAQs will be added, updated, and deleted from the admin panel. And they will be displayed on the user side.
Usually, the FAQ section is displayed statically. But it would be great if you can manage it from your admin panel. That’s why we should choose dynamic FAQ section over hard-coded one.
Table of content:
Add FAQ from the admin panel
Display all FAQs to the admin
Edit any FAQ
Delete FAQ
Display FAQs on the user side
We will be using PHP PDO prepared statements to prevent SQL injection. We will be using bootstrap, jQuery, font-awesome, and richText libraries in this tutorial. All source files can be downloaded at the end of this article. Download all these libraries and paste them into your project.
1. Add FAQ
First, we will create a file named “add.php“. In this file, we will first include the above libraries.
<!-- include bootstrap, font awesome and rich text library CSS -->
<link rel="stylesheet" type="text/css" href="css/bootstrap.css" />
<link rel="stylesheet" type="text/css" href="font-awesome/css/font-awesome.css" />
<link rel="stylesheet" type="text/css" href="richtext/richtext.min.css" />
<!-- include jquer, bootstrap and rich text JS -->
<script src="js/jquery-3.3.1.min.js"></script>
<script src="js/bootstrap.js"></script>
<script src="richtext/jquery.richtext.js"></script>
Then we will create a form that will display input fields for the question and its answer.
Then you need to initialize the richText library to make the textarea field with more options like text styles, fonts, lists, tables, and images, etc. Following Javascript code will do that:
// initialize rich text library
window.addEventListener("load", function () {
$("#answer").richText();
});
Then we will handle this request in the same file. We will create the required table in the MySQL database dynamically. Then we will insert a new row with questions and answers entered in input fields.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// check if insert form is submitted
if (isset($_POST["submit"]))
{
// create table if not already created
$sql = "CREATE TABLE IF NOT EXISTS faqs (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
question TEXT NULL,
answer TEXT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)";
$statement = $conn->prepare($sql);
$statement->execute();
// insert in faqs table
$sql = "INSERT INTO faqs (question, answer) VALUES (?, ?)";
$statement = $conn->prepare($sql);
$statement->execute([
$_POST["question"],
$_POST["answer"]
]);
}
// [query to get all FAQs]
?>
Run the code now and you will be able to see a form with 2 input fields. When hit submit, you will see a new table will be created in your database. And a new row will be inserted in that table. Add a few more questions and their answers using the form.
2. Display all FAQ
Now we need to display all added FAQs to the admin side. In the same add.php file, the following code goes in the [query to get all FAQs] section:
// get all faqs from latest to oldest
$sql = "SELECT * FROM faqs ORDER BY id DESC";
$statement = $conn->prepare($sql);
$statement->execute();
$faqs = $statement->fetchAll();
This will fetch all the questions sorted by newest to oldest. Now we need to display all of them on a table. The following code goes in the [show all FAQs here] section:
Refresh the page now and you will see an edit link with each row. Then create a file named “edit.php” that will handle this request.
2. Fetch the row from MySQL
You will see an ID in the URL in the browser’s address bar. We need to get this value and search it in the MySQL database. This file will be similar to add.php but with auto-populate values in the input fields. First, we will fetch the row from the database.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// check if FAQ exists
$sql = "SELECT * FROM faqs WHERE id = ?";
$statement = $conn->prepare($sql);
$statement->execute([
$_REQUEST["id"]
]);
$faq = $statement->fetch();
if (!$faq)
{
die("FAQ not found");
}
// [update query goes here]
?>
This will fetch the row from the database using the ID from the URL. If the row is not found, then it will display an error and stop the script.
3, 4 & 5. Create form with auto-populate values
This form will use the above $faq variable to display the values in input fields.
If you refresh the page now, you will see the same form as the added FAQ but the input fields are auto-populated with selected FAQ values. It will also have a hidden input field for the ID.
6 & 7. Update the data and redirect back
Now we need to handle the update request. In the edit.php file, replace the [update query goes here] section with the following code to update the data in the database.
// check if edit form is submitted
if (isset($_POST["submit"]))
{
// update the FAQ in database
$sql = "UPDATE faqs SET question = ?, answer = ? WHERE id = ?";
$statement = $conn->prepare($sql);
$statement->execute([
$_POST["question"],
$_POST["answer"],
$_POST["id"]
]);
// redirect back to previous page
header("Location: " . $_SERVER["HTTP_REFERER"]);
}
Refresh the page now and try to update any FAQ. You will see that it will be updated in the MySQL database as well as in the HTML table too.
4. Delete FAQ
First, we need to show a delete button with each row. It will be a form with a hidden input field that contains the value of that FAQ. When that form is submitted, we will again first check if the record exists in the database. If yes, then we will simply remove it.
The following code goes in the [delete button goes here] section of the add.php file:
<!-- delete form -->
<form method="POST" action="delete.php" onsubmit="return confirm('Are you sure you want to delete this FAQ ?');">
<input type="hidden" name="id" value="<?php echo $faq['id']; ?>" required />
<input type="submit" value="Delete" class="btn btn-danger btn-sm" />
</form>
Then we need to create a new file named “delete.php” that will handle this request.
<?php
// connect database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// check if FAQ existed
$sql = "SELECT * FROM faqs WHERE id = ?";
$statement = $conn->prepare($sql);
$statement->execute([
$_REQUEST["id"]
]);
$faq = $statement->fetch();
if (!$faq)
{
die("FAQ not found");
}
// delete from database
$sql = "DELETE FROM faqs WHERE id = ?";
$statement = $conn->prepare($sql);
$statement->execute([
$_POST["id"]
]);
// redirect to previous page
header("Location: " . $_SERVER["HTTP_REFERER"]);
?>
If you refresh the page now, you will see a red delete button with each row. On clicking, it will ask for confirmation. When confirmed, it will remove the FAQ from the MySQL database using PHP.
5. Display FAQs on the user side
Now comes the final part, where we will display our dynamic FAQ on the user side. Typically we have an index.php as our website home page. But you might have a separate page for FAQs. Either way, you first need to fetch all the data from the database:
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// fetch all FAQs from database
$sql = "SELECT * FROM faqs";
$statement = $conn->prepare($sql);
$statement->execute();
$faqs = $statement->fetchAll();
?>
After that, we will be using Bootstrap’s panel to display the data as an accordion.
Run the code now and you will see a beautiful accordion with a question, which when clicked will display an answer. You can add as many questions and their answer from the database and they will be displayed here automatically.
So that’s how you can create a dynamic FAQ section on your website that can be managed from your admin panel.
Learn how to add dynamic testimonial section on your website from here.
In this tutorial, we are going to teach you how you can add a dynamic testimonial to your website using PHP and MySQL in the backend and Vue JS in the frontend. Testimonials are displayed on a website to show the new users how your product satisfies your previous customers. We will be creating 2 pages, one for adding testimonials from the admin panel. And second to display all testimonials in a beautiful design.
If you do not have a dynamic testimonial, then you have to manually add, modify or delete a testimonial from your website. By going dynamic, you can perform all these actions from your admin panel.
Add Testimonial
First, download Bootstrap from here and Vue JS from here. Paste the CSS and JS files in your project, we will be using them in a moment. After that, we need to create a form from which we can add testimonials. Each testimonial will have a picture of the person, name, designation in his company, and his comments.
The following code goes in your admin panel from where you want to add testimonials.
<!-- include bootstrap -->
<link rel="stylesheet" type="text/css" href="bootstrap.min.css" />
<!-- include vue js -->
<script src="vue.min.js"></script>
<!-- container for vue js app -->
<div class="container" style="margin-top: 50px; margin-bottom: 50px;" id="addTestimonialApp">
<div class="row">
<!-- center align form -->
<div class="offset-md-3 col-md-6">
<h2 style="margin-bottom: 30px;">Add Testimonial</h2>
<!-- form to add testimonial -->
<form v-on:submit.prevent="store" enctype="multipart/form-data">
<!-- picture of user -->
<div class="form-group">
<label>Picture</label>
<input type="file" name="picture" accept="image/*" class="form-control" />
</div>
<!-- name of user -->
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control" />
</div>
<!-- designation of user -->
<div class="form-group">
<label>Designation</label>
<input type="text" name="designation" class="form-control" />
</div>
<!-- comment -->
<div class="form-group">
<label>Comment</label>
<textarea name="comment" class="form-control"></textarea>
</div>
<!-- submit button -->
<input type="submit" name="submit" class="btn btn-info" value="Add Testimonial" />
</form>
</div>
</div>
[show all testimonials for deleting]
</div>
This will show a form with input fields. But when you click on the “Add Testimonial” button, nothing happens. This is because we need to render it using Vue JS.
<script>
// initialize vue js app
var addTestimonialApp = new Vue({
el: "#addTestimonialApp", // id of container div
data: {
// all values used in this app
testimonials: []
},
// all methods
methods: {
// [other methods goes here]
// called when form is submitted
store: function () {
// get this app instance
var self = this;
var form = event.target;
// call an AJAX to create a new entry in testimonials
var ajax = new XMLHttpRequest();
ajax.open("POST", "store.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
// console.log(response);
alert(response.message);
// if there is no error
if (response.status == "success") {
self.testimonials.unshift(response.testimonial);
form.reset();
} else {
// when there is any error
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// create form data object and form to it
var formData = new FormData(form);
// actually sending the request
ajax.send(formData);
},
},
// [mount code goes here]
});
</script>
Refresh the page now and you will be able to submit the form, this is because of Vue JS. An AJAX request will be sent to the server to store the picture attached and save the other fields in the MySQL database using PHP.
Create a new file named “store.php” and paste the following code in it:
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// create tables if not exists
$sql = "CREATE TABLE IF NOT EXISTS testimonials (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
picture TEXT NULL,
name VARCHAR(255) NULL,
designation VARCHAR(255) NULL,
comment TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)";
$result = $conn->prepare($sql);
$result->execute();
$file_path = "";
if ($_FILES["picture"]["error"] == 0)
{
$folder_name = "testimonials";
mkdir($folder_name);
$file_path = $folder_name . "/" . $_FILES["picture"]["name"];
move_uploaded_file($_FILES["picture"]["tmp_name"], $file_path);
}
// insert in testimonials table
$sql = "INSERT INTO testimonials (picture, name, designation, comment, created_at) VALUES (?, ?, ?, ?, NOW())";
$result = $conn->prepare($sql);
$result->execute([
$file_path,
$_POST["name"],
$_POST["designation"],
$_POST["comment"],
]);
$testimonial_id = $conn->lastInsertId();
// get the testimonial just inserted in database
$sql = "SELECT * FROM testimonials WHERE id = ?";
$result = $conn->prepare($sql);
$result->execute([
$testimonial_id
]);
$testimonial = $result->fetch();
echo json_encode([
"status" => "success",
"message" => "Testimonial has been added.",
"testimonial" => $testimonial
]);
exit();
If you refresh the page now, upload the picture, enter the fields and hit submit, it will create a new table in the database if not already created. Then it will create a folder named “testimonials” and save the image file in it. Then it will insert a new row in it. And finally, it will return the new row back to the client (AJAX).
From there we will prepend it in our local testimonials array. Now we need to display all the added testimonials in an HTML table with a button to delete them.
Display all Testimonials to Admin
The following code goes in the [show all testimonials for deleting] section:
This will create an empty HTML table because we need to load the in it first. We will call an AJAX to fetch all the stored testimonials using PHP and MySQL and display them using Vue JS. The following code goes in the [mount code goes here] section:
mounted: function () {
this.getData();
}
Now we need to create a function named “getData” in our Vue JS instance. Replace the code in section [other methods goes here] with the following:
// [delete method]
getData: function () {
// get this app instance
var self = this;
// call an AJAX to get all testimonials
var ajax = new XMLHttpRequest();
ajax.open("POST", "fetch.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
// console.log(response);
// if there is no error
if (response.status == "success") {
self.testimonials = response.data;
} else {
// when there is any error
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// create form data object
var formData = new FormData();
// actually sending the request
ajax.send(formData);
},
Finally, we need to create a new file named “fetch.php” that will fetch all the testimonials from the MySQL database using PHP.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// fetch all testimonials
$sql = "SELECT * FROM testimonials ORDER BY id DESC";
$statement = $conn->prepare($sql);
$statement->execute();
$data = $statement->fetchAll();
// create new field for full comment text
// because we will be displaying less text and display 'show more' button
for ($a = 0; $a < count($data); $a++)
{
$data[$a]["comment_full"] = $data[$a]["comment"];
$data[$a]["comment"] = substr($data[$a]["comment"], 0, 50);
}
// send the response back to client
echo json_encode([
"status" => "success",
"message" => "Testimonial has been fetched.",
"data" => $data
]);
exit();
?>
Refresh the page now and you will be able to see all the testimonials added. Also, if you add a new testimonial, it will automatically be prepended in the HTML table. Now we need to make it able to delete the testimonial.
Delete Testimonial
We need to follow the following steps to delete the testimonial:
Call an AJAX with an ID of testimonial.
On server side, fetch the testimonial using ID.
Delete the picture from the “testimonials” folder using PHP unlink() function.
Delete the testimonial from MySQL database.
Send the response back to client.
The client will remove the testimonial from local array.
It will automatically be removed from the HTML table.
Replace the section [delete method] with the following code:
// method to delete testimonial
deleteTestimonial: function () {
// get this app instance
var self = this;
// get form
var form = event.target;
// call an AJAX to delete the testimonial
var ajax = new XMLHttpRequest();
ajax.open("POST", "delete.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
console.log(response);
// remove from local array if deleted from server
if (response.status == "success") {
for (var a = 0; a < self.testimonials.length; a++) {
var testimonial = self.testimonials[a];
if (testimonial.id == form.id.value) {
self.testimonials.splice(a, 1);
break;
}
}
} else {
// display an error message
alert(response.message);
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// append form in form data object
var formData = new FormData(form);
// call AJAX with form data
ajax.send(formData);
},
Then we need to create a new file named “delete.php” that will handle this request. It will have the following code:
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// get the testimonial just inserted in database
$sql = "SELECT * FROM testimonials WHERE id = ?";
$result = $conn->prepare($sql);
$result->execute([
$_POST["id"]
]);
$testimonial = $result->fetch();
if (!$testimonial)
{
// send the response back to client
echo json_encode([
"status" => "error",
"message" => "Testimonial not found."
]);
exit();
}
// remove the picture from folder
unlink($testimonial["picture"]);
// create a query to delete the pricing table from database
$sql = "DELETE FROM testimonials WHERE id = ?";
// prepare the query
$result = $conn->prepare($sql);
// execute the query
$result->execute([
$_POST["id"]
]);
// send the response back to client
echo json_encode([
"status" => "success",
"message" => "Testimonial has been deleted."
]);
exit();
Refresh the page now and you will be able to delete the testimonials as well. The only thing left is to show the testimonials on the user side.
Display Testimonials on User Side
To display testimonials on the user side, you need to download font-awesome and slick, you already have the Bootstrap and Vue JS files in your project folder. You can download font-awesome from here and slick from here. After downloading, paste both folders into your project, we will include them on your user side.
The following code will display the layout for each testimonial using HTML. Which we will render using Vue JS in the next step.
Create a new file named “script.js” and paste the following code in it:
var mainURL = window.location.origin + "/" + window.location.pathname + "/";
var testimonialApp = new Vue({
el: "#testimonialApp",
data: {
testimonials: []
},
methods: {
loadMoreContent: function () {
var node = event.target;
var index = node.getAttribute("data-index");
if (this.testimonials[index].comment.length > 50) {
// it needs to display less
node.innerHTML = "show more";
this.testimonials[index].comment = this.testimonials[index].comment_full.substr(0, 50);
} else {
// it needs to display more
node.innerHTML = "show less";
this.testimonials[index].comment = this.testimonials[index].comment_full;
}
},
getData: function () {
// get this app instance
var self = this;
// call an AJAX to get all testimonials
var ajax = new XMLHttpRequest();
ajax.open("POST", "fetch.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
// console.log(response);
// if there is no error
if (response.status == "success") {
self.testimonials = response.data;
setTimeout(function () {
$('.items').slick({
dots: true,
infinite: true,
speed: 800,
autoplay: false,
slidesToShow: 2,
slidesToScroll: 2,
responsive: [{
breakpoint: 1024,
settings: {
slidesToShow: 3,
slidesToScroll: 3,
infinite: true,
dots: true
}
}, {
breakpoint: 600,
settings: {
slidesToShow: 2,
slidesToScroll: 2
}
}, {
breakpoint: 480,
settings: {
slidesToShow: 1,
slidesToScroll: 1
}
}
]
});
}, 100);
} else {
// when there is any error
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// create form data object
var formData = new FormData();
// actually sending the request
ajax.send(formData);
}
},
mounted: function () {
this.getData();
}
});
At this point, you will be able to view the testimonials added from the admin panel on your website. It will also have a carousel slider. Finally, you can apply some CSS styles to make it look better for the users.
So that’s it, you have a dynamic testimonial section fully manageable from the admin panel. If you face any problems in following this, kindly do let us know in the comments section below.
A pricing table on a website is used to give your users an idea of how much you charge for your services. In this article, we are going to teach you, how you can create a dynamic pricing table using PHP, MySQL, and Vue JS. The pricing table is created in bootstrap so it will be mobile responsive.
From the admin panel, we will create multiple pricing tables and their data will be stored in the MySQL database. On the user side, the data will be fetched from the database and displayed to the user using Vue JS.
Pricing tables are basically packages offered at different prices so users can pick the one they find convenient. We will be using PHP as a server-side language, and MySQL to store the pricing table data in the database. And Vue JS to render the HTML.
Show form to add pricing table
To make our pricing table dynamic, we must first add it in the database. First, download Bootstrap from here and Vue JS from here. Paste the CSS and JS files in your project, we will be using them in a moment. After that, we need to create a form from which we can create a pricing table.
Each pricing table will have a title, a short description, an amount, and some features. The feature means the services you will be provided in that package.
The following code goes in your admin panel from where you want to add pricing tables.
<!-- include bootstrap -->
<link rel="stylesheet" type="text/css" href="bootstrap.min.css" />
<!-- include vue js -->
<script src="vue.min.js"></script>
<!-- container for vue js app -->
<div class="container" style="margin-top: 50px; margin-bottom: 50px;" id="addPricingTableApp">
<div class="row">
<!-- center align form -->
<div class="offset-md-3 col-md-6">
<h2 style="margin-bottom: 30px;">Create Pricing Table</h2>
<!-- prevent the form from redirecting, and call JS function 'store' -->
<form v-on:submit.prevent="store">
<!-- get title of pricing table -->
<div class="form-group">
<label>Title</label>
<input type="text" v-model="title" class="form-control" />
</div>
<!-- get description -->
<div class="form-group">
<label>Description</label>
<input type="text" v-model="description" class="form-control" />
</div>
<!-- get amount -->
<div class="form-group">
<label>Amount</label>
<input type="number" v-model="amount" class="form-control" />
</div>
<!-- list of features in this pricing table -->
<h2 style="margin-top: 50px; margin-bottom: 50px;">Features</h2>
<div class="row" style="margin-bottom: 50px;">
<div class="col-md-12">
<!-- loop through an array in vue js app -->
<div v-for="(feature, index) in features" style="border: 1px solid black; padding: 10px; margin-bottom: 10px;">
<!-- show input field to get the feature value -->
<div class="form-group">
<label>Feature</label>
<input type="text" class="form-control" v-model="features[index].value" placeholder="Can have HTML" />
</div>
<!-- button to delete the feature from pricing table -->
<button type="button" class="btn btn-danger btn-sm" v-on:click="deleteFeature" v-bind:data-index="index">Delete Feature</button>
</div>
</div>
</div>
<!-- button to add new feature -->
<div class="row" style="margin-bottom: 20px;">
<div class="col-md-12">
<button type="button" class="btn btn-success" v-on:click="addFeature">Add Feature</button>
</div>
</div>
<!-- submit button -->
<input type="submit" class="btn btn-info" value="Add Pricing" />
</form>
</div>
</div>
[show all pricing tables]
</div>
This will show a form with input fields. But when you click on the “Add Feature” button, nothing happens. This is because we need to render it using Vue JS.
<script>
// initialize vue js app
var addPricingTableApp = new Vue({
el: "#addPricingTableApp", // id of container div
data: {
// all values used in this app
title: "",
description: "",
amount: 0,
features: [],
pricings: []
},
// all methods
methods: {
// [store method]
// delete feature box
deleteFeature: function () {
var index = event.target.getAttribute("data-index");
this.features.splice(index, 1);
},
// add new feature box
addFeature: function() {
this.features.push({
value: ""
});
}
}
});
</script>
Refresh the page now and you will be able to add and remove features, this is because of Vue JS. Now we need to call an AJAX request to save this pricing table data in the MySQL database so it can be fetched later.
Save pricing table in MySQL using PHP
First, we need to create a “store” method in Vue JS “methods” object. The following code goes in the [store method] section:
// called when form is submitted
store: function () {
// get this app instance
var self = this;
// call an AJAX to create a new pricing table
var ajax = new XMLHttpRequest();
ajax.open("POST", "store.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
console.log(response);
// if added in database, then prepend in local array too
if (response.status == "success") {
self.pricings.unshift(response.pricing);
} else {
// display an error message
alert(response.message);
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// create form data object and append all the values in it
var formData = new FormData();
formData.append("title", this.title);
formData.append("description", this.description);
formData.append("amount", this.amount);
formData.append("features", JSON.stringify(this.features));
// actually sending the request
ajax.send(formData);
},
Then we need to create a file named “store.php” that will handle this request. Following will be the code of the store.php file:
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// create tables if not exists
$sql = "CREATE TABLE IF NOT EXISTS pricings (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT NULL,
amount DOUBLE NOT NULL
)";
$result = $conn->prepare($sql);
$result->execute();
$sql = "CREATE TABLE IF NOT EXISTS pricing_features (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
pricing_id INTEGER,
value TEXT NOT NULL,
FOREIGN KEY (pricing_id) REFERENCES pricings(id) ON UPDATE CASCADE ON DELETE CASCADE
)";
$result = $conn->prepare($sql);
$result->execute();
// insert in pricings table
$sql = "INSERT INTO pricings (title, description, amount) VALUES (:title, :description, :amount)";
$result = $conn->prepare($sql);
$result->execute([
":title" => $_POST["title"],
":description" => $_POST["description"],
":amount" => $_POST["amount"]
]);
$pricing_id = $conn->lastInsertId();
// save all features in a separate table
$features = json_decode($_POST["features"]);
foreach ($features as $feature)
{
$sql = "INSERT INTO pricing_features(pricing_id, value) VALUES (:pricing_id, :value)";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $pricing_id,
":value" => $feature->value
]);
}
// get the pricing table just inserted in database
$sql = "SELECT * FROM pricings WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":id" => $pricing_id
]);
$pricing = $result->fetch();
// send the response back to client with new pricing table
echo json_encode([
"status" => "success",
"message" => "Pricing table has been added.",
"pricing" => $pricing
]);
exit();
This assumes that you have a database named “test”. You can change it as per yours if you already have one. This will create the pricings and pricing_features table if you do not already have one.
Refresh the page now and fill out the pricing table form and hit submit. Then refresh your phpMyAdmin page and you will see a new row in the “pricings” table. You will also see multiple rows in the “pricing_features” table.
Show pricing table on the user side
Now the pricing table and its features have been saved in the MySQL database, you can easily display them on your user side. Paste the following code anywhere you want to show the pricing table to your users:
We need to call an AJAX request to get the pricing tables from the server. So following will be your Javascript code on same file:
<script>
var pricingTableApp = new Vue({
el: "#pricingTableApp",
data: {
pricings: []
},
methods: {
getData: function () {
var self = this;
var ajax = new XMLHttpRequest();
ajax.open("POST", "get-data.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
// console.log(this.responseText);
var response = JSON.parse(this.responseText);
console.log(response);
if (response.status == "success") {
self.pricings = response.pricings;
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
var formData = new FormData();
ajax.send(formData);
}
},
mounted: function () {
this.getData();
}
});
</script>
Now create a file named “get-data.php” that will fetch pricing table along with their features and return to the client.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// get all pricing tables sorting by amount from lowest to highest
$sql = "SELECT * FROM pricings ORDER BY amount ASC";
$result = $conn->prepare($sql);
$result->execute();
$pricings = $result->fetchAll();
// get all the features of each pricing table too
$data = [];
foreach ($pricings as $pricing)
{
$sql = "SELECT * FROM pricing_features WHERE pricing_id = :pricing_id";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $pricing["id"]
]);
$pricing_features = $result->fetchAll();
$pricing["features"] = $pricing_features;
array_push($data, $pricing);
}
// send the response back to client with the data
echo json_encode([
"status" => "success",
"message" => "Data has been fetched.",
"pricings" => $data
]);
exit();
Refresh the page now and you will be able to see a beautifully designed pricing table on your website.
Edit & delete the pricing table
We will have a pure dynamic pricing table if we were able to modify or delete the features from it from admin panel.
At this point, your desired work is work. But you can go ahead and allow an admin to edit or delete the pricing table. Suppose you want to add more features to some package, or you want to delete a specific pricing table altogether.
Display all pricing tables
First, we need to display a list of all pricing tables in an HTML table. The following code goes in the [show all pricing tables] section:
<!-- show all pricing tables -->
<div class="row" style="margin-top: 50px;">
<div class="col-md-12">
<table class="table table-bordered">
<!-- table heading -->
<tr>
<th>ID</th>
<th>Title</th>
<th>Description</th>
<th>Actions</th>
</tr>
<!-- loop through an array of pricing tables -->
<tr v-for="(pricing, index) in pricings">
<td v-text="pricing.id"></td>
<td v-text="pricing.title"></td>
<td v-text="pricing.description"></td>
<td>
<!-- edit pricing table link -->
<a class="btn btn-link" v-bind:href="'edit.php?id=' + pricing.id">
Edit
</a>
<!-- form to delete pricing table -->
<form v-on:submit.prevent="deletePricing">
<input type="hidden" name="id" v-model="pricing.id" required />
<input type="submit" class="btn btn-danger btn-sm" value="Delete" />
</form>
</td>
</tr>
</table>
</div>
</div>
We already have a Vue JS instance named addPricingTableApp. When this instance is mounted, we will call an AJAX request to get the pricing tables from MySQL database using PHP.
var addPricingTableApp = new Vue({
el: "#addPricingTableApp",
data: {
// other variables
},
methods: {
// other methods
// method to get all pricing tables from server
getData: function () {
// get this app instance
var self = this;
// call an AJAX to fetch all pricing tables
var ajax = new XMLHttpRequest();
ajax.open("POST", "get-data.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
console.log(response);
// render the data in table
if (response.status == "success") {
self.pricings = response.pricings;
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// send an AJAX request to the server
var formData = new FormData();
ajax.send(formData);
},
// method to delete pricing table
deletePricing: function () {
//
},
// other methods
},
mounted: function () { // called when the DOM is mounted
this.getData();
}
});
Refresh the admin side now and you will be able to view all the pricing tables in an HTML table. We already have a separate file for fetching data from the database “get-data.php“, so it will fetch easily.
Update
The table above created will also display edit and a delete button. When you click on the edit button, it will take you to the “edit.php” file with a parameter having the value of that pricing table. Now we need to create a file named “edit.php” which will show a form with an auto-populated value of that pricing table.
The edit page will also have the functionality to add or delete features from the pricing table. We will also be using Vue JS here to populate the values in input fields.
This will be almost the same as adding a new pricing table. First, we need to fetch the values from the MySQL database by calling an AJAX request.
<script>
var editPricingTableApp = new Vue({
el: "#editPricingTableApp",
data: {
id: "",
pricing: null
},
methods: {
// [update method goes here]
getPricingTable: function () {
var self = this;
var ajax = new XMLHttpRequest();
ajax.open("POST", "get-pricing-table.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
// console.log(this.responseText);
var response = JSON.parse(this.responseText);
console.log(response);
if (response.status == "success") {
self.pricing = response.pricing;
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
var formData = new FormData();
formData.append("id", self.id);
ajax.send(formData);
},
deleteFeature: function () {
var index = event.target.getAttribute("data-index");
this.pricing.features.splice(index, 1);
},
addFeature: function() {
this.pricing.features.push({
value: ""
});
}
},
mounted: function () {
this.id = document.getElementById("pricing-id").value;
this.getPricingTable();
}
});
</script>
Then we need to create a file named “get-pricing-table.php” to fetch the data of this pricing table only.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// get pricing table using ID
$sql = "SELECT * FROM pricings WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":id" => $_POST["id"]
]);
$pricing = $result->fetch();
// get all the features too of this pricing table
$sql = "SELECT * FROM pricing_features WHERE pricing_id = :pricing_id";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $pricing["id"]
]);
$pricing_features = $result->fetchAll();
$pricing["features"] = $pricing_features;
// send the response back to client with the data
echo json_encode([
"status" => "success",
"message" => "Pricing table has been fetced.",
"pricing" => $pricing
]);
exit();
Refresh the edit page now and you will be able to view the data in input fields along with all the features of that pricing table.
Now when this form submits, we need to call an AJAX request to update the data. The following code goes in your [update method goes here] section:
update: function () {
var self = this;
var ajax = new XMLHttpRequest();
ajax.open("POST", "update.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
// console.log(this.responseText);
var response = JSON.parse(this.responseText);
console.log(response);
alert(response.message);
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
var formData = new FormData();
formData.append("id", this.id);
formData.append("title", this.pricing.title);
formData.append("description", this.pricing.description);
formData.append("amount", this.pricing.amount);
formData.append("features", JSON.stringify(this.pricing.features));
ajax.send(formData);
},
Finally, create a file named “update.php” that will handle this request and will update the pricing table.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// udpdate the pricing table
$sql = "UPDATE pricings SET title = :title, description = :description, amount = :amount WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":title" => $_POST["title"],
":description" => $_POST["description"],
":amount" => $_POST["amount"],
":id" => $_POST["id"]
]);
// delete all the old features
$sql = "DELETE FROM pricing_features WHERE pricing_id = :pricing_id";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $_POST["id"]
]);
// insert new features
$features = json_decode($_POST["features"]);
foreach ($features as $feature)
{
$sql = "INSERT INTO pricing_features(pricing_id, value) VALUES (:pricing_id, :value)";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $_POST["id"],
":value" => $feature->value
]);
}
// send the response back to client
echo json_encode([
"status" => "success",
"message" => "Pricing table has been updated."
]);
exit();
Now refresh the page and try to update any pricing table, you will see it will be changed in the database as well as on the user side. On the user side, you have to refresh the page to see the changes.
Delete
You already have a method named “deletePricing” in your Vue JS instance named “addPricingTableApp“. You just need to modify it as the following:
// method to delete pricing table
deletePricing: function () {
// get this app instance
var self = this;
// get form
var form = event.target;
// call an AJAX to delete the pricing table
var ajax = new XMLHttpRequest();
ajax.open("POST", "delete.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
console.log(response);
// remove from local array if deleted from server
if (response.status == "success") {
for (var a = 0; a < self.pricings.length; a++) {
var pricing = self.pricings[a];
if (pricing.id == form.id.value) {
self.pricings.splice(a, 1);
break;
}
}
} else {
// display an error message
alert(response.message);
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// append form in form data object
var formData = new FormData(form);
// call AJAX with form data
ajax.send(formData);
},
Now we need to create a file named “delete.php” and in this file, we will simply run the delete query.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// create a query to delete the pricing table from database
$sql = "DELETE FROM pricings WHERE id = :id";
// prepare the query
$result = $conn->prepare($sql);
// execute the query
$result->execute([
":id" => $_POST["id"]
]);
// send the response back to client
echo json_encode([
"status" => "success",
"message" => "Pricing table has been deleted."
]);
exit();
Refresh the page now on your admin side and hit the delete button. You will see the row will be deleted from an HTML table and also from the database. If you refresh the user side, it will be removed from there too.
So that’s how you can create a fully responsive dynamic pricing table on your website, which can be managed from the admin panel. You do not have to go into the code to make changes. If you face any problems in following this, please do let us know in the comments section below.
In this article, we are going to teach you, how you can forcefully log out a user in your Laravel website using PHP and MySQL. Usually when users once logged in to your website, can only be logged out by clicking a logout button where we will log out the user using the Laravel Auth class.
But there will be a situation where you want to forcefully log out a specific user. At the end of this article, you will be able to do so.
You can check this tutorial on YouTube as well, if you prefer a video based tutorial.
Let’s get started
We assume that you already have a Laravel project up and running. You might have a users table and you might also have some user records saved in it.
First, you need to create a new column in your users table. That column will save the boolean value indicating whether the user has to be forcefully logged out or not ?
Add column in user’s table
Run the following command in your terminal at the root of your project:
This will create a file named {time}_add_column_force_logout_in_users_table.php in “database/migrations” folder. Open that file and it will have the following content:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class AddColumnForceLogoutInUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
// add a column wiht a data type 'boolean' with a default value 0
Schema::table('users', function (Blueprint $table) {
$table->boolean("force_logout")->after("password")->default(0);
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::table('users', function (Blueprint $table) {
//
});
}
}
Now run the following command in your terminal to run this migration:
php artisan migrate
This will create a column named “force_logout” in your users table after the “password” column. Its data type will be boolean and its default value will be 0 which means FALSE.
You also need to add this column in your “App\Models\User.php” $fillable array, like this:
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name',
'email',
'password',
"force_logout", // add column in user model too
];
Now we need to create a controller (if you do not already have one). Run the following command in your terminal:
php artisan make:controller UsersController
This will create your controller in “app/Http/Controllers” folder with a file named “UsersController.php”. We will come back to this in a moment. First open your “routes/web.php” and create the following routes in it:
// include controller
use App\Http\Controllers\UsersController;
// called when the user is marked to be logged out forcefully
Route::post("/force-logout", [UsersController::class, "force_logout"]);
// to show all users
Route::get("/users", [UsersController::class, "users"]);
// user side home page
Route::get('/', [UsersController::class, "index"]);
Now open your UsersController class and first you need to include the Auth library and User model at the top.
// used to perform actions on logged in user
use Auth;
// get user model class
use App\Models\User;
Then we need to create a method to show all users.
// get all users
public function users()
{
// fetch all users sorted by newest to oldest with pagination
$users = User::orderBy("id", "desc")->paginate();
// render the page 'users' and pass the $users array in it
return view("users", [
"users" => $users
]);
}
Now create a file named “users.blade.php” in your “resources/views” folder. Write the following code in it:
<!-- table to display all users -->
<table>
<!-- table heading -->
<tr>
<th>Name</th>
<th>Email</th>
<th>Action</th>
</tr>
<!-- loop through all users -->
@foreach ($users as $user)
<tr>
<!-- show user name and email -->
<td>{{ $user->name }}</td>
<td>{{ $user->email }}</td>
<td>
<!-- form which when submit will mark the user as to be forcefully log out -->
<form method="POST" action="{{ url('/force-logout') }}" onsubmit="return confirm('Are you sure you want to forcefully logout this user ?');">
<!-- CSRF protection token -->
{{ csrf_field() }}
<!-- user ID in a hidden input field, will be submitted with the form -->
<input type="hidden" name="id" value="{{ $user->id }}" required />
<!-- submit button -->
<input type="submit" value="Force Logout" />
</form>
</td>
</tr>
@endforeach
</table>
<!-- show pagination links -->
{{ $users->links() }}
<!-- minor stylings on the table -->
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
th, td {
padding: 15px;
}
</style>
This will show the user name, email, and a button “Force Logout”. On clicking that button, it will ask for confirmation. Once confirmed, the form will be submitted. Now we need to handle this form submission.
Force logout any user you select
We already have the route for this, just need to create another method in your controller.
// mark user as to be logged out
public function force_logout()
{
// make sure the ID exists in users table
request()->validate([
"id" => "required|exists:users,id"
]);
// set the 'force_logout' column value as 1 of selected user
User::where("id", "=", request()->id)
->update([
"force_logout" => 1
]);
// redirect the user back to previous page
return redirect()->back();
}
This will first check if the user exists in the database. If yes, then it will update that user’s force_logout column value to 1 which means TRUE.
Finally, we need to log out the user if this column has a TRUE value. So create another method in your controller:
// home page for the user
public function index()
{
// check if user is logged in and is to be logged out forcefully
if (Auth::check() && Auth::user()->force_logout)
{
// since the user will be logged out now, set the value to 0 again
Auth::user()->force_logout = 0;
// update the 'force_logout' value in the database
Auth::user()->save();
// logout the user session
Auth::logout();
}
// display home page, Auth functions will not work now
return view("welcome");
}
You can write this code on your home page or in the constructor of your controller. The next time that the user accesses that page, he will be logged out.
In this tutorial, we are going to teach you how can you show instagram feed in your PHP website without using any API.
Video tutorial:
First, we need to create a table in our database where we will save all the latest posts from our Instagram page or profile.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");
// create table to store latest instagram feeds
$sql = "CREATE TABLE IF NOT EXISTS instagram (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
url TEXT DEFAULT NULL,
image_path TEXT DEFAULT NULL,
video_path TEXT DEFAULT NULL,
caption TEXT DEFAULT NULL,
likes TEXT DEFAULT NULL,
comments TEXT DEFAULT NULL
)";
$result = $conn->prepare($sql);
$result->execute();
[handle form submission]
?>
[form goes here]
This will create a table named “Instagram” in your database named “test”. The table will have the following columns:
id (primary key)
URL shortcode of Instagram post
Path of the image stored in your server
Path of video stored in your server
Caption (if any) of post
Number of likes on a post
Number of comments on a post
Saving Instagram Feed in MySQL using PHP
Now you need to show a form that will show a link that you need to open in your browser. That link will output the JSON string, you need to copy-paste that JSON string into a textbox in the form.
The following code goes in the [form goes here] section:
<form method="POST" action="index.php">
<p>
<a href="https://www.instagram.com/{your_page_name}/?__a=1">Goto this link and paste the JSON string in textbox below</a>
</p>
<p>
<label>Paste JSON</label>
<textarea name="json" rows="10" cols="30" required></textarea>
</p>
<input type="submit" name="submit" class="Save" />
</form>
[show instagram feed]
Refresh the page and you will see a link, a textbox, and a submit button. Click on the link and you will see a JSON string, paste that JSON in the textbox and hit submit. Now we need to write the code to save that JSON in our database.
The following code goes in the [handle form submission] section:
// check if form is submitted
if (isset($_POST["submit"]))
{
// get JSON from textarea
$json = $_POST["json"];
// decode JSON into arrays and objects
$content = json_decode($json);
// get all the latest posts
$edges = $content->graphql->user->edge_owner_to_timeline_media->edges;
mkdir("instagram-media");
// delete previous posts from our database
$sql = "DELETE FROM instagram";
$result = $conn->prepare($sql);
$result->execute();
// loop through all posts
foreach ($edges as $edge)
{
// get single post
$node = $edge->node;
// get URL shortcode of post
$url = $node->shortcode;
// get caption, if any
$caption = $node->edge_media_to_caption->edges[0]->node->text;
// get number of likes
$likes = $node->edge_liked_by->count;
// get total number of comments
$comments = $node->edge_media_to_comment->count;
// save image in our server if uploaded
$image_path = "";
if (!is_null($node->display_url))
{
$image_path = "instagram-media/" . $url . ".png";
file_put_contents($image_path, file_get_contents($node->display_url));
}
// save video in our server if uploaded
$video_path = "";
if (!is_null($node->video_url))
{
$video_path = "instagram-media/" . $url . ".mp4";
file_put_contents($video_path, file_get_contents($node->video_url));
}
// insert in database
$sql = "INSERT INTO instagram(url, image_path, video_path, caption, likes, comments) VALUES (:url, :image_path, :video_path, :caption, :likes, :comments)";
$result = $conn->prepare($sql);
$result->execute([
":url" => $url,
":image_path" => $image_path,
":video_path" => $video_path,
":caption" => $caption,
":likes" => $likes,
":comments" => $comments
]);
}
echo "<p>Done</p>";
}
[fetch all instagram feeds from database]
This will fetch the latest posts from your instagram page or profile. If there is any image or video attached to it, it will be saved in a folder named “instagram-media”. And it’s path will be stored in the database. If all goes well, it will display a message “Done”.
Fetching Instagram Feed from MySQL via PHP
Now we can easily fetch all latest instagram feeds from our database. Replace the section [fetch all instagram feeds from database] with the following code:
// get all posts from database
$sql = "SELECT * FROM instagram ORDER BY id ASC";
$result = $conn->query($sql);
$instagram_feed = $result->fetchAll();
Finally, we need to show them in our HTML. Replace the section [show instagram feed] with the following code:
<main>
<div class="container">
<div class="gallery">
<!-- loop through all rows from database -->
<?php foreach ($instagram_feed as $feed): ?>
<div class="gallery-item" tabindex="0">
<!-- wrap with anchor tag, when clicked will go to instagram detail post page -->
<a href="https://www.instagram.com/p/<?php echo $feed['url']; ?>" target="_blank" style="color: white;">
<!-- thumbnail of post -->
<img src="<?php echo $feed['image_path']; ?>" class="gallery-image" />
<div class="gallery-item-info">
<ul>
<!-- show no. of likes -->
<li class="gallery-item-likes">
<i class="fa fa-heart"></i>
<?php echo $feed["likes"]; ?>
</li>
<!-- show no. of comments -->
<li class="gallery-item-comments">
<i class="fa fa-comment"></i>
<?php echo $feed["comments"]; ?>
</li>
</ul>
<!-- show caption -->
<p><?php echo $feed["caption"]; ?></p>
</div>
</a>
</div>
<?php endforeach; ?>
</div>
</div>
</main>
<!-- style CSS -->
<link rel="stylesheet" type="text/css" href="instagram.css?v=<?php echo time(); ?>" />
<!-- font awesome -->
<script src="https://use.fontawesome.com/b8680c3f3d.js"></script>
Comments has been added with each line for explanation. Each post will be wrapped in an anchor tag, so when you clicked on it, you will be redirected to the post’s page on instagram.
Styles
Last thing you are going to need is CSS styles. So create a file named “instagram.css” and paste the following code in it:
If you refresh the page now, you will be able to view all the posts from your database in a grid format. There will be 3 posts in each row. Now you have all the code with the style, you can customize it as per your needs.
So that’s how you can show your Instagram feed without API using simple PHP and MySQL.
In this tutorial, we are going to teach you how you can implement events and listeners in Laravel. Events are created and multiple listeners can be assigned to them. Listeners are responsible for performing an action like saving the data in the database, sending an email, etc. In your controllers, you only need to emit an event and all the listeners associated with it will be called automatically.
Video tutorial:
Creating an event
First, we are going to create an event. Open terminal or command prompt at the root of your Laravel project and run the following command:
php artisan make:event MyEvent
A new file will be created at app/Events/MyEvent.php. Now place all the variables in it that you will be passed while calling this event.
<?php
namespace App\Events;
use Illuminate\Broadcasting\Channel;
use Illuminate\Broadcasting\InteractsWithSockets;
use Illuminate\Broadcasting\PresenceChannel;
use Illuminate\Broadcasting\PrivateChannel;
use Illuminate\Contracts\Broadcasting\ShouldBroadcast;
use Illuminate\Foundation\Events\Dispatchable;
use Illuminate\Queue\SerializesModels;
class MyEvent
{
use Dispatchable, InteractsWithSockets, SerializesModels;
public $my_value;
/**
* Create a new event instance.
*
* @return void
*/
public function __construct($my_value)
{
$this->my_value = $my_value;
}
/**
* Get the channels the event should broadcast on.
*
* @return \Illuminate\Broadcasting\Channel|array
*/
public function broadcastOn()
{
return new PrivateChannel('channel-name');
}
}
Creating a Listener
Now we need to create a listener that will be called when the above event is emitted. Open the terminal and run the following command in it:
php artisan make:listener MyListener
A new file will be created at app/Listeners/MyListener.php. It will have the following content:
<?php
namespace App\Listeners;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Queue\InteractsWithQueue;
class MyListener
{
/**
* Create the event listener.
*
* @return void
*/
public function __construct()
{
//
}
/**
* Handle the event.
*
* @param object $event
* @return void
*/
public function handle($event)
{
dd($event->my_value);
}
}
That $event variable will hold all the values sent from the event class (MyEvent, in this case).
Attach Listeners to Event
The event and listeners are created. Now is the time to tell the Laravel framework to which listeners should be called when a specific event is called. Goto app/Providers/EventServiceProvider.php and include the event and listener at the top:
use App\Events\MyEvent;
use App\Listeners\MyListener;
Then update the $listen array in it:
/**
* The event listener mappings for the application.
*
* @var array
*/
protected $listen = [
MyEvent::class => [
MyListener::class
]
];
Now, whenever a MyEvent event is emitted, the MyListener listener will automatically be called.
Emitting an Event
Emitting an event in your Laravel project is very easy. You just need to include your event at the top:
use App\Events\MyEvent;
Then whenever you need to call it, you can simply call it like this:
event(new MyEvent("adnan-tech.com"));
At this point, the event will be emitted and the listener will be called. But we need to find a way to check if the listener is actually being called. We can simply do dd() inside listener’s handle() function.
Sockets are used for real-time communication. They are now being used in chat apps, team collaboration tools, and many more. Socket IO emit events to the receivers and the receivers are constantly listening to that event. When the event is received on the client-side, they can perform the necessary action. You can attach as many event listeners as you want and perform different actions for each event.
Users are connected with a Node JS server using a client-side library called Socket IO. Users can also join the room which will be helpful if you are creating a group chat app. There are 4 ways in which socket events are fired.
Send event to all connected users, including the sender.
Send event to all users, except the sender.
Emit event to all users in a room.
Send event to specific users.
In this tutorial, we will be covering the 4th part i.e. send socket events to specific users.
Video tutorial:
Problem
Suppose you have a chat app where you want 2 people to have a private chat. Now you want to have a real-time effect i.e. to show the new messages without having to refresh the page. This requires sockets that send the data in real-time. And we can show the data on the client-side in real-time too. Now when a sender sends a message to a specific user, we need to send the socket event to that specific user only.
Solution
We will create a simple script that allows us to send events to a specific user only. You can then integrate and customize that logic in your project. First, you need to download and install Node JS. You also need to download the Socket IO JS client-side library. We will have a simple database from where we can show all the users in a list, with a button to send an event to that user only. So we need to create a database with a simple users table, you can use your own database as well.
Database
In your phpMyAdmin, create a database named “send_socket_event_to_specific_users”. In that database, create a users table:
CREATE TABLE `users` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` text NOT NULL
);
Add few rows in that table so we can show them in a list or a table.
It will show all users in a table with a button to send message. Now when the page loads, we need to get the ID of the user, you can also get it from PHP sessions.
Include Socket IO library
Before that, we need to include the Socket IO JS library. You can download it from here.
<script src="socket.io.js"></script>
<script>
var userId = prompt("Enter user ID");
var socketIO = io("http://localhost:3000");
socketIO.emit("connected", userId);
</script>
It will store your ID in userId variable. And it will connect with Node JS server and emit an event “connected” with your ID.
Now we need to create a simple Node server. Create an empty folder and create a file named “server.js” in it. Then open the CMD in that folder and run the following commands one-by-one:
var express = require("express");
var app = express();
var http = require("http").createServer(app);
var socketIO = require("socket.io")(http, {
cors: {
origin: "*"
}
});
var users = [];
socketIO.on("connection", function (socket) {
socket.on("connected", function (userId) {
users[userId] = socket.id;
});
// socket.on("sendEvent") goes here
});
http.listen(process.env.PORT || 3000, function () {
console.log("Server is started.");
});
This will start the server at port 3000, creates a users array and store all connected user’s socket ID in it.
Send event using socket IO emit function
Back in index.php, we need to create a JS function to send the event when the user click the “Send message” button:
function sendEvent(form) {
event.preventDefault();
var message = prompt("Enter message");
socketIO.emit("sendEvent", {
"myId": userId,
"userId": form.id.value,
"message": message
});
}
Now in server.js, we need to listen to that event and send the message to that user only. But before that, we need to include mysql module because the user’s names are stored in mysql database. At the top of your server.js:
var mysql = require("mysql");
var connection = mysql.createConnection({
host: "localhost",
port: 3306,
user: "root",
password: "",
database: "send_socket_event_to_specific_users"
});
connection.connect(function (error) {
console.log("Database connected: " + error);
});
And after the socket connected event:
socket.on("sendEvent", async function (data) {
connection.query("SELECT * FROM users WHERE id = " + data.userId, function (error, receiver) {
if (receiver != null) {
if (receiver.length > 0) {
connection.query("SELECT * FROM users WHERE id = " + data.myId, function (error, sender) {
if (sender.length > 0) {
var message = "New message received from: " + sender[0].name + ". Message: " + data.message;
socketIO.to(users[receiver[0].id]).emit("messageReceived", message);
}
});
}
}
});
});
This will search the sender and receiver by ID, and emit the event to the receiver with the name of the sender.
Listen to socket IO events
Now we need to listen to that event in our index.php and show a message in a list when that event is received. First, create a ul where all messages will be displayed:
<ul id="messages"></ul>
Then attach that event in JS:
socketIO.on("messageReceived", function (data) {
var html = "<li>" + data + "</li>";
document.getElementById("messages").innerHTML = html + document.getElementById("messages").innerHTML;
});
So that’s how you can use the socket IO emit function to send the event to a specific user only.
Check out realtime chat app tutorial using socket IO.
In this tutorial, we are going to show you, how you can email a download link of a file to a user when they request to download files from your website. We are going to create a system where you can upload files and the user will be able to download them. But before downloading, they must enter their email address. The link to download the file will be emailed to the user. In this way, you will get a large number of emails. This will help you with a very large email list.
Prevent direct access to files
First, we are going to prevent users from downloading the files directly from the URL. We will be storing all our uploaded files in the “uploads” folder. So, create a new folder named “uploads” at the root of your project. Create a “.htaccess” file in this folder. In this file, write the following single line:
deny from all
This will gives a “403 Forbidden” error whenever someone tries to access the file directly from the browser.
Upload files
Now we are going to create a form that will allow you to upload files. You can create this form in your admin panel because usually, the administrator of the website can upload files.
Create a database named “collect_emails_while_downloading_files” in your phpMyAdmin. Or you can use your own database if you already have one. In this database, you need to create a table where we will store the path and name of all uploaded files.
CREATE TABLE files (
id INTEGER(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
file_name TEXT NOT NULL,
file_path TEXT NOT NULL
);
Then we need to save the selected file in the uploads folder and its path in the files table in the MySQL database. We will be using PHP PDO prepared statements that will help us from preventing the SQL injection.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost:8889;dbname=collect_emails_while_downloading_files", "root", "root");
// check if form is submitted, for admin panel only
if (isset($_POST["upload"]))
{
// get the file
$file = $_FILES["file"];
// make sure it does not have any error
if ($file["error"] == 0)
{
// save file in uploads folder
$file_path = "uploads/" . $file["name"];
move_uploaded_file($file["tmp_name"], $file_path);
// save file path in database, prevent SQL injection too
$sql = "INSERT INTO files(file_name, file_path) VALUES (:file_name, :file_path)";
$result = $conn->prepare($sql);
$result->execute([
":file_name" => $file["name"],
":file_path" => $file_path
]);
}
else
{
die("Error uploading file.");
}
}
// get all files
$sql = "SELECT * FROM files ORDER BY id DESC";
$result = $conn->query($sql);
$files = $result->fetchAll();
?>
Refresh the page and try uploading a file. You will see it will be saved in your uploads folder and its path and name will be stored in the files table. Also, try accessing that from the browser directly, it will give you a 403 Forbidden error.
Show all uploaded files
In the previous step, we ran a query to fetch all files sorting from latest to oldest. Now we need to show them on a table.
This will show all files in a table with a button to download. When that button is clicked, we need to get the user’s email address so we can email him a download link for that file. That link will be valid for that file and for that email only.
<script>
function onFormSubmit(form) {
// get email address and submit
var email = prompt("Enter your email:", "");
if (email != null && email != "") {
form.email.value = email;
return true;
}
return false;
}
</script>
Send download link in email
We will be using the PHPMailer library to send emails. Open CMD at the root folder of your project and run the following command. Make sure you have the composer downloaded and installed in your system.
composer require phpmailer/phpmailer
Create a table in your database that will store all the download requests of files sent by users. Run the following query in your database in phpMyAdmin:
CREATE TABLE download_requests (
id INTEGER(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
file_id INTEGER(11) NOT NULL,
email TEXT NOT NULL,
token TEXT NOT NULL,
CONSTRAINT fk_file_id FOREIGN KEY (file_id) REFERENCES files (id) ON DELETE CASCADE ON UPDATE CASCADE
);
Create a file named “check-email.php” and write the following code in it. It will send the email to the user and also will store the data in the above created table.
<?php
// composer require phpmailer/phpmailer
// include PHPMailer library
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;
require 'vendor/autoload.php';
// connect with database
$conn = new PDO("mysql:host=localhost:8889;dbname=collect_emails_while_downloading_files", "root", "root");
// get all form values
$id = $_POST["id"];
$email = $_POST["email"];
// generate a unique token for this email only
$token = time() . md5($email);
// get file from database
$sql = "SELECT * FROM files WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":id" => $id
]);
$file = $result->fetch();
if ($file == null)
{
die("File not found");
}
// insert in download requests, prevent SQL injection too
$sql = "INSERT INTO download_requests(file_id, email, token) VALUES (:id, :email, :token)";
$result = $conn->prepare($sql);
$result->execute([
":id" => $id,
":email" => $email,
":token" => $token
]);
// send email to user
$mail = new PHPMailer(true);
try
{
$mail->SMTPDebug = 0;
$mail->isSMTP();
$mail->Host = 'smtp.gmail.com';
$mail->SMTPAuth = true;
$mail->Username = 'your_email@gmail.com';
$mail->Password = 'your_password';
$mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
$mail->Port = 587;
$mail->setFrom('adnan@gmail.com', 'Adnan');
$mail->addAddress($email); // Add a recipient
$mail->addReplyTo('adnan@gmail.com', 'Adnan');
// Content
$mail->isHTML(true);
$mail->Subject = 'Download your files';
// mention download link in the email
$email_content = "Kindly click the link below to download your files: <br />";
$base_url = "http://localhost:8888/tutorials/collect-emails-while-downloading-files-php-mysql";
$email_content .= "<a href='" . $base_url . "/download.php?email=" . $email . "&token=" . $token . "'>" . $file['file_name'] . "</a>";
$mail->Body = $email_content;
$mail->send();
echo '<p>Link to download files has been sent to your email address: ' . $email . '</p>';
}
catch (Exception $e)
{
die("Message could not be sent. Mailer Error: " . $mail->ErrorInfo);
}
Make sure to change the base URL at line 68. Also, change your email address and password on lines 53 and 54 respectively. This email will be used to send the emails. Goto this link and enable a less secure apps option for that email address.
Enable less secure apps – Gmail
Test the code now. You will be able to see a list of all uploaded files with a button to download. When clicked, it will show a prompt where you can enter your email address. When clicked “OK”, it will send an email with a download link and also it will store it in the “download_requests” table in the MySQL database.
In your email, you will see a link to download the file. But right now it will give a 404 Not found error because the file is not created yet.
Download the file from email download link
Create a file named “download.php” and write the following code in it. This will directly download the file into your system.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost:8889;dbname=collect_emails_while_downloading_files", "root", "root");
// get variables from email
$email = $_GET["email"];
$token = $_GET["token"];
// check if the download request is valid
$sql = "SELECT *, download_requests.id AS download_request_id FROM download_requests INNER JOIN files ON files.id = download_requests.file_id WHERE download_requests.email = :email AND download_requests.token = :token";
$result = $conn->prepare($sql);
$result->execute([
":email" => $email,
":token" => $token
]);
$file = $result->fetch();
if ($file == null)
{
die("File not found.");
}
// download the file
$url_encoded_file_name = rawurlencode($file["file_name"]);
$file_url = "http://localhost:8888/tutorials/collect-emails-while-downloading-files-php-mysql/uploads/" . $url_encoded_file_name;
// die($file_url);
// headers to download any type of file
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="' . $file["file_name"] . '"');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($file["file_path"]));
readfile($file["file_path"]);
Make sure to change your base URL at line 26. Now you can run a complete test cycle again. Upload a file, click the download button, and enter your email. Check your email and then click the download link from the email and the file will be downloaded. Verify the file that is downloaded correctly.
So that’s how you can collect a large number of emails by allowing people to simply download files. You can create a very large email list from it.
Learn how to send attachment with an email using PHP.
