jQuery UI Sortable Tutorial - Save Positions With Ajax & PHP & MySQL

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
In this video you will learn how to implement a jquery sortable but not only that I'm also going to show you how to save the new positions so that even if you refresh the page the positions will be still there, so stay at me Hey what's up guys Senaid here from codingpassiveincome.com the place where I help others to become a web developer much easier and faster than they will do it on they own so if that's something that interests you consider subscribing -> so now I'm here at my PHPStorm, I have created new project and also empty index.php file, so first thing that I'm going to do is I'll just declare and create the basic HTML document, okay and then maybe change the title to the jQuery sortable and then I'm going to include the bootstrap library, so I'll go to getbootstrap.com -> Download and then I will just use bootstrap CDN link, okay Your will just need the CSS, okay and also I'm going to include or maybe let's first design this, so we will have of course one container and maybe let's set margin top to the 100 pixels will have one row and that row I'll just justify content to the center, okay and then we will have one column, let's say MD 4 with offset 4, which means that I want to position everything to the center so help one column in the center with four and then from both side it will be four so it will be exactly in the center and then here I'm just going to create one table so I will say table class let's say table-stripped table-hovered and maybe table-border okay and in this table we need to have table-head okay and then of course table-body so now here in the table-head I have planned just to make a list of the countries that I have saved in my phpMyAdmin and my MySQL database so if I go here I have database jQuery sortable and then I have table country and as you can see here in this table it's ID, name and about so those are the three columns that I have and if you don't have this table you can download from the link in the description below, so here in the table-head I'm just going to create one row and maybe one column let's just say country name I mean a nothing special and here in the table body, we need to list all those countries so for that reason I will just write directly PHP code here because does the only thing that you are going to do and I'm not going to load dynamically because I know there's just a few countries in there so I will create a new variable connection and say new mysqli, localhost, root , password is empty then the table is jQuery sortable okay, I hope you can guys see this okay. So now we need to create one SQL so I'll say connection query and then we'll say select ID name from country and that's it okay, and then we all say while data equil SQL fetch array, we are going just to echo this table row so say table row close it and then column like this and here I will just specify the name, so I will say name okay, and this ID you are going to use later. I will show you in a moment, so let's just check what we have got so far, so when we refresh jQuery sortables, We have a problem This needs to be like this and here we go, that's our table With nice hover effect and that's it but as you can see now we are not able just to move those rows anyway so for that reason we are going to implement the jQuery sortable so I will go to the code.jquery.com and then I will use the minified version of the jquery okay I will paste it here and then we also need to use UI here and I will just go to the minified again copy and save okay so now once we have those two libraries we are able to write jQuery code that we will need so here I will say that the document dot ready which means we are waiting for the page to load we will set on the table table body sortable, okay and now we go here and refresh you will notice that I am able to move those rows but the problem is that if for example I move Afghanistan down here and hit refresh you can see that it's again loaded at the top which is the problem and we want to maintain so that each time when we make a change we will also change the position of the country in this database so that once we refresh it will always have the same position that we want so for that reason I'm going to add a new column here in this table so go to the structure add one column and then I will say position and I will hit save, okay so at the moment all positions are 0 and that is going to change soon okay so here I also say select ID, name from country but order by position okay so at the moment it will probably stay the same no it's not going to stay the same because it's all zeros but that's okay we will just for that reason be able to move and change the position we want okay so now what you want to do is to detect when some change happens so for that reason in the sortable so for those of you that want to learn more you can go to the jQuery sortable okay here and here you have clean explanation from the jQuery UI website about everything if you put API documentation you'll be able to see all the methods that they have and everything that you can't achieve it sir table but actually what is very important for us is update okay so as you can see it says that this event is triggered when the user stopped at sorting and the dump position has changed so that means that when we make this move then this update will fire okay so for that reason we write here so I will say update and then in here I'll write the function so as you can see here they say that we can receive event and UI we can use it but for our case it's not that much important so first thing that I want to make sure is I want that we print to the console this so that you know what it is and what you can get from this okay so if I hit refresh here F12 and then make a change as you can see I get table body and in here what we are interested is are the children and basically the children are all as you can see when I go over are all the rows that we have here so we can say here that we want to go through all rows or children's so I will say see this.children().each(function() ok and then we will see this like index so when we say now try to print to the console index you can see that basically that is this index here 0 1 2 3 and that's exactly when we go with the mouse over you can see it says it's 0 position is basically the index of the row so when we hit refresh make a change here we go that's it so this is exactly what we are looking at so for that reason in order for us to know is there any change in the position what I would love to do is to actually this table row to add some attributes so first attribute that I would have is data index which will represent the index in our table here and that is basically this ID okay so I will just copy ID and then the next attribute is data position which is basically our current position in table so here we'll say position okay and now if we refresh and go to the inspect we can see that every table row have its own index and data position which at the moment of course is zero okay so now what I want to do is that we actually here in the each when go through each and every table row I want that we actually compare the current index and the saved position in the database so if there is a difference we are going to update it and also we will save the new position so that we can make an update in the database okay so here I can say that we will check if this which is current row dot attribute data position different then index and as you can see it is starting from the zero so I'm increasing it for one here if it's different we are going to update so I'll say this this attribute data position is equal to the index plus one and then also I want to add a class changed or maybe updated whatever so the position is updated so let's see what will happen now okay so refresh and now if I make change here you can see now that all table rows has been updated the data position is correct now and we have added class updated but if we refresh and then if we try only to move this one now we can see that we have also updated the position for the first three because it is different but once we have in the table here we have the real positions it won't happen to all columns okay so now this part is working and all that we need to do is to actually after we finish this part here we will just make an update in the database so let's say we'll have one function say new positions and then here I will write it I'll say here function save new positions so let's say we'll have one variable positions equal an array and then I want that we go through all class updated updated dot each function and here we will say to this array you'll create a new array and then say let's say it will be position and or maybe index comma position so we will know that first element in this array is index and the second one will be position okay and that's it and then also I want that we remove class updated so that we don't update it each and every time okay so that's it now all that we need to do is to actually make an AJAX call to our server sorry yes okay so let's say URL will be this index dot PHP file the method will be post and then data type is going to be let's say text that's what it's returns from server and then let's say the data that we are sending to the server let's say update 1 and then we will send positions as positions so here I am using update just it's my way to have like a key that will be unique for every Ajax call just in case that you have multiple Ajax calls to the same page okay and then they will say here success function response that's what is returned by the server so let's say we will just print to the log but there are several returns ok and now of course we need to write this in the PHP so here at the top I will say if isset post and down let's say update which is the key that we used we will get the positions so say for each post positions I believe its positions yep as position what you are going to do we will just actually make make an update in the table so maybe let's get this line to the top because we will need it so I hold it here so we need here and also we will need down here so for that reason I have moved to the top and here I know that index is at position 0 and then our new position is at position 1 ok and then we will just say connection query update country set position equal new position where ID equal index and that's it and then we will say exit where ID equil index and thats is, and then we will just say exit success okay so let's make a test so I will refresh, open Network so when I change those two as you can see I have made a new post to the index.php here are all that we have sent ok cool and response is success but let's actually check our table so I'll go to the country and as you can see now the positions have been updated and let's see what will happen when we refresh the page so as you can see now the positions have been saved but let's now try to make a change for those two okay and now I mean we can check it here but basically as you can see the first one that has been changed is 32 which is basically the one of these we are not touching those first three that we didn't change the position and of course if I go down here and then make change only for those two okay check now this index and as you can see now we have made switch in the positions only for those two because we have implemented here that only on the changed we are adding the class updated and then here we are going to updated it and then saving in the new position and of course if we refresh it will stay the same so I can grab this move just to the top fresh and it stays here so pretty much guys that's it I hope that this is very easy to understand tutorial I hope that you liked it and if you do please like it and share with your friends and of course you have any questions feel free to ask in the comments below take care ! :)
Info
Channel: Coding Passive Income
Views: 28,022
Rating: undefined out of 5
Keywords: jquery, jquery tutorial, tutorial, jquery sortable, jquery tutorial for beginners, jquery tutorials, jquery ui, sortable, jquery sortable save with php mysql, jquery ui sortable, php, ajax, ui, jquery ajax, mysql, jquery sortable table, javascript, how to
Id: V1nYMDoSCXY
Channel Id: undefined
Length: 21min 48sec (1308 seconds)
Published: Fri Feb 16 2018
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.