code-boxx/0-PHP-FEEDBACK.MD

## 0-PHP-FEEDBACK.MD

      
    Raw
  

              0-PHP-FEEDBACK.MD
            
          
    PHP MYSQL FEEDBACK SYSTEM

https://code-boxx.com/feedback-system-php-mysql/
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.

LICENSE

Copyright by Code Boxx
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

  
## 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=utf8mb4;

ALTER TABLE `feedback`
  ADD PRIMARY KEY (`feedback_id`);

ALTER TABLE `feedback`
  MODIFY `feedback_id` bigint(20) NOT NULL AUTO_INCREMENT;

-- (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`);

-- (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`);

## 2-feedback-lib.php
<?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);
  }

  // (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;
  }
}

// (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();

## 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;

## 4-feedback-page.css
* {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
}
.feed-form {
  max-width: 500px;
  padding: 20px;
  border: 1px solid #ddd;
  background: #f2f2f2;
}
.feed-qn {
  padding: 10px 0;
  font-weight: 700;
}
.feed-r, .feed-o {
  width: 100%;
  margin-bottom: 20px;
}
.feed-o {
  padding: 10px;
  border: 0;
}
.feed-go {
  padding: 10px 20px;
  font-weight: 700;
  border: 0;
  color: #fff;
  background: #006aee;
  cursor: pointer;
}

## 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>
    <meta charset="utf-8">
    <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>

## 5-admin.php
<?php
require "2-feedback-lib.php";
$res = $FEED->get(1, true);
print_r($res);
	-- (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;

	-- (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`);

	-- (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`);
	<?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);
	}

	// (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;
	}
	}

	// (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();
	<?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;
	* {
	font-family: Arial, Helvetica, sans-serif;
	box-sizing: border-box;
	}
	.feed-form {
	max-width: 500px;
	padding: 20px;
	border: 1px solid #ddd;
	background: #f2f2f2;
	}
	.feed-qn {
	padding: 10px 0;
	font-weight: 700;
	}
	.feed-r, .feed-o {
	width: 100%;
	margin-bottom: 20px;
	}
	.feed-o {
	padding: 10px;
	border: 0;
	}
	.feed-go {
	padding: 10px 20px;
	font-weight: 700;
	border: 0;
	color: #fff;
	background: #006aee;
	cursor: pointer;
	}
	<?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>
	<meta charset="utf-8">
	<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>
	<?php
	require "2-feedback-lib.php";
	$res = $FEED->get(1, true);
	print_r($res);