How to Create Tables in WordPress based on MySQL queries with wpDataTables Plugin

you creating my SQL based WP data tables my skill is the most popular database engine on the web because it's free because it supports huge databases and if you use it wisely it's doing all the operations with data in this huge database is really quickly and effectively also WordPress itself runs on my scale so it's pretty much native for WordPress tools such as WP data tables to support my scale also my skill is the way to go if you have a huge data set which will be mentioned in the next tutorial in this tutorial we will just go through creating MySQL base WP data tables in general so to create my skill based W data table from a data source you need to have this data source of course so you already need to have a table on MySQL side in this tutorial we are going to use this dummy employee table I've prepared dump also you can download this dump from our from our site really what is it dump basically it's a an file used for importing data from one MySQL installation to another it's a number of a number of inserts number of the search statements which you first create the table with the given structure then insert the data right here we have something like a hundred of rows made a little bit more I will show you how to import this dump using PHP my admin tool if you are working if you have some experience with web do and you are of course familiar with PHP myadmin because it's an excellent tool for managing MySQL servers databases tables dating these tables older than whatever so you don't have to use this SQL dump you can use your own tables I will just show it for users that have no previous experience to go really step-by-step to explain how this works so first of all you need to make sure you have PHP myadmin installed which I guess all of the modern hosts provide this and all of modern packages that you can use on your localhost such as month one for whatever so go to your localhost where you have or the host where you have WordPress with WP data tables installed and open the DHCP my admin tool then open the database that you previously configured on WP data tables site to be used for queries if you didn't configure this it will use the same database as your WordPress so you can just go and open this this database in our case this is WP data tables database okay so here is you can see all the MySQL tables that already exist but we are going to import a dump so we go to the import tab then go to choose a file and here is our dummy employees SQL dump file so we just open it and click on go and it will execute all the statements that are in this file we'll create the table and import all date in this so here's table called dummy employees and we have a MIDI I guess one hundred hundred of rows with birthdays they hired position last name first name and ID right here you can already see the query that returns all the rows from this day this is actually the query that we will need so we will just fetch all the data from from our table and use it in a WEA table so you can try to build it yourselves so add everything from dummy employees copies do you see that it works and returns data that we need you can also be more complicated like select everything from dummy employees where first-name is not like Colin for example or where birthdate is between year 190 70 and 100 1985 etc but here we'll use just very simple query not to complicate things okay so this is the first step preparing the data sources for all the table W data tables that are created from data sources right now this step is finished so we just go to the dashboard of our WordPress go to WP data tables add from data source and we say MySQL table example this case we use the MySQL query as the table type and you see we have a SQL editor here I've copied our query we can just paste it here or you can create it here I just recommend before you paste your queries try it first in PHP myadmin and see if it actually returns something otherwise you can get no data on WPD table side or SQL syntax problems it's hard to debug from here much easier to understand why it's not working on each p.m. I am inside because you can see the database structure so we don't need to do anything else on this tab which is quicksave double D data tables queries the SQL Server if it returns on a date and if it returned you can see that it reads the column structure and it guesses the date types so I guess that these two are dates that these are strings and this one position is also a string right now the table is already done ready for use as you think it's not nice to see the ID in the fronton so we just untick the invisible checkbox sorry I need to say now we will see the ID review but if I click Save we save the table then click on preview once again you can see that there is no ID here and that all our data is already here let's do some fine-tuning for the table I guess it will look nicer if we have some more readable displaying headers first name for the last name then I think for the dates it's much better to have date range filters instead of simple strings then and position and for position I guess it will be nice to have a co-ed box filter so now we save and preview once again see the table same but now we can filter the position using nice drop down you can also filter the values in the in the table using this date range Pickers so now we see that our table is all set we just need to insert it in our wordpress post or page or whatever we want it go create a new page my SQL based WP data table example say here's the list of employees you can click on the WP dating button here and choose our table that we created click OK click publish and click view page to see the page in the front-end and see here's the list of employees and everything that we prepared is here we can filter we can sort etcetera not very complicated in this example we are not using server-side processing which means that once my skill returns these hundreds of rows there passed to WP rate tables then WP data tables in sirs these hundreds of rows this hundreds of rows in the page and generates the table on the client side but if if we want to use if you want to use the W Canadian tables with a really large data set which has ten thousands of rows 100 thousands of rows we would need to use server-side processing feature which means that we will always fresh only ten records or 20 records at a time and do all the certain filtering pagination on the server-side this is the topic of our next tutorial one more thing I wanted to mention here if you are not familiar with my skill and you don't want to study how to prepare the queries for the for the table you can either use the W datatable constructor to create tables completely manually from WordPress admin panel or if you already have a MySQL table that you just don't know how to query correctly you can also use WP data table constructor to try to construct my skill where we have a great generator built in there and we have of course a tutorial on how to use it thanks for watching and see you in the next tutorials purchase WP data tables exclusively on code Canyon

Channel: wpDataTables

Views: 79,340

Keywords: WordPress, Tables, Tables in WordPress, Creating Tables in WordPress, Responsive Tables, wpDataTables, WordPress Plugin, Plugin

Id: PNKk7q1w8Yg

Length: 11min 27sec (687 seconds)

Published: Fri Jul 17 2015