When working with PHP and MySQL, we often need to build dynamic forms that fetch data from the database. These forms allow users to insert, update, delete, and search records dynamically.
1. Steps to Build a Dynamic Form from a MySQL Query
- Connect to MySQL Database
- Fetch Data from the Database
- Dynamically Populate Form Fields
- Submit Form & Process Data (Insert, Update, Delete, Search)
- Display Results
2. Example 1: Populating a Dropdown from a MySQL Query
A dropdown list (select box) can be populated dynamically from a MySQL database.
🔹 Example: Fetching Categories for a Dropdown
<?php
// Step 1: Connect to Database
$conn = mysqli_connect(“localhost”, “root”, “”, “shop”);
// Check Connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
// Step 2: Query the Database
$sql = “SELECT id, category_name FROM categories”;
$result = mysqli_query($conn, $sql);
?>
<!– Step 3: Create the HTML Form –>
<form method=”POST”>
<label>Select Category:</label>
<select name=”category”>
<option value=””>– Select –</option>
<?php
// Step 4: Populate Dropdown
while ($row = mysqli_fetch_assoc($result)) {
echo “<option value='” . $row[‘id’] . “‘>” . $row[‘category_name’] . “</option>”;
}
?>
</select>
<button type=”submit”>Submit</button>
</form>
<?php
// Step 5: Handle Form Submission
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
$category_id = $_POST[‘category’];
echo “Selected Category ID: ” . $category_id;
}
// Close Connection
mysqli_close($conn);
?>
✔ Output:
A dropdown list populated dynamically from the categories table.
3. Example 2: Generating a Form from a Database Table
Instead of manually coding a form, we can generate input fields dynamically based on database columns.
🔹 Example: Auto-Generating an Input Form from a Table
<?php
// Step 1: Connect to Database
$conn = mysqli_connect(“localhost”, “root”, “”, “shop”);
// Step 2: Fetch Table Structure
$sql = “SHOW COLUMNS FROM products”; // Retrieves column names from `products` table
$result = mysqli_query($conn, $sql);
?>
<!– Step 3: Create the HTML Form –>
<form method=”POST”>
<?php
while ($row = mysqli_fetch_assoc($result)) {
$column_name = $row[‘Field’]; // Get column name
echo “<label>” . ucfirst($column_name) . “:</label>”;
echo “<input type=’text’ name=’$column_name’><br><br>”;
}
?>
<button type=”submit”>Submit</button>
</form>
<?php
// Step 4: Handle Form Submission
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
foreach ($_POST as $key => $value) {
echo “<p><b>$key:</b> $value</p>”;
}
}
// Close Connection
mysqli_close($conn);
?>
✔ Output:
This dynamically generates an input field for each column in the products table.
4. Example 3: Pre-Filling Form for Editing Data
If we want to update existing records, we need to pre-fill form fields with database values.
🔹 Example: Edit User Data
<?php
// Step 1: Connect to Database
$conn = mysqli_connect(“localhost”, “root”, “”, “users_db”);
// Step 2: Fetch Existing Data for User ID 1
$user_id = 1;
$sql = “SELECT * FROM users WHERE id = $user_id”;
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
?>
<!– Step 3: Create the Form with Pre-Filled Values –>
<form method=”POST”>
<label>Name:</label>
<input type=”text” name=”name” value=”<?php echo $row[‘name’]; ?>”><br><br>
<label>Email:</label>
<input type=”email” name=”email” value=”<?php echo $row[’email’]; ?>”><br><br>
<button type=”submit” name=”update”>Update</button>
</form>
<?php
// Step 4: Handle Form Submission & Update Record
if (isset($_POST[‘update’])) {
$name = $_POST[‘name’];
$email = $_POST[’email’];
$update_sql = “UPDATE users SET name=’$name’, email=’$email’ WHERE id=$user_id”;
mysqli_query($conn, $update_sql);
echo “Record Updated!”;
}
// Close Connection
mysqli_close($conn);
?>
✔ Output:
A form pre-filled with existing user data, ready for updates.
5. Example 4: Search Form (Filter Query Results)
Users can search records based on their input.
🔹 Example: Search Users by Name
<?php
$conn = mysqli_connect(“localhost”, “root”, “”, “users_db”);
?>
<!– Step 1: Create the Search Form –>
<form method=”POST”>
<input type=”text” name=”search” placeholder=”Search by name”>
<button type=”submit”>Search</button>
</form>
<?php
// Step 2: Handle Form Submission & Execute Query
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
$search = $_POST[‘search’];
$sql = “SELECT * FROM users WHERE name LIKE ‘%$search%'”;
$result = mysqli_query($conn, $sql);
// Step 3: Display Results in a Table
echo “<table border=’1′><tr><th>ID</th><th>Name</th><th>Email</th></tr>”;
while ($row = mysqli_fetch_assoc($result)) {
echo “<tr><td>” . $row[‘id’] . “</td><td>” . $row[‘name’] . “</td><td>” . $row[’email’] . “</td></tr>”;
}
echo “</table>”;
}
// Close Connection
mysqli_close($conn);
?>
✔ Output:
Users can search records dynamically, and results are displayed in a table.
6. Example 5: Insert Form (Saving User Input to MySQL)
A form that saves user input into the database.
🔹 Example: Insert Data into users Table
<?php
$conn = mysqli_connect(“localhost”, “root”, “”, “users_db”);
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
$name = $_POST[‘name’];
$email = $_POST[’email’];
$sql = “INSERT INTO users (name, email) VALUES (‘$name’, ‘$email’)”;
mysqli_query($conn, $sql);
echo “User Added!”;
}
mysqli_close($conn);
?>
<!– HTML Form –>
<form method=”POST”>
<label>Name:</label>
<input type=”text” name=”name”><br><br>
<label>Email:</label>
<input type=”email” name=”email”><br><br>
<button type=”submit”>Add User</button>
</form>
✔ Output:
This form inserts new users into the users table.
7. Summary
| Feature | Example Code |
| Dropdown List from Query | Populating a <select> from MySQL |
| Auto-Generated Form | Dynamically creating form fields |
| Pre-Filled Form for Editing | Load user data into a form for updates |
| Search Form | Filter database records dynamically |
| Insert Form | Save user input to the database |
8. Conclusion
Building forms from queries in PHP/MySQL allows for dynamic, scalable, and interactive web applications. By using database queries, we can create searchable, editable, and insertable forms.