Dynamic FAQ - PHP and MySQL

Dynamic FAQ – PHP and MySQL

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.

Table of content:

  1. Add FAQ from admin panel
  2. Display all FAQ to admin
  3. Edit any FAQ
  4. Delete FAQ
  5. Display FAQs on 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.

<!-- layout for form to add FAQ -->
<div class="container" style="margin-top: 50px; margin-bottom: 50px;">
	<div class="row">
		<div class="offset-md-3 col-md-6">
			<h1 class="text-center">Add FAQ</h1>

			<!-- for to add FAQ -->
			<form method="POST" action="add.php">

				<!-- question -->
				<div class="form-group">
					<label>Enter Question</label>
					<input type="text" name="question" class="form-control" required />
				</div>

				<!-- answer -->
				<div class="form-group">
					<label>Enter Answer</label>
					<textarea name="answer" id="answer" class="form-control" required></textarea>
				</div>

				<!-- submit button -->
				<input type="submit" name="submit" class="btn btn-info" value="Add FAQ" />
			</form>
		</div>
	</div>

	[show all FAQs here]
</div>

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:

<!-- show all FAQs added -->
<div class="row">
	<div class="offset-md-2 col-md-8">
		<table class="table table-bordered">
			<!-- table heading -->
			<thead>
				<tr>
					<th>ID</th>
					<th>Question</th>
					<th>Answer</th>
					<th>Actions</th>
				</tr>
			</thead>

			<!-- table body -->
			<tbody>
				<?php foreach ($faqs as $faq): ?>
					<tr>
						<td><?php echo $faq["id"]; ?></td>
						<td><?php echo $faq["question"]; ?></td>
						<td><?php echo $faq["answer"]; ?></td>
						<td>
							[edit button goes here]

							[delete button goes here]
						</td>
					</tr>
				<?php endforeach; ?>
			</tbody>
		</table>
	</div>
</div>

Refresh the page now and you will be able to view all the inserted questions sorting by newest to oldest.

3. Edit any FAQ

Following are the steps to update any specific row in the MySQL database using PHP PDO prepared statement:

  1. Show a link to go to edit page with each row
  2. Fetch the row data from MySQL using ID from URL
  3. Create a same form like add.php
  4. Auto-populate the values in the input fields
  5. Create a hidden input field for ID
  6. Update the data in database using ID
  7. Redirect to previous page

1. Show a link to go to edit page

Following code goes in the [edit button goes here] section:

<!-- edit button -->
<a href="edit.php?id=<?php echo $faq['id']; ?>" class="btn btn-warning btn-sm">
	Edit
</a>

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.

<!-- include 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 JS -->
<script src="js/jquery-3.3.1.min.js"></script>
<script src="js/bootstrap.js"></script>
<script src="richtext/jquery.richtext.js"></script>

<!-- layout for form to edit FAQ -->
<div class="container" style="margin-top: 50px; margin-bottom: 50px;">
	<div class="row">
		<div class="offset-md-3 col-md-6">
			<h1 class="text-center">Edit FAQ</h1>

			<!-- form to edit FAQ -->
			<form method="POST" action="edit.php">

				<!-- hidden ID field of FAQ -->
				<input type="hidden" name="id" value="<?php echo $faq['id']; ?>" required />

				<!-- question, auto-populate -->
				<div class="form-group">
					<label>Enter Question</label>
					<input type="text" name="question" class="form-control" value="<?php echo $faq['question']; ?>" required />
				</div>

				<!-- answer, auto-populate -->
				<div class="form-group">
					<label>Enter Answer</label>
					<textarea name="answer" id="answer" class="form-control" required><?php echo $faq['answer']; ?></textarea>
				</div>

				<!-- submit button -->
				<input type="submit" name="submit" class="btn btn-warning" value="Edit FAQ" />
			</form>
		</div>
	</div>
</div>

<script>
	// initialize rich text library
	window.addEventListener("load", function () {
		$("#answer").richText();
	});
</script>

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.

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 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.

<!-- include CSS -->
<link rel="stylesheet" type="text/css" href="css/bootstrap.css" />
<link rel="stylesheet" type="text/css" href="font-awesome/css/font-awesome.css" />

<!-- include JS -->
<script src="js/jquery-3.3.1.min.js"></script>
<script src="js/bootstrap.js"></script>

<!-- show all FAQs in a panel -->
<div class="container" style="margin-top: 50px; margin-bottom: 50px;">
	<div class="row">
		<div class="col-md-12 accordion_one">
		    <div class="panel-group">
		    	<?php foreach ($faqs as $faq): ?>
			        <div class="panel panel-default">

			        	<!-- button to show the question -->
			            <div class="panel-heading">
			                <h4 class="panel-title">
			                	<a data-toggle="collapse" data-parent="#accordion_oneLeft" href="#faq-<?php echo $faq['id']; ?>" aria-expanded="false" class="collapsed">
			                		<?php echo $faq['question']; ?>
			                	</a>
			                </h4>
			            </div>

			            <!-- accordion for answer -->
			            <div id="faq-<?php echo $faq['id']; ?>" class="panel-collapse collapse" aria-expanded="false" role="tablist" style="height: 0px;">
			                <div class="panel-body">
			                	<div class="text-accordion">
			                        <?php echo $faq['answer']; ?>
			                    </div>
			                </div>
			            </div>
			        </div>
		        <?php endforeach; ?>
		    </div>
		</div>
	</div>
</div>

Finally, you can apply some CSS styles to make it look better.

.accordion_one .panel-group {
    border: 1px solid #f1f1f1;
    margin-top: 100px
}
a:link {
    text-decoration: none
}
.accordion_one .panel {
    background-color: transparent;
    box-shadow: none;
    border-bottom: 0px solid transparent;
    border-radius: 0;
    margin: 0
}
.accordion_one .panel-default {
    border: 0
}
.accordion-wrap .panel-heading {
    padding: 0px;
    border-radius: 0px
}
h4 {
    font-size: 18px;
    line-height: 24px
}
.accordion_one .panel .panel-heading a.collapsed {
    color: #999999;
    display: block;
    padding: 12px 30px;
    border-top: 0px
}
.accordion_one .panel .panel-heading a {
    display: block;
    padding: 12px 30px;
    background: #fff;
    color: #313131;
    border-bottom: 1px solid #f1f1f1
}
.accordion-wrap .panel .panel-heading a {
    font-size: 14px
}
.accordion_one .panel-group .panel-heading+.panel-collapse>.panel-body {
    border-top: 0;
    padding-top: 0;
    padding: 25px 30px 30px 35px;
    background: #fff;
    color: #999999
}
.img-accordion {
    width: 81px;
    float: left;
    margin-right: 15px;
    display: block
}
.accordion_one .panel .panel-heading a.collapsed:after {
    content: "\2b";
    color: #999999;
    background: #f1f1f1
}
.accordion_one .panel .panel-heading a:after,
.accordion_one .panel .panel-heading a.collapsed:after {
    font-family: 'FontAwesome';
    font-size: 15px;
    width: 36px;
    line-height: 48px;
    text-align: center;
    background: #F1F1F1;
    float: left;
    margin-left: -31px;
    margin-top: -12px;
    margin-right: 15px
}
.accordion_one .panel .panel-heading a:after {
    content: "\2212"
}
.accordion_one .panel .panel-heading a:after,
.accordion_one .panel .panel-heading a.collapsed:after {
    font-family: 'FontAwesome';
    font-size: 15px;
    width: 36px;
    height: 48px;
    line-height: 48px;
    text-align: center;
    background: #F1F1F1;
    float: left;
    margin-left: -31px;
    margin-top: -12px;
    margin-right: 15px
}

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.

Source code:

Leave a Reply

Please disable your adblocker or whitelist this site!