The Related URL shows one of 2 pages page I am CORRECTING by switching from my former WP table plugin to wpDataTables plugin. Please look at it and the site to understand what I'm doing. Be patient as this table loads; there are 36,317 rows/records. Once loaded it works great, but of course this will NOT work for most users and can only work on wide broadband connections with more powerful machines. I need some advise to point me in the right direction as I learn how to use this plugin and MySQL. I purchased this product because it can apparently handle large databases.
DESCRIPTION OF TABLE FUNCTION GOALS: This site will contain 2 pricing tables that change many times each year (10 to 20 times each year for each table). The 2 tables are each "rate decks" for VoIP telephone service. Each time my vendors provide new rates (normally a couple of times each month), I must update the tables so users will always have current information. Each row (record) in each table has 3 fields:
Country
Prefix
Price
Country is an alpha numeric field with name of the country (i.e. Bulgaria, Philippines, etc.). Prefix is a number field containing digits only, with anywhere from 1 to a maximum of 15 digits but normally has 4 to 7 digits. (Prefix is country code followed by additional digits signifying area codes and/or other prefixes, but can also be a single digit, such as "1" for United States. Additional digits for USA could be "1801599" as another example.) See my sample page.
One of the 2 tables will have a single row for the United States with a single flat rate calling the USA (i.e. only ONE record for the USA), and more than 36,000 additional rows/records showing ALL other countries of the world with their prefixes and prices. This table will have anywhere from 36,000 to 55,000 records/rows (currently 36,317 in the sample page above). The 2nd table will be the same as the first but also adding ALL prefixes within the USA, which brings the table size to about 200,000 records.
I need to provide a simple lookup query for front-end users so they can check per-minute call pricing for whatever country and/or phone prefixes they enter. The effect for the user should be:
Enter either country name or prefix
Result when country (or the first letters spelling that country name) is entered will be a list of all records showing the country, prefix and price.
Result when prefix is entered will be all prefixes that begin with the digits entered (i.e. if I entered "1801", it would return all entries that begin with those digits, showing country, prefix and price.
Prices are taken out to 5 decimal places. (i.e. fractional pennies such as $0.00369). All prices are USD. You can see a demo by visiting the Related URL above.
I know very little about MySQL although many years ago (decades) i learned dBase programming so i understand basic principles.
I need advice on how to set up the table and query for users. When reading descriptions about the kind of table, it seems "Generate a Query to MySQL Database" might be the right approach. All data is currently in 2 .csv files. I don't yet know how to set up MySQL databases, but i can learn.
Please give me some guidance before I begin and go down the wrong path . . .
I spent my whole weekend trying to learn MySQL and understand how to use wpDataTables. As of now, I have learned the MySQL basics, created 2 databases for the 2 calling plans for which I must provide pricing look-ups. One has 36,317 records and the other has more than 197,000 records.
As you will see, this is an International VoIP phone service providing discount calling rates to most countries on earth at discount and wholesale prices (hence the 2 different look-up queries needed.)
Each of the 2 databases need to provide 2 different look-ups:
1. Country
2. Prefix
User will either enter the country for which they wish to check prices, OR they'll enter the phone prefix (country code + first 3 to 6 digits of the phone number). In come countries, the result will produce thousands of results (for instance a search on "Philippines" would produce several thousand different rates; search on "United States" in the Wholesale table would produce 197,000+ rows, but a search on United States in the Discount Table produces only 1 row (since that table provides only 1 rate to the USA, but the wholesale table provides thousands.
Since the wholesale table can produce the extreme results, should we limit that one to prefix look-ups only? Of course, if a person entered only a "1" in the prefix query, they'd also get 197,000+ results. As an expert in your plugin, your thoughts and comments are welcome.
I am making good progress understanding how to format and present the table using your plugin and pulling MySQL queries . . .any suggestions on how to present the queries to the public?
if I understood you correctly you want to have two tables with three columns each which can be searchable by two columns? You have two CSV files with data which you successfully import into database.
Now if you do not know how to write a query it is not a problem. Just follow this tutorial which will guide you to create table from your MySQL tables. Once you create tables you need to choose appropriate filtering with possible enabling filter in form option to have those filters above the tables.
Table created like this will be editable from website, if you choose so, but please keep in mind that we do not have option for bulk editing such large amount of data and if you need to change number of rows frequently maybe it is better to do it on MySQL side and all the changes will be reflected on the your website
The Related URL shows one of 2 pages page I am CORRECTING by switching from my former WP table plugin to wpDataTables plugin. Please look at it and the site to understand what I'm doing. Be patient as this table loads; there are 36,317 rows/records. Once loaded it works great, but of course this will NOT work for most users and can only work on wide broadband connections with more powerful machines. I need some advise to point me in the right direction as I learn how to use this plugin and MySQL. I purchased this product because it can apparently handle large databases.
DESCRIPTION OF TABLE FUNCTION GOALS: This site will contain 2 pricing tables that change many times each year (10 to 20 times each year for each table). The 2 tables are each "rate decks" for VoIP telephone service. Each time my vendors provide new rates (normally a couple of times each month), I must update the tables so users will always have current information. Each row (record) in each table has 3 fields:
Country is an alpha numeric field with name of the country (i.e. Bulgaria, Philippines, etc.). Prefix is a number field containing digits only, with anywhere from 1 to a maximum of 15 digits but normally has 4 to 7 digits. (Prefix is country code followed by additional digits signifying area codes and/or other prefixes, but can also be a single digit, such as "1" for United States. Additional digits for USA could be "1801599" as another example.) See my sample page.
One of the 2 tables will have a single row for the United States with a single flat rate calling the USA (i.e. only ONE record for the USA), and more than 36,000 additional rows/records showing ALL other countries of the world with their prefixes and prices. This table will have anywhere from 36,000 to 55,000 records/rows (currently 36,317 in the sample page above). The 2nd table will be the same as the first but also adding ALL prefixes within the USA, which brings the table size to about 200,000 records.
I need to provide a simple lookup query for front-end users so they can check per-minute call pricing for whatever country and/or phone prefixes they enter. The effect for the user should be:
Prices are taken out to 5 decimal places. (i.e. fractional pennies such as $0.00369). All prices are USD. You can see a demo by visiting the Related URL above.
I know very little about MySQL although many years ago (decades) i learned dBase programming so i understand basic principles.
I need advice on how to set up the table and query for users. When reading descriptions about the kind of table, it seems "Generate a Query to MySQL Database" might be the right approach. All data is currently in 2 .csv files. I don't yet know how to set up MySQL databases, but i can learn.
Please give me some guidance before I begin and go down the wrong path . . .
Thank you.
Hi Richard,
I tried to visit your website but it gave me a countdown. Is there any way to bypass it and see the tables?
Sorry about that. Here's the by-pass URL that should let you see the public-facing site:
https://voifone.com?bypass=aug15
I spent my whole weekend trying to learn MySQL and understand how to use wpDataTables. As of now, I have learned the MySQL basics, created 2 databases for the 2 calling plans for which I must provide pricing look-ups. One has 36,317 records and the other has more than 197,000 records.
As you will see, this is an International VoIP phone service providing discount calling rates to most countries on earth at discount and wholesale prices (hence the 2 different look-up queries needed.)
Each of the 2 databases need to provide 2 different look-ups:
1. Country
2. Prefix
User will either enter the country for which they wish to check prices, OR they'll enter the phone prefix (country code + first 3 to 6 digits of the phone number). In come countries, the result will produce thousands of results (for instance a search on "Philippines" would produce several thousand different rates; search on "United States" in the Wholesale table would produce 197,000+ rows, but a search on United States in the Discount Table produces only 1 row (since that table provides only 1 rate to the USA, but the wholesale table provides thousands.
Since the wholesale table can produce the extreme results, should we limit that one to prefix look-ups only? Of course, if a person entered only a "1" in the prefix query, they'd also get 197,000+ results. As an expert in your plugin, your thoughts and comments are welcome.
I am making good progress understanding how to format and present the table using your plugin and pulling MySQL queries . . .any suggestions on how to present the queries to the public?
Hi Richard,
if I understood you correctly you want to have two tables with three columns each which can be searchable by two columns? You have two CSV files with data which you successfully import into database.
Now if you do not know how to write a query it is not a problem. Just follow this tutorial which will guide you to create table from your MySQL tables. Once you create tables you need to choose appropriate filtering with possible enabling filter in form option to have those filters above the tables.
Table created like this will be editable from website, if you choose so, but please keep in mind that we do not have option for bulk editing such large amount of data and if you need to change number of rows frequently maybe it is better to do it on MySQL side and all the changes will be reflected on the your website