Simple Feedback System With PHP MySQL (Free Download)

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!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

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) : void {
    $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.

 

 

 

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 the feedback_users table to VARCHAR.
• 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 the feedback_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