Inline Editing using PHP, MySQL, jQuery and Twitter Bootstrap
In some appliaction user want to keep everything (add, edit and delete pages as one page) in one place, where user can add, edit and delete content they want using inline editing features. Have look at two type inline editing styles in the demo page.
Please refer my latest tutorial on Inline edit ( Inline edit using jQuery and PHP, MySQL and Bootstrap 3 )
Popup style – Inline editing :
Inline style – Inline Editing :
Inline Editing using PHP, MySQL, jQuery and Twitter Bootstrap
Step 1:
I have created sample table to implement this, use following sql query to create sample table for demo purpose.
CREATE TABLE IF NOT EXISTS `sample` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(75) NOT NULL, `comments` text NOT NULL, `country_name` varchar(100) NOT NULL, `dob` date NOT NULL, `appt` datetime NOT NULL, `combo_appt` date NOT NULL, `email` varchar(100) NOT NULL, `options` varchar(150) NOT NULL, `wy_text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
Insert some sample data’s in it, using following sql queries if you need it.
INSERT INTO `sample` (`id`, `username`, `comments`, `country_name`, `dob`, `appt`, `combo_appt`, `email`, `options`, `wy_text`) VALUES (1, 'muni', 'HI\nmuni', 'USA', '2013-08-17', '2013-08-22 10:50:00', '2014-09-22', 'muni@smmarttutorials.com', 'Array', '<h2>awesome</h2> comment!<br><br>Thank you,<br><br><br>Muni.');
Step 2:
Create config.php file to keep database CONNECTIONS at one place. Also kept two database utility functions to prepare prepared sql statements and get results of MySQL statements.
<?php
/*
* Site : http:www.smarttutorials.net
* Author :muni
*
*/
define('BASE_PATH','http://localhost/demo_tut/inline/');
define('DB_HOST', 'localhost');
define('DB_NAME', 'smarttut_demo');
define('DB_USER','xyz');
define('DB_PASSWORD','mysql');
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// $sql = "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))";
// $mysqli->query($sql);
/**
* Preparing Statement
*/
function prepareStatement($sql, $params = null)
{
global $mysqli;
$stmt = $mysqli->prepare($sql);
if (!($stmt)) {
throw new Exception("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
}
if (!empty($params) && is_array($params)) {
$count = count($params);
$bindStr = str_repeat('s', $count);
$stmt->bind_param($bindStr, ...$params);
}
if (!$stmt->execute()) {
throw new Exception("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
}
return $stmt;
}
/**
* GetResult
*/
function getResult($stmt)
{
if (!($res = $stmt->get_result())) {
throw new Exception("Getting result set failed: (" . $stmt->errno . ") " . $stmt->error);
}
return $res;
}Step 3:
Create index.php file and add following css and jquery files to implement simple textbox inline editing.
<link href="css/jquery-ui.css" rel="stylesheet">
<link href="css/bootstrap.min.css" rel="stylesheet" media="screen">
<link href="css/bootstrap-editable.css" rel="stylesheet">
<script src="js/jquery-1.10.2.min.js"></script>
<script src="js/jquery-ui.js"></script>
<script src="js/bootstrap.min.js"></script>
<script src="js/bootstrap-editable.min.js"></script>Next create functions.php file to keep following list of function which take care getting list of records from database, also it’s taking care of insert/update particular record in database.
<?php
/**
* File functions.php
* @author muni
* @link https://smarttutorials.net/
*/
function getSamples()
{
$sql = "SELECT * FROM sample";
$stmt = prepareStatement($sql);
$res = getResult($stmt);
return $res->fetch_assoc();
}
/**
* Insert/Update Username
*/
function insertOrUpdateUsername($data)
{
$id = $data['pk'];
$username = $data['value'];
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(username, id) VALUES(?, ?)";
}else{
$query = "UPDATE sample SET username=? WHERE id=?";
}
prepareStatement($query, [$username, $id]);
}
/**
* Insert/Update Comments
*/
function insertOrUpdateComments($data)
{
$id = $data['pk'];
$comments = $data['value'];
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(comments, id) VALUES(?, ?)";
}else{
$query = "UPDATE sample SET comments=? WHERE id=?";
}
prepareStatement($query, [$comments, $id]);
}
/**
* Insert/Update Country
*/
function insertOrUpdateCountry($data)
{
$id = $data['pk'];
$country = $data['value'];
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(country_name, id) VALUES(?, ?)";
}else{
$query = "UPDATE sample SET country_name=? WHERE id=?";
}
prepareStatement($query, [$country, $id]);
}
/**
* Insert/Update dob
*/
function insertOrUpdateDob($data)
{
$id = $data['pk'];
$dob = $data['value'];
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(dob, id) VALUES(?, ?)";
}else{
$query = "UPDATE sample SET dob=? WHERE id=?";
}
prepareStatement($query, [$dob, $id]);
}
/**
* Insert/Update Appt
*/
function insertOrUpdateAppt($data)
{
$id = $data['pk'];
$appt = $data['value'];
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(appt, id) VALUES(?, ?)";
}else{
$query = "UPDATE sample SET appt=? WHERE id=?";
}
prepareStatement($query, [$appt, $id]);
}
/**
* Insert/Update Combo
*/
function insertOrUpdateCombo($data)
{
$id = $data['pk'];
$combo = $data['value'];
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(combo_appt, id) VALUES(?, ?)";
}else{
$query = "UPDATE sample SET combo_appt=? WHERE id=?";
}
prepareStatement($query, [$combo, $id]);
}
/**
* Insert/Update Email
*/
function insertOrUpdateEmail($data)
{
$id = $data['pk'];
$email = $data['value'];
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(email, id) VALUES(?, ?)";
} else {
$query = "UPDATE sample SET email=? WHERE id=?";
}
prepareStatement($query, [$email, $id]);
}
/**
* Insert/Update Options
*/
function insertOrUpdateOptions($data)
{
$id = $data['pk'];
$options = !empty($data['value']) ? $data['value'] : [];
$options = json_encode($options);
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(options, id) VALUES(?, ?)";
}else{
$query = "UPDATE sample SET options=? WHERE id=?";
}
prepareStatement($query, [$options, $id]);
}
/**
* Insert/Update wy
*/
function insertOrUpdateWy($data)
{
$id = $data['pk'];
$text = htmlspecialchars($data['value']);
$sql = "SELECT id FROM sample WHERE id=?";
$stmt = prepareStatement($sql, [$id]);
$res = getResult($stmt);
if ($res->num_rows == 0) {
$query = "INSERT INTO sample(wy_text, id) VALUES(?, ?)";
}else{
$query = "UPDATE sample SET wy_text=? WHERE id=?";
}
prepareStatement($query, [$text, $id]);
} Now add following a tag and define type following properties data-type, data-pk, data=url and data-title in each of the inline edit.
<a href="#" id="username" data-type="text" data-pk="1" data-url="post.php" data-title="Enter username">
<?php
if (!empty($result[1])) {
echo $result[1];
} else {
echo "muni";
}
?></a>Now defining inline editing style (popup or inline) with following jQuery script. Next selecting the above HTML DOM element and making it inline editable.
<script type="text/javascript">
$.fn.editable.defaults.mode = "popup";
$('#username').editable();
</script>
Now when user clicks on above a tag text, editable textbox will open where they can enter their name and click on submit to save.
where data-type=’text’ when user clicks on it, it will generate textbox.
data-url=’post.php’ — it says where to post submitted data.
data-pk=’1′ — Assign unique primary key of the data.
Textarea :
<a data-url="post.php" data-original-title="Enter comments" data-placeholder="Your comments here..." data-pk="1" data-type="textarea" id="comments" href="#"><?=isset($result['comments']) ? htmlspecialchars($result['comments']) : "Enter your comments here";?></a>
<script type="text/javascript">
$('#comments').editable();
</script>Email :
<a href="#" id="email" data-type="email" data-pk="1">
<?php
if (!empty($result[7])) { echo $result[7];
} else { echo "Enter your email!";
}
?>
</a>
<script>
$(function() {
$('#email').editable({
url : 'post.php',
title : 'Enter email'
});
});
</script>
Dropdown Box :
<a data-url="post.php" data-type='select' data-original-title="Select your country" data-pk="1" id="country">
<?php
if (!empty($result[3])) { echo $result[3];
} else { echo "Select your country";
}
?>
</a>
<script>
$(function() {
$('#country').editable({
source : [{
value : '',
text : 'Select your country'
}, {
value : 'India',
text : 'India'
}, {
value : 'USA',
text : 'USA'
}, {
value : 'Singapore',
text : 'Singapore'
}]
});
});
</script>
To implement editable datapicker you need to add following datapicker css and jquery files.
<link href="css/datepicker.css" rel="stylesheet" media="screen"> <script src="js/bootstrap-datepicker.js"></script>
Datepicker :
<a data-url="post.php" href="#" data-type='date' id='dob' data-original-title="Select your dob" data-pk="1">
<?php
if (!empty($result[4])) { echo $result[4];
} else { echo "Select your dob";
}
?>
</a>
<script>
$(function() {
$('#dob').editable({
format : 'yyyy-mm-dd',
viewformat : 'dd/mm/yyyy',
datepicker : {
weekStart : 1
}
});
});
</script>
To implement datatimepicker to add following css and js files.
<link href="css/datetimepicker.css" rel="stylesheet" media="screen"> <script src="js/bootstrap-datetimepicker.js"></script>
Datetimepicker :
<a data-url="post.php" href="#" data-type='datetime' id='appt' data-original-title="Select your appiontment date and time" data-pk="1">
<?php
if (!empty($result[5])) { echo $result[5];
} else { echo "Select your appiontment date and time";
}
?>
</a>
<script>
$(function() {
$('#appt').editable({
format : 'yyyy-mm-dd hh:ii',
viewformat : 'dd/mm/yyyy hh:ii',
datetimepicker : {
weekStart : 1
}
});
});
</script>
Combo Datapicker :
To implement this datapicker you to add following css and js files.
<script src="js/combodate.js"></script> <script src="js/moment.min.1.7.2.js"></script>
<a data-url="post.php" href="#" data-type='combodate' id='combo' data-pk="1" data-value="1984-05-15" data-original-title="Select date">
<?php
if (!empty($result[6])) { echo $result[6];
} else { echo "Select your dob";
}
?>
</a>
<script>
$(function() {
$('#combo').editable({
format : 'YYYY-MM-DD',
viewformat : 'DD.MM.YYYY',
template : 'D / MMMM / YYYY',
combodate : {
minYear : 2000,
maxYear : 2015,
minuteStep : 1
}
});
});
</script>
Please refer following Combodate documentation, if you have any doubts.
wysiwyg text editor :
To implement this add following css and js files.
<link href="css/bootstrap-wysihtml5.css" rel="stylesheet" media="screen"> <link href="css/prettify.css" rel="stylesheet" media="screen"> <script src="js/wysihtml5-0.3.0.min.js"></script> <script src="js/wysihtml5.js"></script> <script src="js/bootstrap-wysihtml5.js"></script>
<div id="wy" data-type="wysihtml5" data-pk="1">
<?php
if (!empty($result[9])) { echo htmlspecialchars_decode($result[9]);
} else { echo "Write your story";
}
?>
</div>
<script>
$(function() {
$('#wy').editable({
url : 'post.php',
title : 'Enter comments'
});
});
</script>
Please refer following Bootstrap inline editing Documentation
Step 5:
Finally create post.php file, which take care of submitted data through inline editing and updates the database table fields.
<?php
require_once 'config.php';
require_once 'functions.php';
try {
if ($_POST['name']=='username') {
insertOrUpdateUsername($_POST);
}
if ($_POST['name']=='comments') {
insertOrUpdateComments($_POST);
}
if ($_POST['name']=='country') {
insertOrUpdateCountry($_POST);
}
if ($_POST['name']=='dob') {
insertOrUpdateDob($_POST);
}
if ($_POST['name']=='appt') {
insertOrUpdateAppt($_POST);
}
if ($_POST['name']=='combo') {
insertOrUpdateCombo($_POST);
}
if ($_POST['name']=='email') {
insertOrUpdateEmail($_POST);
}
if($_POST['name']=='options'){
insertOrUpdateOptions($_POST);
}
if($_POST['name']=='wy'){
insertOrUpdateWy($_POST);
}
$status = [
"success" => true,
"message" => "Record updated successfully"
];
} catch (Exception $e) {
$status = [
"success" => false,
"message" => "Error updating record: " . mysqli_error($con)
];
}
echo json_encode($status);exit;Download Premium Only Scripts & 80+ Demo scripts Instantly at just 1.95 USD per month + 10% discount to all Exclusive Scripts
If you want any of my script need to be customized according to your business requirement,
Please feel free to contact me [at] muni2explore[at]gmail.com
Note: But it will be charged based on your customization requirement
