UPDATE Y INSERT FORMATO FECHA MYSQL PHP JQUERY

Basic AJAX CRUD app using PHP, MySQL and jQuery

CODE PHP DEVELOPMENT TUTORIALS January 23, 2021 4 Comments SHARE

We are going to build a basic AJAX CRUD app from scratch using only PHP, MySQL and jQuery AJAX. CRUD is an acronym for Create, Read, Update, Delete. Basic operations that are at the heart of all web apps.

We will use Bootstrap for the layout and utilize other jQuery plugins for the UI.

But first, the file structure.

/db – The directory that has all the server and database related files

env.php – environment settings for database, server and miscellaneous display options

generate-db.php – creates a database based on the details set in the env. file

create-table.php -creates a table based on the details set in the env. file

connect-db.php – global include for databases connection

/api – The directory that has all endpoint related files

create.php – all the code and mySQL statements for inserting records

delete.php – all the code and mySQL statements for deleting records

index.php – all the code and mySQL statements for showing rows based on the current page

update.php – all the code and mySQL statements for updating records

/js/index.js – The file that handles all page events

displayBookList() – displays the initial list of all books with pagination

displayPageEntries() – displays books for the current page

refreshList(data) – universal function to refresh the content of the table, takes in data.

View book – has all the code to display a record

Create book – code to add a new record

Edit book – all the code to fill in the edit form with current data

Update book – code to update a record

/index.php – Is the main file that contains all elements, our app will use modal windows to display the forms.

Let’s start.

Step 1: Take care of the database. These files are for global use but create-table.php and generate-db.php are used only once.

.env.php

<?php

// Database info

$server = "localhost";

$user = "root";

$pass = "";

$db_name = "awesomedb";

$table_name = "books";

// Server URL

$url = "http://localhost/";

$display_rows = 10;

generate-db.php

<?php

include_once("env.php");

// Create connection

$connection = new mysqli($server, $user, $pass);

// Check connection

if ($connection->connect_error) {

die("ERROR: Could not connect " . $connection->connect_error);

}

// Create new database

$sql = "CREATE DATABASE `$db_name`";

if($connection->query($sql) === TRUE){

echo "Database created successfully";

} else {

echo "ERROR: Unable to execute $sql. " . $connection->error;

}

// Close connection

$connection->close();

create-table.php

<?php

include_once("connect-db.php");

// Create new table

$sql = "CREATE TABLE `$table_name` (

id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(191) NOT NULL,

author VARCHAR(191) NOT NULL,

published_date DATE,

last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

status ENUM('active', 'pending', 'deleted')

)";

if($connection->query($sql) === TRUE){

echo "Table '".$table_name."' created successfully";

} else {

echo "Error creating table: " . $connection->error;

}

// Close connection

$connection->close();

connect-db.php

<?php

include_once("env.php");

// Create connection

$connection = new mysqli($server, $user, $pass, $db_name);

// Check connection

if ($connection->connect_error) {

die("ERROR: Could not connect " . $connection->connect_error);

}

Step 2: Prepare the endpoints. These files run the mySQL queries for the operations. This is where the magic actually happens.

create.php

<?php

require_once('../db/connect-db.php');

// Add new record

$sql = "INSERT INTO `$table_name` ( `title`, `author`, `published_date`) VALUES ( '".$_POST['title']."', '".$_POST['author']."', '".$_POST['published_date']."')";

$result = $connection->query($sql);

// Return value

$sql = "SELECT * FROM $table_name ORDER BY id DESC LIMIT 1";

$result = $connection->query($sql);

$data = $result->fetch_assoc();

echo json_encode($data);

index.php

<?php

require_once('../db/connect-db.php');

$page = (isset($_GET["page"])) ? $_GET["page"] : 1;

$start_from = ($page-1) * $display_rows;

// Get all rows for selected page

$sql = "SELECT * FROM $table_name ORDER BY id DESC LIMIT $start_from, $display_rows";

$result = $connection->query($sql);

while($row = $result->fetch_assoc()){

$json[] = $row;

}

$data['data'] = $json;

// Get total count of all books

$sql = "SELECT COUNT(*) AS total FROM $table_name";

$result = $connection->query($sql);

$values = mysqli_fetch_assoc($result);

$data['total'] = $values['total'];

// Return books

echo json_encode($data);

update.php

<?php

require_once('../db/connect-db.php');

// Update record

$sql = "UPDATE `$table_name` SET `title` = '".$_POST['title']."', `author` = '".$_POST['author']."', `published_date` = '".$_POST['published_date']."' WHERE `id` = ".$_POST['id']."";

$result = $connection->query($sql);

// Return value

$sql = "SELECT * FROM $table_name WHERE `id` = '".$_POST['id']."'";

$result = $connection->query($sql);

$data = $result->fetch_assoc();

echo json_encode($data);

delete.php

<?php

require_once('../db/connect-db.php');

$id = $_POST["id"];

// Delete record

$sql = "DELETE FROM $table_name WHERE id = '".$id."'";

if($connection->query($sql) === TRUE){

echo json_encode([$id]);

} else {

echo "Error deleting record " . $connection->error;

}

// Close connection

$connection->close();

Step 3: Design the user interface. Page elements like lists, pagination, buttons and modal windows allows for user interaction. We used a few jQuery plugins to improve the UI like twbsPagination, bootstrap-validator, jQueryUI and toast to display notifications

index.php

<?php require_once("db/env.php"); ?>

<!doctype html>

<head>

var url = "<?php echo $url; ?>";

var display_rows = "<?php echo $display_rows; ?>";

$(function(){

$("#published_date").datepicker({ dateFormat: 'yy-mm-dd' });

$("#published_date_edit").datepicker({ dateFormat: 'yy-mm-dd' });

});

</script>

<title>Trial Project for Worker Bee TV - Submitted by Jay Acab chiefofstack.com </title>

</head>

<body>

<h2>Books</h2>

</div>

Add New Book

</button>

</div>

<thead>

<tr>

<th>Date Published</th>

<th>Author name</th>

<th>Book Title</th>

<th>Action</th>

</tr>

</thead>

<tbody>

</tbody>

</table>

</div>

<div>

<h4 class="modal-title float-left" id="modalLabel">Book Information</h4>

</div>

<div>

</div>

<label class="control-label" for="title">Title:</label>

</div>

<label class="control-label" for="author">Author:</label>

</div>

<label class="control-label" for="published_date">Date Published:</label>

</div>

</div>

</form>

</div>

<div>

</div>

<div>

</div>

<label class="control-label" for="title">Title:</label>

</div>

<label class="control-label" for="author">Author:</label>

</div>

<label class="control-label" for="published_date">Date Published:</label>

</div>

<button type="submit" class="btn submit-book btn-success">Submit</button>

</div>

</form>

</div>

<div>

</div>

<div>

</div>

<label class="control-label" for="title_edit">Title:</label>

</div>

<label class="control-label" for="author_edit">Author:</label>

</div>

<label class="control-label" for="published_date_edit">Date Published:</label>

</div>

<button type="submit" class="btn submit-book-edit btn-success">Submit</button>

</div>

</form>

</div>

</body>

</html>

Step 4: Handle the events. This file brings your page to life. It connects page events to the endpoints to complete the operation.

index.js

$(document).ready(function() {

/* Initialize the index */

var page = 1;

var current_page = 1;

var total_page = 0;

var display = "list";

/* Display Initial List of Books */

if(display=="list")

displayBookList();

/* View book */

$("body").on("click",".view-book",function(){

var id = $(this).parent("td").data('id');

var title = $(this).parent("td").prev("td").text();

var author = $(this).parent("td").prev("td").prev("td").text();

var published_date = $(this).parent("td").prev("td").prev("td").prev("td").text();

display = "details";

$("#id_view").text(id);

$("#title_view").text(title);

$("#author_view").text(author);

$("#published_date_view").text(published_date);

});

/* Create new book */

$('#create-book-form').on('submit', function(e){

e.preventDefault();

var form_action = $("#create-book-form").attr("action");

var title = $("#title").val();

var author = $("#author").val();

var published_date = $("#published_date").val();

display = "create";

if(title != '' && author != '' && published_date !=''){

$.ajax({

dataType: 'json',

type:'POST',

url: url + form_action,

data:{

title:title,

author:author,

published_date:published_date

}

}).done(function(data){

// Reset Form

$("#title").val('');

$("#author").val('');

$("#published_date").val('');

// Display new item on the list

displayPageEntries();

// Hide modal

$("#create-book").modal('hide');

toastr.success('Item Created Successfully.', 'Success Alert', {timeOut: 5000});

});

}else{

alert('All fields are required. Please make sure you fill out all fields correctly.')

}

});

/* Edit book */

$("body").on("click",".edit-book",function(){

var id = $(this).parent("td").data('id');

var title = $(this).parent("td").prev("td").text();

var author = $(this).parent("td").prev("td").prev("td").text();

var published_date = $(this).parent("td").prev("td").prev("td").prev("td").text();

display = "edit";

$("#title_edit").val(title);

$("#author_edit").val(author);

$("#published_date_edit").val(published_date);

$("#edit-book-form").find(".edit-id").val(id);

});

/* Update book */

$('#edit-book-form').on('submit', function(e){

e.preventDefault();

var form_action = $("#edit-book-form").attr("action");

var title = $("#title_edit").val();

var author = $("#author_edit").val();

var published_date = $("#published_date_edit").val();

var id = $("#edit-book-form").find(".edit-id").val();

if(title != '' && author != '' && published_date !=''){

$.ajax({

dataType: 'json',

type:'POST',

url: url + form_action,

data:{

id:id,

title:title,

author:author,

published_date:published_date

}

}).done(function(data){

// Reset Form

$("#title_edit").val('');

$("#author_edit").val('');

$("#published_date_edit").val('');

// Display new item on the list

displayPageEntries();

// Hide modal

$("#edit-book").modal('hide');

toastr.success('Item Updated Successfully.', 'Success Alert', {timeOut: 5000});

});

}else{

alert('All fields are required. Please make sure you fill out all fields correctly.')

}

});

/* Delete book */

$("body").on("click",".delete-book",function(){

var id = $(this).parent("td").data('id');

$.ajax({

dataType: 'json',

type:'POST',

url: url + 'api/delete.php',

data:{ id:id }

}).done(function(data){

displayPageEntries();

toastr.success('Item Deleted Successfully.', 'Success Alert', {timeOut: 5000});

});

/* Display initial list of all books with pagination */

function displayBookList() {

$.ajax({

dataType: 'json',

url: url+'api/index.php',

data: {page:page}

}).done(function(data){

total_page = Math.ceil(data.total/display_rows);

current_page = page;

refreshList(data.data);

$('#pagination').twbsPagination({

totalPages: total_page,

visiblePages: current_page,

onPageClick: function (event, pageL) {

page = pageL;

displayPageEntries();

}

});

}

/* Display books for current page*/

function displayPageEntries() {

$.ajax({

dataType: 'json',

url: url+'api/index.php',

data: {page:page}

}).done(function(data){

refreshList(data.data);

});

}

/* Refresh table list */

function refreshList(data) {

var rows = '';

$.each(data,function(key,value) {

rows = rows + '<tr>';

rows = rows + '<td>'+value.published_date+'</td>';

rows = rows + '<td>'+value.author+'</td>';

rows = rows + '<td>'+value.title+'</td>';

rows = rows + '<td data-id="'+value.id+'">';

rows = rows + '<button data-toggle="modal" data-target="#view-book" class="btn btn-primary btn-sm view-book">View Details</button> ';

rows = rows + '<button data-toggle="modal" data-target="#edit-book" class="btn btn-primary btn-sm edit-book">Edit Book</button> ';

rows = rows + '<button class="btn btn-danger btn-sm delete-book">Delete</button>';

rows = rows + '</td>';

rows = rows + '</tr>';

});

$("tbody").html(rows);

}

});

///////////////////////////////////////

Jquery

$(document).ready(function() {

$("#datepicker").datepicker({

dateFormat: 'yy-mm-dd'

});

})

JS:

$(document).ready(function () {

$("#datepicker").datepicker({

dateFormat: "yy-mm-dd",

onSelect: function () {

var date = $("#datepicker").val();

$.ajax({

type: "POST", //or GET. Whichever floats your boat.

url: "path/to/process_page.php",

data: { date: date },

success: function(data) {

alert(data);

//Write code here for you successful update (if you want to)

error: function() {

alert("Error.");

}

});

}

});

</script>

/////////////////////////////////////////////////////////

index.php

<?php

$connect = mysqli_connect("localhost", "root", "", "testing");

$query = "SELECT * FROM tbl_order ORDER BY order_id desc";

$result = mysqli_query($connect, $query);

<!DOCTYPE html>

<html>

<head>

<title>Webslesson Tutorial | Ajax PHP MySQL Date Range Search using jQuery DatePicker</title>

</head>

<body>

<h2 align="center">Ajax PHP MySQL Date Range Search using jQuery DatePicker</h2>

<h3 align="center">Order Data</h3><br />

</div>

</div>

</div>

<br />

<tr>

<th width="30%">Customer Name</th>

<th width="10%">Value</th>

<th width="12%">Order Date</th>

</tr>

<?php

while($row = mysqli_fetch_array($result))

{

<tr>

</tr>

<?php

}

</table>

</div>

</body>

</html>

$(document).ready(function(){

$.datepicker.setDefaults({

dateFormat: 'yy-mm-dd'

});

$(function(){

$("#from_date").datepicker();

$("#to_date").datepicker();

});

$('#filter').click(function(){

var from_date = $('#from_date').val();

var to_date = $('#to_date').val();

if(from_date != '' && to_date != '')

{

$.ajax({

url:"filter.php",

method:"POST",

data:{from_date:from_date, to_date:to_date},

success:function(data)

{

$('#order_table').html(data);

}

});

}

else

{

alert("Please Select Date");

}

});

</script>

filter.php

<?php

//filter.php

if(isset($_POST["from_date"], $_POST["to_date"]))

{

$connect = mysqli_connect("localhost", "root", "", "testing");

$output = '';

$query = "

SELECT * FROM tbl_order

WHERE order_date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."'

$result = mysqli_query($connect, $query);

$output .= '

<tr>

<th width="30%">Customer Name</th>

<th width="10%">Value</th>

<th width="12%">Order Date</th>

</tr>

if(mysqli_num_rows($result) > 0)

{

while($row = mysqli_fetch_array($result))

{

$output .= '

<tr>

<td>'. $row["order_id"] .'</td>

<td>'. $row["order_customer_name"] .'</td>

<td>'. $row["order_item"] .'</td>

<td>$ '. $row["order_value"] .'</td>

<td>'. $row["order_date"] .'</td>

</tr>

}

else

{

$output .= '

<tr>

<td colspan="5">No Order Found</td>

</tr>

}

$output .= '</table>';

echo $output;

}

Database

-- Table structure for table `tbl_order`

CREATE TABLE IF NOT EXISTS `tbl_order` (

`order_id` int(11) NOT NULL AUTO_INCREMENT,

`order_customer_name` varchar(255) NOT NULL,

`order_item` varchar(255) NOT NULL,

`order_value` double(12,2) NOT NULL,

`order_date` date NOT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

-- Dumping data for table `tbl_order`

INSERT INTO `tbl_order` (`order_id`, `order_customer_name`, `order_item`, `order_value`, `order_date`) VALUES

(1, 'David E. Gary', 'Shuttering Plywood', 1500.00, '2016-10-14'),

(2, 'Eddie M. Douglas', 'Aluminium Heavy Windows', 2000.00, '2016-10-08'),

(3, 'Oscar D. Scoggins', 'Plaster Of Paris', 150.00, '2016-09-29'),

(4, 'Clara C. Kulik', 'Spin Driller Machine', 350.00, '2016-09-30'),

(5, 'Christopher M. Victory', 'Shopping Trolley', 100.00, '2016-10-01'),

(6, 'Jessica G. Fischer', 'CCTV Camera', 800.00, '2016-10-02'),

(7, 'Roger R. White', 'Truck Tires', 2000.00, '2016-09-28'),

(8, 'Susan C. Richardson', 'Glass Block', 200.00, '2016-10-04'),

(9, 'David C. Jury', 'Casing Pipes', 500.00, '2016-09-27'),

(10, 'Lori C. Skinner', 'Glass PVC Rubber', 1800.00, '2016-09-30'),

(11, 'Shawn S. Derosa', 'Sony HTXT1 2.1-Channel TV', 180.00, '2016-10-03'),

(12, 'Karen A. McGee', 'Over-the-Ear Stereo Headphones ', 25.00, '2016-10-01'),

(13, 'Kristine B. McGraw', 'Tristar 10" Round Copper Chef Pan with Glass Lid', 20.00, '2016-09-30'),

(14, 'Gary M. Porter', 'ROBO 3D R1 Plus 3D Printer', 600.00, '2016-10-02'),

(15, 'Sarah D. Hunter', 'Westinghouse Select Kitchen Appliances', 35.00, '2016-09-29'),

(16, 'Diane J. Thomas', 'SanDisk Ultra 32GB microSDHC', 12.00, '2016-10-05'),

(17, 'Helena J. Quillen', 'TaoTronics Dimmable Outdoor String Lights', 16.00, '2016-10-04'),

(18, 'Arlette G. Nathan', 'TaoTronics Bluetooth in-Ear Headphones', 25.00, '2016-10-03'),

(19, 'Ronald S. Vallejo', 'Scotchgard Fabric Protector, 10-Ounce, 2-Pack', 20.00, '2016-10-03'),

(20, 'Felicia L. Sorensen', 'Anker 24W Dual USB Wall Charger with Foldable Plug', 12.00, '2016-10-04');

</script>

/////////////////////////////////////////////////////////

https://www.appsloveworld.com/mysql/100/213/update-mysql-database-fields-with-datepicker-jquery-ajax-php

'm assuming what you are asking is that you want the datepicker to update your MySQL table when you select on a date without having to refresh that page. The code you provided is a little confusing, but I put something together that may help.

I am guessing that the input field that's in your code is actually supposed to be the html where you are getting the value.

As for keeping it all on one single page... I don't think that will be possible. For best results, you will have to make a new .php page to accept the value of the datepicker, which is passed through the $.ajax call where it says date. If you did it on the same page, there's a possibility you may end up getting a swamp of unwanted html data returned to you via the data parameter in the success function, but it would most likely throw an error and get the error function callback.

In this example, I called the php page process_date.php.

HTML:

JS:

$(document).ready(function () {

$("#datepicker").datepicker({

dateFormat: "yy-mm-dd",

onSelect: function () {

var date = $("#datepicker").val();

$.ajax({

type: "POST", //or GET. Whichever floats your boat.

url: "path/to/process_page.php",

data: { date: date },

success: function(data) {

alert(data);

//Write code here for you successful update (if you want to)

error: function() {

alert("Error.");

}

});

}

});

</script>

Crud System Using Ajax jQuery PHP ...

Pause

Unmute

Remaining Time -13:00

Fullscreen

Now Playing

Crud System Using Ajax jQuery PHP MySQL part 2

Point of sales system Using Ajax jQuery PHP MySQL part 36