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!

ⓘ 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 Feedback System Useful Bits & Links
The End

 

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.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

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.

 

1) THE DATABASE

1A) FEEDBACK TABLE

1-feedback.sql
-- (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=latin1;
 
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

1-feedback.sql
-- (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=latin1;

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

1-feedback.sql
-- (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=latin1;

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.

 

 

2) PHP LIBRARY

2A) LIBRARY MECHANICS

2-feedback-lib.php
<?php
class Feedback {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    try {
      $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
      ]);
    } catch (Exception $ex) { exit($ex->getMessage()); }
  }
 
  // (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
  // $sql - sql to run
  // $data - data to bind
  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", "utf8");
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, G, H) When $FEED = new Feedback is created, the constructor will automatically connect to the database. Remember to change the settings to your own.
  • (B) When $FEED is destroyed, the destructor automatically closes the database connection.
  • (C) function query() is a simple support function to run an SQL query.

 

 

2B) ADMIN FUNCTIONS

2-feedback-lib.php
// (D) SAVE FEEDBACK
// $title : feedback title
// $questions : array of feedback questions
// $desc : feedback description (optional)
// $id : feedback id (for update only)
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
// $id : feedback id
// $user : also include user feedback? default false.
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
// $uid : user id
// $fid : feedback id
// $feed : array of feedback data
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.

 

3) DUMMY DATA & USAGE EXAMPLE

3-dummy.php
<?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().

 

 

4) DUMMY USER FEEDBACK PAGE

4-feedback-page.php
<?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, 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.

 

5) DUMMY ADMIN

5-admin.php
<?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.

 

 

USEFUL 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, 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

 

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!

Leave a Comment

Your email address will not be published.