Did you know there is a table in your MySQL database that holds all the schema for existing tables? Well, it can be extremely useful when it comes to tasks like this one.
There is a certain keyword that describes a full database table, and it’s called,describe surprisingly! In this tutorial, this special command will be utilized to automatically generate an HTML input form with the correct fields of a database’s table. Let’s get started.
Step 1 – Connect to your database
I’ve already assumed you’ve got a connection sorted, but if not, this is the code I used to connect to my local database using MySQLi.
$servername = "localhost";
$username = "codewall";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
Step 2 – Start a form HTML variable
After connecting to your database, create a new variable, this will be appended to throughout the script.
$form = "<form>";
Step 3 – Prepare your SQL
Next up is to prepare your query, this is where the describe command comes in, it actually holds a lot of information, but most of it is necessary for this use-case. For this example. the table used was a table named ‘people’ within the database named ‘codewalldb’, replace this with your own database and table.
$result = $conn->query("describe codewalldb.people");
Step 4 – Loop through the results and add to the form variable
Before we dive into the data, let’s see what a row from the result set actually looks like first –
array(6) { ["Field"]=> string(2) "id" ["Type"]=> string(15) "int(9) unsigned" ["Null"]=> string(2) "NO" ["Key"]=> string(3) "PRI" ["Default"]=> NULL ["Extra"]=> string(14) "auto_increment" }
As you can see here, there is a lot of fluff that we don’t need, and the actual value we do need is under the key of ‘Field’. This is the column name, which is perfect for using for labels and name attributes within forms.
Now add the following PHP
if ($result->num_rows > 0) {
// output data of each row
while ($row = $result->fetch_assoc()) {
if ($row["Field"] != "id") {
$form .= "<label for='" . $row["Field"] . "'>" . $row["Field"] . "</label> <input type='text' name='" . $row["Field"] . "' >";
}
}
$form .= "<input type='submit' value='Submit'></form>";
}
A quick explanation of the code above –
- Skip the id field, as we would want the database to auto-increment this value anyway.
- Add a
<label>tag with them for the attributes being the name of the field. - Add an
<input>tag with the name attribute being the name of the field. - Finally, close of the form with
</form>
Step 5 – Finally print the form variable to the page
Lastly, all is to do, is to echo out the form using the following PHP.
echo $form;
Step 6 – Load the page!
Load/run the script/page, if you’ve thought about this, you’ll of probably added some HTML around it also, like the following.
<!DOCTYPE html>
<html>
<head>
<title>My MySQL Form</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<?php
require 'helpers/connect.php';
$form = "<form>";
$result = $conn->query("describe codewalldb.people");
if ($result->num_rows > 0) {
// output data of each row
while ($row = $result->fetch_assoc()) {
if ($row["Field"] != "id") {
$form .= "<label for='" . $row["Field"] . "'>" . $row["Field"] . "</label> <input type='text' name='" . $row["Field"] . "' >";
}
}
$form .= "<input type='submit' value='Submit'></form>";
}
echo $form;
?>
</body>
</html>
This page should look something like the following image –

Step 7 – Add some styling
All really is left to do in terms of visual styling is to add a little Bootstrap to finish this demo off. I’ve used a CDN to grab the Bootstrap CSS and added some classes to the HTML elements. In addition, added a form-control div around each input and its label.
Here is what it looks like now –

And here is the full and final code –
<!DOCTYPE html>
<html>
<head>
<title>My MySQL Form</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css">
</head>
<body>
<?php
require 'helpers/connect.php';
$form = "<form>";
$result = $conn->query("describe codewalldb.people");
if ($result->num_rows > 0) {
// output data of each row
while ($row = $result->fetch_assoc()) {
if ($row["Field"] != "id") {
$form .= "<div class='form-group'>";
$form .= "<label for='" . $row["Field"] . "'>" . $row["Field"] . "</label> <input type='text' class='form-control' name='" . $row["Field"] . "' >";
$form .= "</div>";
}
}
$form .= "<input type='submit' value='Submit'></form>";
}
echo $form;
?>
</body>
</html>
Summary
This simple snippet of code provides an easy way to build a MySQL table input form, if we chucked it inside a class and gave it a parameter to pass in, it can be further beneficial. You could call upon it with any table name and it would simply output an HTML form with the required fields.
The describe SQL command has a vast array of uses, and this is one of them. It’s kind of made me want to create a package for building HTML forms automatically, hopefully, I will get round to it one day.