php - Update Datepicker UI javascript file with SELECT from SQL table?
This is the structure of SQL table called "calende_bookings".
TABLE `calende_bookings`
- `id`,
- `id_item`,
- `the_date`,
- `id_state`,
- `id_booking`,
- PRIMARY KEY (`id`)
The goal is to get reservation dates which already existing in database and update Datepicker ui which will disable those date ranges in Datapicker, so users will not be able to pick them when they made reservations. Start date is always afternoon so id_state=3 is always start date (from: in outcome case), and date with last id_state=1 before date with id_state=2 is always last date (to: in outcome case). Two items will be displayed seperetely so I need the thing for one of them. This need to be outcome (dynamic dates).
var disabledArrFromTo = [
{ "from": "21-06-2021", "to": "26-06-2021" },
{ "from": "12-08-2021", "to": "14-08-2021" },
{ "from": .....}
]
This is my Datepicker code who actually work fine but when I wrote disabled date-ranges manualy. I used many example with simple dates, so no dates ranges, it disabled dates in Datapicker, but entire script stop with prices calculation.
$(window).load(function () {
$(document).ready(function() {
// An array of objects containing disabled date ranges
var disabledArrFromTo = [ { "from": "1-08-2021", "to": "19-08-2021" }, { "from": "23-08-2021", "to": "9-09-2021" } ];
$("#fromto").datepicker({
beforeShowDay: function(date){
//console.log(date);
// For each calendar date, check if it is within a disabled range.
for(i=0;i<disabledArrFromTo.length;i++){
// Get each from/to ranges
var From = disabledArrFromTo[i].from.split("-");
var To = disabledArrFromTo[i].to.split("-");
// Format them as dates : Year, Month (zero-based), Date
var FromDate = new Date(From[2],From[1]-1,From[0]);
var ToDate = new Date(To[2],To[1]-1,To[0]);
// Set a flag to be used when found
var found=false;
// Compare date
if(date>=FromDate && date<=ToDate){
found=true;
return [false, "red"]; // Return false (disabled) and the "red" class.
}
}
//At the end of the for loop, if the date wasn't found, return true.
if(!found){
return [true, "blue"]; // Return true (Not disabled) and no class.
}
},
buttonImage: "images/calendar_h.png",
buttonText: "Arrival",
buttonImageOnly: true,
changeMonth: true,
changeYear: true,
dateFormat: "dd-mm-yy",
altField: "#fromto",
altFormat: "dd-mm-yy",
gotoCurrent: true,
maxDate: "+2Y",
showOn: 'both',
yearRange: "-0:+2",
minDate: "+1D",
setDate: "+1",
numberOfMonths: 1,
onSelect: function (startDate, inst) {
var date = startDate.split("-");
$('#dd').val(date[0]);
$('#md').val(date[1]);
$('#yd').val(date[2]);
var date = $(this).datepicker('getDate');
if (date) {
date.setDate(date.getDate() + 3);
}
$("#tofrom").datepicker("option","minDate", date)
var startDate = new Date(parseInt($('#yd').val(),10),parseInt($('#md').val(),10)-1,parseInt($('#dd').val(),10));
$('.dirko').text($.datepicker.formatDate('dd. mm. yy', new Date(startDate)));
$('.ddirko').text($.datepicker.formatDate('D dd MM yy', new Date(startDate)));
}
});
$("#tofrom").datepicker({
beforeShowDay: function(date){
//console.log(date);
// For each calendar date, check if it is within a disabled range.
for(i=0;i<disabledArrFromTo.length;i++){
// Get each from/to ranges
var From = disabledArrFromTo[i].from.split("-");
var To = disabledArrFromTo[i].to.split("-");
// Format them as dates : Year, Month (zero-based), Date
var FromDate = new Date(From[2],From[1]-1,From[0]);
var ToDate = new Date(To[2],To[1]-1,To[0]);
// Set a flag to be used when found
var found=false;
// Compare date
if(date>=FromDate && date<=ToDate){
found=true;
return [false, "red"]; // Return false (disabled) and the "red" class.
}
}
//At the end of the for loop, if the date wasn't found, return true.
if(!found){
return [true, ""]; // Return true (Not disabled) and no class.
}
},
buttonImage: "images/calendar_h.png",
buttonText: "Departure",
buttonImageOnly: true,
changeMonth: true,
changeYear: true,
dateFormat: "dd-mm-yy",
altField: "#tofrom",
altFormat: "dd-mm-yy",
gotoCurrent: true,
maxDate: "+2Y",
showOn: 'both',
yearRange: "-0:+2",
minDate: "+1D",
numberOfMonths: 1,
onSelect: function (endDate, inst) {
var eDates = endDate.split("-");
$('#dr').val(eDates[0]);
$('#mr').val(eDates[1]);
$('#yr').val(eDates[2]);
var endDate = new Date(parseInt($('#yr').val(),10),parseInt($('#mr').val(),10)-1,parseInt($('#dr').val(),10));
$('.cirko').text($.datepicker.formatDate('dd. mm. yy', new Date(endDate)));
$('.ccirko').text($.datepicker.formatDate('D dd MM yy', new Date(endDate)));
}
});
});
I extracted dates from databese table in PHP for future dates with this ...
/*ITEM 1*/
SELECT
id_item,
id_state,
id_date,
DATE_FORMAT (the_date, "%e-%m-%Y") AS from_the_date
FROM calende_bookings
WHERE the_date > now()
AND id_item = 1
AND (id_state = 3 || id_state = 1)
ORDER by the_date ASC
My test PHP file connection with database.
<?php
$servername = "********";
$username = "***********";
$password = "***********";
$dbname = "***********";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo 'var disabledArrFromTo = [';
$sql = 'SELECT
id_item,
id_state,
the_date,
DATE_FORMAT (the_date, "%e-%m-%Y") AS from_the_date
FROM calende_bookings
WHERE the_date > now()
AND id_item = 1
AND (id_state = 3
|| id_state = 1)
ORDER by the_date ASC';
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo '"'. $row['from_the_date'] . '",';
}
} else {
echo "0 results";
}
echo '"31-12-2076"];';
$conn->close();
?>
Outcome look like this:
var disabledArrFromTo = ["25-06-2021","26-06-2021","31-12-2076"];
and I need something like this (startdate = date with item_state = 3 and enddate is last item_state = 1 before item_state = 2, check image on the top of the post):
var disabledArrFromTo = [
{ "from": "21-06-2021", "to": "26-06-2021" },
{ "from": "12-08-2021", "to": "14-08-2021" },
{ "from": .....}
]
Answer
Solution:
Try this: Prepare an array with all disabled dates
var disabledDates =['01/01/2020', '02/02/2020'];
function checkDayAvailable(day) { var fDay=day.getDate() + '/'+ (day.getMonth()+1) + "/" + day.getFullYear(); return inArray(fDay, disabled Dates) }
$('#iDate').datepicker({ beforeShowDay: checkDayAvailable });
Sorry, I'm on my phone, maybe I've made some syntax errors
So, for bringing data from db and update the JS array you can do it two ways:
Insert php code in you page. This code will get data from db and then echo it into JS code; This may look like:
<?php
//.....
//.....
$serverName = "your_server_name";
$connectionString = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionString );
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}
$sql = "SELECT * FROM calendar_bookings";
$res = sqlsrv_query( $conn, $sql );
if( $res === false) {
die( print_r( sqlsrv_errors(), true) );
}
$r="'";
while( $row = sqlsrv_fetch_array( $res, SQLSRV_FETCH_ASSOC) ) {
$r.=$row['the_date']."',";
}
sqlsrv_free_stmt( $res);
$r=substr($r, 0, -1);
?>
<script language="javascript">
var var disabledDates =[<?php=$r ?>];
By using an external service called by an ajax call. In this case you will need an external file which contains all php code. For example (supposing you are using jquery in your page): php file (let's call it server.php) will look like:
<?php
$serverName = "your_server_name";
$connectionString = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionString );
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}
$sql = "SELECT * FROM calendar_bookings";
$res = sqlsrv_query( $conn, $sql );
if( $res === false) {
die( print_r( sqlsrv_errors(), true) );
}
$r=[];
while( $row = sqlsrv_fetch_array( $res, SQLSRV_FETCH_ASSOC) ) {
$r[]=$row['the_date'];
}
sqlsrv_free_stmt( $res);
exit json_encode($r);
?>
your js code (supposing you are using jquery, just for simplyfing):
....
<script>
$.post(
'/server.php',
{},
function(data){
try {
var disabledDates = JSON.parse(data);
//now do whatever you need with
} catch(e){
console.warn(e);
}
}
);
</script>