How to update an event in the database by dragging/dropping an external event in JavaScript and PHP?

Question

I'm developing a calendar by using the full-calendar JavaScript library. I've the front-end ready but I'm not sure on how to update the event data in the database, after it gets dropped on the calendar. I'm very new to JavaScript, I've referred to a few similar questions but didn't get exactly what I'm looking for.

Here's my code:

**index.php**

<link href='https://unpkg.com/@fullcalendar/core@4.4.0/main.min.css' rel='stylesheet' />
<link href='https://unpkg.com/@fullcalendar/daygrid@4.4.0/main.min.css' rel='stylesheet' />
<link href='https://unpkg.com/@fullcalendar/timegrid@4.4.0/main.min.css' rel='stylesheet' />
<script src='https://unpkg.com/@fullcalendar/core@4.4.0/main.min.js'></script>
<script src='https://unpkg.com/@fullcalendar/interaction@4.4.0/main.min.js'></script>
<script src='https://unpkg.com/@fullcalendar/daygrid@4.4.0/main.min.js'></script>
<script src='https://unpkg.com/@fullcalendar/timegrid@4.4.0/main.min.js'></script>


<div id='external-events'>
 <p>
<strong>Draggable Events</strong>
</p>
<div class='fc-event'>My Event 1</div>
<div class='fc-event'>My Event 2</div>
<div class='fc-event'>My Event 3</div>
<div class='fc-event'>My Event 4</div>
<div class='fc-event'>My Event 5</div>
<p>
<input type='checkbox' id='drop-remove' />
<label for='drop-remove'>remove after drop</label>
</p>
   </div>
    <div id='calendar-container'>
     <div id='calendar'></div>
    </div>
 ** main.js**
 document.addEventListener('DOMContentLoaded', function() {
 var Calendar = FullCalendar.Calendar;
 var Draggable = FullCalendarInteraction.Draggable;

var containerEl = document.getElementById('external-events');
var calendarEl = document.getElementById('calendar');
var checkbox = document.getElementById('drop-remove');


new Draggable(containerEl, {
itemSelector: '.fc-event',
eventData: function(eventEl) {
  return {
    title: eventEl.innerText
  };
}
});

var calendar = new Calendar(calendarEl, {
plugins: [ 'interaction', 'dayGrid', 'timeGrid' ],
header: {
  left: 'prev,next today',
  center: 'title',
  right: 'dayGridMonth,timeGridWeek,timeGridDay'
},
editable: true,
droppable: true, 
drop: function(info) {
  if (checkbox.checked) {
    info.draggedEl.parentNode.removeChild(info.draggedEl);
  }
},

});

calendar.render();
}); 

**add_event.php**

require 'database.php';
echo $_POST['title'];
$title = $_POST['title'];
$start = $_POST['start'];
$end = $_POST['end'];
$conn = DB::databaseConnection();
$conn->beginTransaction();
$sqlInsert = "INSERT INTO Events (title, start, [end]) VALUES    (:title, :start, :end)";
$stmt = $conn->prepare($sqlInsert);
$stmt->bindParam(':title', $title);
$stmt->bindParam(':start', $start);
$stmt->bindParam(':end', $end);


   if ($stmt->execute()) {
        $conn->commit();
        return true;
    } else {
        $conn->rollback();
        return false;
    }

Here's a link to the codepin which I referred to : https://fullcalendar.io/docs/external-dragging-demo I'm just not sure on how do I get the javascript to link to the insert function. So when the event gets dropped, it should get saved in the database.

You need to handle the `eventReceive` callback in fullCalendar. This will give you the associated event data from the dropped item. You can then send that data to add_event.php using AJAX. See https://fullcalendar.io/docs/eventReceive — ADyson, Mar 05 '20 at 15:31
P.S. **Warning:** Your code is vulnerable to SQL Injection attacks. You should use **parameterised queries** to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. (You've used a prepared statement, but without also using parameters, it gives no protection!) — ADyson, Mar 05 '20 at 15:33
Hey, can you please explain exactly how do I handle the event receive? i got the ajax part but before that eventreceive needs to be handled right? — AAM, Mar 05 '20 at 16:15
"how do I handle the event receive"...well in your calendar code you already handling the "drop" callback (i.e. `drop: function(info) {...etc`. It's the same pattern, just a different function and different data which is received. — ADyson, Mar 05 '20 at 16:20
hey, I just edited the code and added a function which I just wrote. It doesn't work but can you please check if that's how it has to be done and what is wrong in that piece of code? — AAM, Mar 05 '20 at 17:04
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/209102/discussion-between-aam-and-adyson). — AAM, Mar 05 '20 at 17:05
Did you read https://fullcalendar.io/docs/eventReceive? Does `function(start, end, allDay) {` look like `function( info ) {` to you? Where do you think those other parameters are going to come from? I'm guessing you copied this from somewhere without understanding it or checking it properly...where did you find this example? Most of it looks like code for fullCalendar version 3 (not version 4) and intended to work with a different callback. — ADyson, Mar 05 '20 at 17:07
No, I didn't randomly copied it but I had made a calendar just before this one which takes a text input from the user. — AAM, Mar 05 '20 at 17:08
Ok. But most of that code you posted in your eventReceive attempt would only work with fullCalendar version 3. You have to adapt your code to the current circumstances. You likely have a whole pile of errors in your browser's Javascript console when you run this (or at least, you'll start with one error and then keep getting more errors as you fix the earlier ones). — ADyson, Mar 05 '20 at 17:10
I'm not sure Exactly how it should be done as I'm very new to JavaScript. Could you please help and answer it for me? — AAM, Mar 05 '20 at 17:13
I can see that. But that doesn't stop you from at least comparing your code to the documentation and seeing if that part matches up! Anyway I will try and post a fuller answer later when I have more time. — ADyson, Mar 05 '20 at 17:16

ADyson · Accepted Answer · 2020-03-10T23:37:41.680

2

You need to handle the eventReceive callback in fullCalendar. This will give you the associated event data from the dropped item. You can then send that data to add_event.php using AJAX. See https://fullcalendar.io/docs/eventReceive for more details of the callback.

Something like this should work:

eventReceive: function( info ) {

  //get the bits of data we want to send into a simple object
  var eventData = {
    title: info.event.title,
    start: info.event.start,
    end: info.event.end
  };
  //send the data via an AJAX POST request, and log any response which comes from the server
  fetch('add_event.php', {
    method: 'POST',
    headers: {'Accept': 'application/json'},
    body: encodeFormData(eventData)
  })
  .then(response => console.log(response))
  .catch(error => console.log(error));
}

Note: I've used the modern fetch() function to perform the AJAX call, rather than the older XmlHttpRequest, or anything which would rely on jQuery or another external library.

And here's the code for the encodeForm function which the above code makes use of (credit to this site for the idea):

const encodeFormData = (data) => {
  var form_data = new FormData();

  for ( var key in data ) {
    form_data.append(key, data[key]);
  }
  return form_data;   
}

Demo:

document.addEventListener('DOMContentLoaded', function() {
  var Calendar = FullCalendar.Calendar;
  var Draggable = FullCalendarInteraction.Draggable;

  var containerEl = document.getElementById('external-events');
  var calendarEl = document.getElementById('calendar');
  var checkbox = document.getElementById('drop-remove');


  new Draggable(containerEl, {
    itemSelector: '.fc-event',
    eventData: function(eventEl) {
      return {
        title: eventEl.innerText
      };
    }
  });

  var calendar = new Calendar(calendarEl, {
    plugins: ['interaction', 'dayGrid', 'timeGrid'],
    header: {
      left: 'prev,next today',
      center: 'title',
      right: 'dayGridMonth,timeGridWeek,timeGridDay'
    },
    editable: true,
    droppable: true,
    eventReceive: function(info) {

      //get the bits of data we want to send into a simple object
      var eventData = {
        title: info.event.title,
        start: info.event.start,
        end: info.event.end
      };
      console.log(eventData);
      //send the data via an AJAX POST request, and log any response which comes from the server
      fetch('add_event.php', {
          method: 'POST',
          headers: {
            'Accept': 'application/json'
          },
          body: encodeFormData(eventData)
        })
        .then(response => console.log(response))
        .catch(error => console.log(error));
    }
  });
  calendar.render();
});

const encodeFormData = (data) => {
  var form_data = new FormData();

  for (var key in data) {
    form_data.append(key, data[key]);
  }
  return form_data;
}

<link href='https://unpkg.com/@fullcalendar/core@4.4.0/main.min.css' rel='stylesheet' />
<link href='https://unpkg.com/@fullcalendar/daygrid@4.4.0/main.min.css' rel='stylesheet' />
<link href='https://unpkg.com/@fullcalendar/timegrid@4.4.0/main.min.css' rel='stylesheet' />
<script src='https://unpkg.com/@fullcalendar/core@4.4.0/main.min.js'></script>
<script src='https://unpkg.com/@fullcalendar/interaction@4.4.0/main.min.js'></script>
<script src='https://unpkg.com/@fullcalendar/daygrid@4.4.0/main.min.js'></script>
<script src='https://unpkg.com/@fullcalendar/timegrid@4.4.0/main.min.js'></script>


<div id='external-events'>
  <p>
    <strong>Draggable Events</strong>
  </p>
  <div class='fc-event'>My Event 1</div>
  <div class='fc-event'>My Event 2</div>
  <div class='fc-event'>My Event 3</div>
  <div class='fc-event'>My Event 4</div>
  <div class='fc-event'>My Event 5</div>
  <p>
    <input type='checkbox' id='drop-remove' />
    <label for='drop-remove'>remove after drop</label>
  </p>
</div>
<div id='calendar-container'>
  <div id='calendar'></div>
</div>

P.S. You should also urgently fix the SQL injection vulnerability in your PHP code, as I mentioned in the comments above.

edited Mar 10 '20 at 23:37

answered Mar 05 '20 at 23:02

ADyson

57,178
14
51
63

Hey! I fixed the SQL and tested the above code. The error says undefined index for title, start and end. – AAM Mar 09 '20 at 14:19
Ok. That's a bit surprising. Have you used the browser's network tools to debug what is actually being sent in the fetch request? Have you tried logging the contents of $_POST to see what it actually contains when delivered to the server? We need a bit more specific info to debug it. – ADyson Mar 09 '20 at 16:54
Yes. Did that. In the params itself it is showing the title to be undefined. Here's how it looks like: title=undefined&start=Wed%20Mar%2011%202020%2000%3A00%3A00%20GMT-0400%20(Eastern%20Daylight%20Time)&end=null – AAM Mar 09 '20 at 18:09
That's odd. I made a runnable snippet above, combining my code and yours, and no such issue occurs - the title is defined. And the data transmits correctly. Although you might want to think about formatting the date differently, perhaps. – ADyson Mar 09 '20 at 19:10
I justed edited the sql above, any mistakes in there? It seems that file isn't getting the title, start and end. – AAM Mar 09 '20 at 19:18
The SQL isn't related to the POST data error. But still, your SQL should be `INSERT INTO Events (title, start, [end]) VALUES (:title, :start, :end)` so that you actually use the parameter names. The version you've got now is still inserting the actual values into the SQL string directly, which is still vulnerable to attack. The parameter names must be placed into the SQL string instead of the actual values, and then the actual values are added via the bindParam commands. – ADyson Mar 10 '20 at 09:28
Right. Fixed that. I still have the error msg saying the title, start, end are undefined – AAM Mar 10 '20 at 14:37
When I'm dropping the event on the calendar - in the networks tab when I click on the add_event.php file I can see the errors in the response tab: Notice: Undefined index: title in C:\wamp64\www\NewProject\add_event.php on line .. like these for the start and end too. – AAM Mar 10 '20 at 16:31
Ok so it seems the method I was using to encode the form data was not a good one. Apologies for that. I've corrected it now - the changes are: 1) new version of encodeFormData() and 2) removed the Content-type header from the fetch request. You should find it works better now. – ADyson Mar 10 '20 at 23:38
Getting this error: Uncaught PDOException: SQLSTATE[22007]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. – AAM Mar 11 '20 at 15:36
In the console- Object { title: "My Event 1", start: Date Wed Mar 11 2020 00:00:00 GMT-0400 (Eastern Daylight Time), end: null } – AAM Mar 11 '20 at 15:43
Like I said in an earlier comment, you probably will want to consider formatting the dates differently before you send it. I anticipated that a database might not enjoy that human-readable format. Probably something like yyyy-mm-dd hh:mm would work better. It's easy to google ways to format a date using javascript. – ADyson Mar 11 '20 at 15:51
Should I change the data type of start and end columns in the database? – AAM Mar 12 '20 at 17:57
if they're datetime columns already, then no. Anyway that's not the issue. It's how the database is parsing the incoming date string which is the issue. You need to send the date in a different format, as I already mentioned. – ADyson Mar 12 '20 at 22:19
Okay. I'm searching for how to do that. But if you know already I would appreciate the help. – AAM Mar 13 '20 at 16:03
I hardly need to repeat it here. https://stackoverflow.com/questions/3552461/how-to-format-a-javascript-date contains **a lot** of ideas / suggestions / solutions already, for example. There are many other places to find out, too. – ADyson Mar 13 '20 at 16:13
start = $.fullCalendar.formatDate(start, "YYYY-MM-dd HH:mm:ss"); I'm not sure on how to put this in my code – AAM Mar 13 '20 at 16:16
that won't work because it's for fullCalendar 3. There is an updated [formatDate](https://fullcalendar.io/docs/formatDate) method in v4, although it may not produce the result you need. Personally I'd recommend using momentJS, it's by far the easiest way. – ADyson Mar 13 '20 at 20:04
Okay. Since I couldn't figure exactly how to do that, I asked that as another question. I will accept this answer as the functionality could be of help to other stackoverflow users. Thank you – AAM Mar 16 '20 at 14:23

How to update an event in the database by dragging/dropping an external event in JavaScript and PHP?

1 Answers1