This would be my solution proposal.
Working principle:
- The users list is fetched from the db table and displayed in a bootstrap table.
- Each user id is saved as the value of the corresponding editButton.
- When an editButton is clicked - and before the modal is shown - the user id is used to fetch the data of the user from users by performing an ajax request to get-user.php. Note that this is the normal workflow: using only the user id - corresponding to a user record in the html table - to fetch all other user data from users and to fill that values in the modal controls. It would not be appropriate to "pass" all the user values from the html table to the modal. Why? Because the data in the html table can be formatted in other way as the real data of the db table users. If you would then "pass" the formatted values from the html table to the modal, your data in the modal would be also formatted in other way as in the db table users. And, when you would click on the updateButton, that different formatted data would have to be un-formatted before saving into users. Not so good.
- If no user data is found, or errors are raised (custom, or on-failure), then the modal is still shown, but a bootstrap alert (of type "danger") is displayed (in the .modal-messages div) and all inputs and the updateButton of the modal are disabled. Note that the disabled controls are re-enabled and all bootstrap alerts are removed when the modal is closed, e.g. before it becomes hidden.
- If user data is found, it is filled into the modal controls and the modal is shown.
- One can change then the values in the modal and click on the updateButton in the end. Then, an ajax request to update-user.php is performed, updating the user values in the users table.
- If the user could be successfully updated, a "success" bootstrap alert is shown in the modal. Otherwise, in case of (custom) errors, a "danger" alert is displayed.
Code structure:
The code consists of four files:
- index.php: contains the users list (e.g. the bootstrap table), the modal and the client-side functionality triggered by different events.
- get-user.php: contains the php code for fetching a user by her/his user id.
- update-user.php: performs the saving operation of the user values changed in the. modal.
- connection.php: contains the error reporting functions and the code for creating the mysqli connection.
Notes/suggestions:
- My code uses the object-oriented mysqli extension.
- I implemented prepared statements overall, in order to avoid any SQL injection possibility. The steps are commented for clarity and understanding.
- If you want, you could read this and this article to see how to implement error/exception handling.
I am open to any question you may have. So, don't hesitate to ask anything.
index.php
<?php
require 'connection.php';
/*
* The SQL statement to be prepared.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT *
FROM users';
/*
* Prepare the SQL statement for execution - ONLY ONCE.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* @link http://php.net/manual/en/mysqli-stmt.execute.php
*/
$statement->execute();
/*
* Get the result set from the prepared statement.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
* PHP config file (php.ini) and restart web server (I assume Apache) and
* mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* @link http://php.net/manual/en/mysqli-stmt.get-result.php
* @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
/*
* Fetch data and save it into an array:
*
* Array (
* [0] => Array (
* [id] => 1
* [name] => Sai
* [gender] => male
* [age] => 23
* )
* [1] => Array (
* [id] => 2
* [name] => Satya
* [gender] => female
* [age] => 18
* )
* )
*
* @link http://php.net/manual/en/mysqli-result.fetch-all.php
*/
$users = $result->fetch_all(MYSQLI_ASSOC);
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* @link http://php.net/manual/en/mysqli-result.free.php
*/
$result->close();
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* @link http://php.net/manual/en/mysqli-stmt.close.php
*/
$statement->close();
/*
* Close the previously opened database connection.
*
* @link http://php.net/manual/en/mysqli.close.php
*/
$connection->close();
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in the head -->
<title>Demo</title>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" type="text/css" rel="stylesheet" />
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" type="text/css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" type="text/javascript"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function () {
/*
* Fill the modal with data on modal show.
*/
$('#editModal').on('show.bs.modal', function (event) {
var modal = $(this);
// Extract the user id from the modal triggering button.
var editButton = $(event.relatedTarget);
var userId = editButton.val();
// Set the user id in the modal (read prior to update operation).
modal.find('#userId').val(userId);
// Fetch the user details and update the modal's content with them.
$.ajax({
method: 'post',
dataType: 'json',
url: 'get-user.php',
data: {
'userId': userId
},
success: function (response, textStatus, jqXHR) {
modal.find('.modal-title').text('Edit user ' + response.id);
modal.find('#name').val(response.name);
switch (response.gender) {
case 'female':
modal.find('#genderOptionFemale').prop('checked', true);
break;
case 'male': // No break.
default:
modal.find('#genderOptionMale').prop('checked', true);
break;
}
modal.find('#age').val(response.age);
},
error: function (jqXHR, textStatus, errorThrown) {
/*
* If the status code of the response is a custom one, defined by
* the developer - here 420, then the corresponding error message
* is displayed. Otherwise, the displayed message will be a general
* user-friendly one - so, that no system-related infos will be shown.
*/
var message = (jqXHR.status === 420)
? jqXHR.statusText
: 'An error occurred during your request. Please try again.';
displayModalAlert(modal, 'danger', message);
disableModalControls(modal);
},
complete: function (jqXHR, textStatus) {
//...
}
});
});
/*
* Re-enable the disabled controls on modal hide.
*/
$('#editModal').on('hide.bs.modal', function (event) {
var modal = $(this);
removeModalAlerts(modal);
enableModalControls(modal);
});
/*
* Update the user with the user input values.
*/
$('#updateButton').on('click', function (event) {
var modal = $('#editModal');
removeModalAlerts(modal);
$.ajax({
method: 'post',
dataType: 'json',
url: 'update-user.php',
data: {
'userId': modal.find('#userId').val(),
'name': modal.find('#name').val(),
'gender': modal.find('input[name="genderOptions"]:checked').val(),
'age': modal.find('#age').val()
},
success: function (response, textStatus, jqXHR) {
displayModalAlert(modal, 'success', response);
},
error: function (jqXHR, textStatus, errorThrown) {
/*
* If the status code of the response is a custom one, defined by
* the developer - here 420, then the corresponding error message
* is displayed. Otherwise, the displayed message will be a general
* user-friendly one - so, that no system-related infos will be shown.
*/
var message = (jqXHR.status === 420)
? jqXHR.statusText
: 'An error occurred during your request. Please try again.';
displayModalAlert(modal, 'danger', message);
},
complete: function (jqXHR, textStatus) {
//...
}
});
});
});
/**
* Display a bootstrap alert in the modal.
*
* @param modal object The modal object.
* @param type string Type of alert (success|info|warning|danger).
* @param message string Alert message.
* @return void
*/
function displayModalAlert(modal, type, message) {
modal.find('.modal-messages').html(getAlert(type, message));
}
/**
* Remove any bootstrap alert from the modal.
*
* @param modal object The modal object.
* @return void
*/
function removeModalAlerts(modal) {
modal.find('.modal-messages').html('');
}
/**
* Disable predefined modal controls.
*
* @param modal object The modal object.
* @return void
*/
function disableModalControls(modal) {
modal.find('.disabled-on-error')
.prop('disabled', true)
.addClass('disabled')
.css({
'cursor': 'not-allowed'
});
}
/**
* Enable predefined modal controls.
*
* @param modal object The modal object.
* @return void
*/
function enableModalControls(modal) {
modal.find('.disabled-on-error')
.prop('disabled', false)
.removeClass('disabled')
.css({
'cursor': 'auto'
});
}
/**
* Get a bootstrap alert.
*
* @param type string Type of alert (success|info|warning|danger).
* @param message string Alert message.
* @return string The bootstrap alert.
*/
function getAlert(type, message) {
return '<div class="alert alert-' + type + ' alert-dismissible fade show" role="alert">'
+ '<span>' + message + '</span>'
+ '<button type="button" class="close" data-dismiss="alert" aria-label="Close">'
+ '<span aria-hidden="true">×</span>'
+ '</button>'
+ '</div>';
}
</script>
<style type="text/css">
body {
margin: 0;
padding: 20px;
}
</style>
</head>
<body>
<div class="container">
<table class="table table-hover">
<thead>
<tr>
<th>Name</th>
<th>Gender</th>
<th>Age</th>
<th> </th>
</tr>
</thead>
<tbody>
<?php
if ($users) {
foreach ($users as $user) {
$id = $user['id'];
$name = $user['name'];
$gender = $user['gender'];
$age = $user['age'];
?>
<tr>
<td>
<?php echo $name; ?>
</td>
<td>
<?php echo $gender; ?>
</td>
<td>
<?php echo $age; ?>
</td>
<td>
<button type="button" id="editButton" name="editButton" value="<?php echo $id; ?>" class="btn btn-primary" data-toggle="modal" data-target="#editModal">
Edit
</button>
</td>
</tr>
<?php
}
} else {
?>
<tr>
<td colspan="4">
- No users found -
</td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
<!-- Modal -->
<div class="modal fade" id="editModal" tabindex="-1" role="dialog" aria-labelledby="editModalLabel" aria-hidden="true">
<div class="modal-dialog modal-dialog-centered" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="editModalLabel">Edit</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<div class="modal-messages"></div>
<form>
<input type="hidden" id="userId" name="userId">
<div class="form-group">
<label for="name">Name *</label>
<input type="text" id="name" name="name" class="form-control disabled-on-error">
</div>
<div class="form-group">
<label>Gender</label>
<div class="form-check form-check-inline">
<input type="radio" id="genderOptionMale" name="genderOptions" value="male" class="form-check-input disabled-on-error">
<label for="genderOptionMale" class="form-check-label">male</label>
</div>
<div class="form-check form-check-inline">
<input type="radio" id="genderOptionFemale" name="genderOptions" value="female" class="form-check-input disabled-on-error">
<label for="genderOptionFemale" class="form-check-label">female</label>
</div>
</div>
<div class="form-group">
<label for="age">Age</label>
<input type="number" id="age" name="age" class="form-control disabled-on-error">
</div>
<small class="form-text text-muted">* - Mandatory fields</small>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
<button type="button" id="updateButton" name="updateButton" value="update" class="btn btn-primary disabled-on-error">
Update
</button>
</div>
</div>
</div>
</div>
</body>
</html>
get-user.php
<?php
require 'connection.php';
// Validate the posted user id.
if (!isset($_POST['userId']) || empty($_POST['userId'])) {
/*
* This custom response header triggers the ajax error because the status
* code begins with 4xx (which corresponds to the client errors). Here is
* defined "420" as the custom status code. One can choose whatever code
* between 401-499 which is not officially assigned, e.g. which is marked
* as "Unassigned" in the official HTTP Status Code Registry. See the link.
*
* @link https://www.iana.org/assignments/http-status-codes/http-status-codes.xhtml HTTP Status Code Registry.
*/
header('HTTP/1.1 420 No user specified.');
exit();
} /* Other validations here using elseif statements */
// Get the user id.
$userId = $_POST['userId'];
/*
* The SQL statement to be prepared. Notice the so-called markers,
* e.g. the "?" signs. They will be replaced later with the
* corresponding values when using mysqli_stmt::bind_param.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT *
FROM users
WHERE id = ?
LIMIT 1';
$statement = $connection->prepare($sql);
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to prepare(). The first
* argument of bind_param() is a string that contains one
* or more characters which specify the types for the
* corresponding bind variables.
*
* @link http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$statement->bind_param('i', $userId);
$statement->execute();
$result = $statement->get_result();
/*
* Fetch data and save it into an array:
*
* Array (
* [id] => 1
* [name] => Sai
* [gender] => male
* [age] => 23
* )
*
* @link https://secure.php.net/manual/en/mysqli-result.fetch-array.php
*/
$user = $result->fetch_array(MYSQLI_ASSOC);
/*
* When no records are found, fetch_array()
* returns NULL. In this case throw an error.
*/
if (!isset($user)) {
header('HTTP/1.1 420 No user found by the given criteria.');
exit();
}
$result->close();
$statement->close();
$connection->close();
echo json_encode($user);
exit();
update-user.php
<?php
require 'connection.php';
// Validate the posted user id.
if (!isset($_POST['userId']) || empty($_POST['userId'])) {
header('HTTP/1.1 420 No user specified.');
exit();
} /* Other validations here using elseif statements */
// Validate the posted user name.
if (!isset($_POST['name']) || empty($_POST['name'])) {
header('HTTP/1.1 420 Please provide the name.');
exit();
} /* Other validations here using elseif statements */
// Get the posted values.
$userId = $_POST['userId'];
$name = $_POST['name'];
$gender = $_POST['gender'];
$age = $_POST['age'];
$sql = 'UPDATE users
SET
id = ?,
name = ?,
gender = ?,
age = ?
WHERE id = ?';
$statement = $connection->prepare($sql);
$statement->bind_param('issii', $userId, $name, $gender, $age, $userId);
$statement->execute();
$affectedRows = $connection->affected_rows;
$statement->close();
$connection->close();
if ($affectedRows === -1) {
header('HTTP/1.1 420 An error occurred during your request. Please try again.');
exit();
} else {
echo json_encode('User data successfully saved.');
exit();
}
connection.php
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');
/*
* Error reporting.
*
* Also, define an error handler, an exception handler and, eventually,
* a shutdown handler function to handle the raised errors and exceptions.
*
* @link https://phpdelusions.net/articles/error_reporting Error reporting basics
* @link http://php.net/manual/en/function.error-reporting.php
* @link http://php.net/manual/en/function.set-error-handler.php
* @link http://php.net/manual/en/function.set-exception-handler.php
* @link http://php.net/manual/en/function.register-shutdown-function.php
*/
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER! */
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception).
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* @link http://php.net/manual/en/class.mysqli-driver.php
* @link http://php.net/manual/en/mysqli-driver.report-mode.php
* @link http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// Create a new db connection.
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
Create table syntax
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`gender` enum('male','female') NOT NULL DEFAULT 'male',
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
