How To Automatically Make A MySQL Input Form With PHP

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 a HTML input form with the correct fields of a databases 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 explanations 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 the for attribute 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 –

auto generated form

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 –

auto generated form with styling

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 a HTML form with the required fields.

The describe SQL command has a vast array of uses, and this is one of them. It’s kinda of made me want to create a package for building HTML forms automatically, hopefully I will get round to it one day.

How To Automatically Make A MySQL Input Form With PHP
Posted in PHP

You May Also Like

Leave a Reply

Your email address will not be published.