Edit Select Option Dynamic Using PHP Mysql
Filter

Edit Select Option Dynamic Using PHP Mysql

by Soeng Souy /
Share

1. database DB (SQL)

CREATE TABLE `tbl_insert` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `country` varchar(200) DEFAULT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_select` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `country` varchar(200) NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tbl_select` (`id`, `country`) VALUES
(1, 'Cambodia'),
(2, 'Mexico'),
(3, 'India'),
(4, 'France'),
(5, 'China'),
(6, 'Bermuda'),
(7, 'Australia'),
(8, 'Andorra'),
(9, 'Burundi'),
(10, 'Colombia');

2. database.php (Database Connection)

<?php
    $servername = 'localhost';
    $username = 'root';
    $password = '';
    $dbname = "db_select";
    $connection = mysqli_connect($servername, $username, $password, $dbname);
    if(!$connection) {
        die('Could not connect MySQL: ' . mysqli_error($connection));
    }
?>

3. edit.php (Edit Data)

This file is used to edit existing data. It allows updating the user's name and country based on their ID.

<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'db_country';
$connection = mysqli_connect($servername, $username, $password, $dbname);

$update = '';
if(!$connection) {
    die('Could not connect MySQL: ' . mysqli_error($connection));
}

$country = mysqli_query($connection, "SELECT DISTINCT country FROM tbl_insert");

if(count($_POST) > 0) {
    mysqli_query($connection, "UPDATE tbl_insert set name='" . $_POST['name'] . "', country='" . $_POST['country'] . "' WHERE `tbl_insert`.`id` = 1");
    $update = "Update has been successfully!";
}

$edite = mysqli_query($connection, "SELECT * FROM tbl_insert WHERE `tbl_insert`.`id` = 1");
$row = mysqli_fetch_array($edite);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Edit</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
</head>
<body>
    <div class="container-fluid h-100 bg-light text-dark">
        <div class="row justify-content-center align-items-center">
            <h1>Edit User</h1>
        </div>
        <hr/>
        <div class="row justify-content-center align-items-center h-100">
            <div class="col col-sm-6 col-md-6 col-lg-4 col-xl-3">
                <a href="index.php">Back to list</a>
                <h5 class="text-success text-center" id="update"><?= $update; ?></h5>
                <form action="edit.php" method="POST">
                    <br />
                    <div class="form-group">
                        <input type="text" class="form-control" name="name" value="<?= $row['name']; ?>">
                    </div>
                    <div class="form-group">
                        <select class="form-control" name="country">
                            <?php foreach($country as $rows):?>
                                <option value="<?= $rows['country']; ?>" <?= ($row['country'] == $rows['country']) ? 'selected="selected"' : ''; ?>><?= $rows['country']; ?></option>
                            <?php endforeach;?>
                        </select>
                    </div>
                    <div class="form-group">
                        <div class="container">
                            <div class="row">
                                <div class="col"><button type="submit" name="submit" class="col-6 btn btn-primary btn-sm float-left">Submit</button></div>
                                <div class="col"><button type="submit" name="reset" class="col-6 btn btn-secondary btn-sm float-right">Reset</button></div>
                            </div>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</body>
</html>

4. index.php (Display Data and Insert New Records)

This file is used to display the records and provide an option to insert new records.

<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'db_country';
$connection = mysqli_connect($servername, $username, $password, $dbname);

if(!$connection) {
    die('Could not connect MySQL: ' . mysqli_error($connection));
}

$select = "SELECT * FROM `tbl_select`";
$retrive = mysqli_query($connection, "SELECT * FROM tbl_insert");
$option = mysqli_query($connection, "SELECT * FROM tbl_select");

$message = '';
if(isset($_POST['submit'])) {
    $name = $_POST['name'];
    $country = $_POST['country'];

    // Insert data into database
    $insert = "INSERT INTO tbl_insert (name, country) VALUES ('$name', '$country')";
    if(mysqli_query($connection, $insert)) {
        $message = "Records added successfully.";
    } else {
        $message = "ERROR: Could not able to execute $insert. " . mysqli_error($connection);
    }
    // Close connection
    mysqli_close($connection);
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Select</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
</head>
<body>
    <div class="container-fluid h-100 bg-light text-dark">
        <div class="row justify-content-center align-items-center">
            <h1>Select Option Dynamic Mysql</h1>
        </div>
        <hr/>
        <div class="row justify-content-center align-items-center h-100">
            <div class="col col-sm-6 col-md-6 col-lg-4 col-xl-3">
                <h5 class="text-success text-center" id="message"><?= $message; ?></h5>
                <form action="index.php" method="POST">
                    <div class="form-group">
                        <input type="text" class="form-control" name="name" placeholder="Enter name" required>
                    </div>
                    <div class="form-group">
                        <select class="form-control" name="country">
                            <option>Please select country</option>
                            <?php foreach($option as $key => $value) { ?>
                                <option value="<?= $value['country']; ?>"><?= $value['country']; ?></option>
                            <?php } ?>
                        </select>
                    </div>
                    <div class="form-group">
                        <div class="container">
                            <div class="row">
                                <div class="col"><button type="submit" name="submit" class="col-6 btn btn-primary btn-sm float-left">Submit</button></div>
                                <div class="col"><button type="submit" name="reset" class="col-6 btn btn-secondary btn-sm float-right">Reset</button></div>
                            </div>
                        </div>
                    </div>
                </form>
                <hr>
                <table id="tableHorizontalWrapper" class="table table-striped table-bordered table-sm text-center" cellspacing="0" width="100%">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Name</th>
                            <th>Country</th>
                            <th>Action</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php
                    while($row = mysqli_fetch_array($retrive)) {
                        ?>
                        <tr>
                            <td><?= $row['id']; ?></td>
                            <td><?= $row['name']; ?></td>
                            <td><?= $row['country']; ?></td>
                            <td><a href="edit.php?id=<?php echo $row["id"]; ?>">Edit</a></td>
                        </tr>
                    <?php
                    }
                    ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    <script>
        $(document).ready(function() {
            setTimeout(function() {
                $('#message').hide();
            }, 3000);
        });

        $(document).ready(function() {
            setTimeout(function() {
                $('#update').hide();
            }, 3000);
        });
    </script>
</body>
</html>

Explanation:

  1. index.php: Allows users to insert data into the tbl_insert table and display all records with a link to edit them.

  2. edit.php: Displays the user's current data and allows updating their name and country.

  3. database.php: This handles the connection to the MySQL database.

This setup will let you select a country from a drop-down, and upon selection, it will display the corresponding data dynamically without page reloads, although you will need to extend it to handle states and cities using JavaScript and AJAX for dynamic data loading.

Tag
Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close