Welcome to a tutorial on how to create a feedback system with PHP and MySQL. So you want to collect some feedback or do a quick survey from your users? Here is a quick sharing of my simple build – Read on!
ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.
TABLE OF CONTENTS
DOWNLOAD & NOTES
Firstly, here is the download link to the example code as promised.
QUICK NOTES
- Create a database and import
1-feedback.sql
. - Change the database settings in
2-feedback-lib.php
to your own. - Access
3-dummy.php
to generate a dummy feedback form. - Access
4-feedback-page.php
in your browser.
SCREENSHOT
EXAMPLE CODE DOWNLOAD
Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
PHP MYSQL FEEDBACK SYSTEM
All right, let us now get into the details of building a feedback system using PHP MySQL.
PART 1) THE DATABASE
1A) FEEDBACK TABLE
-- (A) FEEDBACK
CREATE TABLE `feedback` (
`feedback_id` bigint(20) NOT NULL,
`feedback_title` varchar(255) NOT NULL,
`feedback_desc` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `feedback`
ADD PRIMARY KEY (`feedback_id`);
ALTER TABLE `feedback`
MODIFY `feedback_id` bigint(20) NOT NULL AUTO_INCREMENT;
First, we have the “main feedback table”. Self-explanatory:
feedback_id
Running number, primary key.feedback_title
Title of the feedback. For example, “XYZ Course Feedback”.feedback_desc
Description, optional.
1B) FEEDBACK QUESTIONS TABLE
-- (B) FEEDBACK QUESTIONS
CREATE TABLE `feedback_questions` (
`feedback_id` bigint(20) NOT NULL,
`question_id` bigint(20) NOT NULL,
`question_text` text NOT NULL,
`question_type` varchar(1) NOT NULL DEFAULT 'R'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `feedback_questions`
ADD PRIMARY KEY (`feedback_id`,`question_id`);
Questions for the feedback form.
feedback_id
Composite primary and foreign key.question_id
Composite primary key, this is a manual running number.question_text
The question itself. For example, “Are the course materials sufficient”?question_type
An open field to specify the type of feedback. For this example:R
Rating (1 to 5).O
Open text field.
Yes, feel free to invent your own question_type
as required. Maybe a S
for a “select one from the list”.
1C) USER FEEDBACK TABLE
-- (C) FEEDBACK FROM USERS
CREATE TABLE `feedback_users` (
`user_id` bigint(20) NOT NULL,
`feedback_id` bigint(20) NOT NULL,
`question_id` bigint(20) NOT NULL,
`feedback_value` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `feedback_users`
ADD PRIMARY KEY (`user_id`,`feedback_id`,`question_id`);
Lastly, a table to record the feedback from the users.
user_id
Composite primary and foreign key, ties back to your existing user database. See the “extras” section below, if you don’t have a user system or want to open it for public feedback.feedback_id
Composite primary and foreign key.question_id
Composite primary and foreign key.feedback_value
An open field to record the feedback value. Should be a number for rating, text for an open field.
PART 2) PHP LIBRARY
2A) LIBRARY MECHANICS
<?php
class Feedback {
// (A) CONSTRUCTOR - CONNECT TO DATABASE
private $pdo = null;
private $stmt = null;
public $error = "";
function __construct () {
$this->pdo = new PDO(
"mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
DB_USER, DB_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
}
// (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
function __destruct () {
if ($this->stmt!==null) { $this->stmt = null; }
if ($this->pdo!==null) { $this->pdo = null; }
}
// (C) SUPPORT FUNCTION - SQL QUERY
function query ($sql, $data=null) {
$this->stmt = $this->pdo->prepare($sql);
$this->stmt->execute($data);
}
}
// (G) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
// (H) NEW FEEDBACK OBJECT
$FEED = new Feedback();
To handle all the mechanics of the Feeback system, we will create a class Feedback
.
- (A, B, H) When
$FEED = new Feedback
is created, the constructor will automatically connect to the database. The destructor closes the connection. - (C)
function query()
is a simple support function to run an SQL query. - (G) Remember to change the database settings to your own.
2B) ADMIN FUNCTIONS
// (D) SAVE FEEDBACK
function save ($title, $questions, $desc=null, $id=null) {
// (D1) AUTO-COMMIT OFF
$this->pdo->beginTransaction();
// (D2) UPDATE/INSERT FEEDBACK
if ($id==null) {
$sql = "INSERT INTO `feedback` (`feedback_title`, `feedback_desc`) VALUES (?,?)";
$data = [$title, $desc];
} else {
$sql = "UPDATE `feedback` SET `feedback_title`=?, `feedback_desc`=? WHERE `feedback_id`=?";
$data = [$title, $desc, $id];
}
$this->query($sql, $data);
if ($id==null) { $id = $this->pdo->lastInsertId(); }
// (D3) DELETE OLD QUESTIONS
$this->query("DELETE FROM `feedback_questions` WHERE `feedback_id`=?", [$id]);
// (D4) ADD QUESTIONS
$sql = "INSERT INTO `feedback_questions` (`feedback_id`, `question_id`, `question_text`, `question_type`) VALUES ";
$data = [];
foreach ($questions as $qid=>$q) {
$sql .= "(?,?,?,?),";
$data[] = $id; $data[] = $qid + 1;
$data[] = $q[0]; $data[] = $q[1];
}
$sql = substr($sql, 0, -1) . ";";
$this->query($sql, $data);
// (D5) COMMIT
$this->pdo->commit();
return true;
}
// (E) GET FEEDBACK QUESTIONS
function get ($id, $user=false) {
// (E1) GET QUESTIONS
$this->query("SELECT * FROM `feedback_questions` WHERE `feedback_id`=?", [$id]);
$results = [];
while ($row = $this->stmt->fetch()) {
$results[$row["question_id"]] = [
"question_text" => $row["question_text"],
"question_type" => $row["question_type"]
];
}
// (E2) INCLUDE USER FEEDBACK
if ($user==true) { foreach ($results as $qid=>$q) {
$sql = "FROM `feedback_users` WHERE `feedback_id`=? AND `question_id`=?";
// (E2-1) AVERAGE RATING
if ($q["question_type"]=="R") {
$this->query("SELECT AVG(`feedback_value`) $sql", [$id, $qid]);
$results[$qid]["feedback_value"] = $this->stmt->fetchColumn();
}
// (E2-2) OPEN FIELD
else {
$results[$qid]["feedback_value"] = [];
$this->query("SELECT `feedback_value` $sql", [$id, $qid]);
while ($row = $this->stmt->fetch()) {
$results[$qid]["feedback_value"][] = $row["feedback_value"];
}
}
}}
// (E3) RESULTS
return $results;
}
// (F) SAVE USER FEEDBACK
function saveuser ($uid, $fid, $feed) {
$sql = "REPLACE INTO `feedback_users` (`user_id`, `feedback_id`, `question_id`, `feedback_value`) VALUES ";
$data = [];
foreach ($feed as $qid=>$val) {
$sql .= "(?,?,?,?),";
$data[] = $uid; $data[] = $fid;
$data[] = $qid; $data[] = $val;
}
$sql = substr($sql, 0, -1) . ";";
$this->query($sql, $data);
return true;
}
The admin functions may look confusing, but keep calm and look carefully. These are nothing more than SQL queries and do data Yoga.
- (D)
save()
Save a new feedback form, or update an existing one. - (E)
get()
Get specified feedback form and the questions. - (F)
saveuser()
Save user feedback.
PART 3) DUMMY DATA & USAGE EXAMPLE
<?php
require "2-feedback-lib.php";
echo $FEED->save("XYZ Course Feedback", [
["Are the course materials sufficient?", "R"],
["How likely are you to recommend this course to friends?", "R"],
["Any other feedback on the course?", "O"]
], "Optional description")
? "OK" : $FEED->error;
How do we use the library to create or update a feedback form? Just use $FEED->save()
.
PART 4) DUMMY USER FEEDBACK PAGE
<?php
// (A) "SETTINGS"
// FIXED NUMBERS FOR THIS TUTORIAL
$uid = 999; // user id
$fid = 1; // feedback id
// (B) LOAD FEEDBACK LIBRARY
require "2-feedback-lib.php";
// (C) OUTPUT HTML ?>
<!DOCTYPE html>
<html>
<head>
<title>Feedback Form</title>
<link rel="stylesheet" href="4-feedback-page.css">
</head>
<body>
<?php
// (C1) SAVE USER FEEDBACK
if (count($_POST)>0) {
echo $FEED->saveuser($uid, $fid, $_POST["ans"]) ? "OK" : $FEED->error;
}
// (C2) SHOW FEEDBACK QUESTIONS
else { $questions = $FEED->get($fid); ?>
<form method="post" class="feed-form">
<?php foreach ($questions as $qid=>$q) { ?>
<!-- (C2-1) QUESTION -->
<div class="feed-qn"><?=$q["question_text"]?></div>
<!-- (C2-2) ANSWER -->
<?php if ($q["question_type"]=="R") { ?>
<div class="feed-r">
<?php for ($i=1; $i<=5; $i++) { ?>
<input type="radio" name="ans[<?=$qid?>]" value="<?=$i?>"<?=$i==3?" checked":""?>>
<?php } ?>
</div>
<?php } else { ?>
<input type="text" name="ans[<?=$qid?>]" class="feed-o" required>
<?php } ?>
<?php } ?>
<input type="submit" value="Save" class="feed-go">
</form>
<?php } ?>
</body>
</html>
With the library in place, we can now build the feedback page relatively easily. For beginners, this page can be generalized into 3 sections:
- (A & B) Initialize stage, do your own system stuff. Then specify the feedback ID, and load the feedback library.
- (C2) Get the feedback form from the database, and show it in HTML.
- (C1) Save the form when it is submitted.
PART 5) DUMMY ADMIN
<?php
require "2-feedback-lib.php";
$res = $FEED->get(1, true);
print_r($res);
Lastly, we can reuse the get()
function to fetch all the feedback results. Show this in your HTML admin panel, or generate a downloadable report with it.
EXTRA BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
NOT A “COMPLETE SYSTEM”
Of course, this system is not complete, it is missing:
- User System – Registration and login.
- Admin – A backend to manage the feedback, and reports.
I figure that some of you guys may already have an existing system, no point reinventing the wheel. So if you are starting from scratch, things are going to be rough… I will leave some links below.
DO WE REALLY NEED A LOGIN & USER SYSTEM?
If you are planning to get feedback from the public, there’s a problem with spam and tracking. Here’s what I will recommend:
- Change
user_id
in thefeedback_users
table toVARCHAR
. - Generate a random unique ID, set it in an HTTP-only cookie to track the user.
- Use the random ID as the
user_id
for thefeedback_users
table. - Implement ReCaptcha to fight spam.
Of course, this is not perfect and you will not get accurate feedback. Users can clear their cookies and spam the feedback form multiple times… But with spam checks and a good firewall in place, the “damage” will be limited.
LINKS & REFERENCES
- Simple User Login System – Code Boxx
- Admin Panel – Code Boxx
- Set Cookie – PHP
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!