Title: No data found when using a view Last modified: April 1, 2021 --- # No data found when using a view * Resolved [busterland](https://wordpress.org/support/users/busterland/) * (@busterland) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/) * Hello, * I have created a view in MySQL but when I’m going on “Data explorer” tab there is “No data found” for this view. * Here is the sql code to create my view : * ``` CREATE OR REPLACE VIEW myview AS SELECT a.field1 AS "Field 1", CONCAT(a.field2, " / ", a.field3) AS "Field 2 / Field 3", REPLACE(FORMAT(b.price, 4, "de_DE"),"."," ") AS "Price", FROM tablea a, tableb b WHERE a.id = b.id ``` * If I delete REPLACE and FORMAT I can see the data in the “Data explorer” tab. But the price is not formated as I need. And in the “Data Publisher” tab I can’t see advanced settings, when I click on the box nothing appears bellow. * I don’t know how I can correct this, do you have any idea? * Thanks for your help. Viewing 8 replies - 1 through 8 (of 8 total) * [charles godwin](https://wordpress.org/support/users/charlesgodwin/) * (@charlesgodwin) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14265600) * I have had experiences with WPDA having problems with column names with spaces or special characters. It is known bug but I don’t know if it’s been fixed yet. Try changing Field 1 to Field_1. you can control the displayed column name in Explorer or publisher. * Thread Starter [busterland](https://wordpress.org/support/users/busterland/) * (@busterland) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14265908) * It’s working whithout spaces, but not for the line with : `REPLACE(FORMAT(b. price, 4, "de_DE"),"."," ") AS "Price"` * But it’s working without REPLACE function : `FORMAT(b.price, 4, "de_DE") AS " Price"` * In the database the price type is “decimal(50,4)” so I have numbers like : “1463975.9100” In the view, the price type is “varchar(102)” and numbers are like : “1.463.975,9100” thanks to the FORMAT function. * I would like to show it as : “1 463 975,9100” on the website. * Do you know how I can do that ? I have seen in the “Data publisher” tab, there is a field “Table options”. I have to add some code here ? Something like replace“.” with ” ” for the field “price” ? I’m not familiar with javascript… * Thanks for your help * [charles godwin](https://wordpress.org/support/users/charlesgodwin/) * (@charlesgodwin) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14266088) * I tested this in MySQL and the parser performed but complained with the double quote around Price but generated a result * But was happy with `REPLACE(FORMAT(b.price, 4, "de_DE"),"."," ") AS Price` or` REPLACE(FORMAT(b.price, 4, "de_DE"),"."," ") AS 'Price'` * Have you tested the create view code in a pure SQL environment, not using WPDA at all? * what you are attempting seems reasonable, I don’t know why it is failing. * No you can’t easily do it in the Table Options portion. you could change column names but not much else. * your original example has a comma after “Price”, that is illegal syntax, remove that comma. * The following is blessed by my MySQL syntax checker. Try it, but make it one line for your test. * ``` CREATE OR REPLACE VIEW myview AS SELECT a.field1 AS 'Field_1', CONCAT(a.field2, " / ", a.field3) AS 'Field_2_Field_3', REPLACE(FORMAT(b.price, 4, "de_DE"),"."," ") AS 'Price' FROM tablea a, tableb b WHERE a.id = b.id; ``` * Thread Starter [busterland](https://wordpress.org/support/users/busterland/) * (@busterland) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14266453) * I created the view directly in phpMyAdmin and I didn’t have an issue. * I tried with a single quote on ‘Price’ and without but I still have the message“ No data found” in the Data explorer. * I only have the error when I use the REPLACE function. When I use only FORMAT it’s ok. * Is there any other solution to get a result with a space for thousands? * Thread Starter [busterland](https://wordpress.org/support/users/busterland/) * (@busterland) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14266710) * I just tried something else. I have created the view in phpMyAdmin without REPLACE in MySQL : `FORMAT(b.price, 4, "de_DE") AS 'Price'` and then go into “Data designer” and used “Reverse engineering”, it’s ok. * Then I created again the view with adding REPLACE : `REPLACE(FORMAT(b.price, 4, "de_DE"),"."," ") AS 'Price'` And I got the following message : * ``` ( ! ) Warning: Invalid argument supplied for foreach() in ...\wordpress\wp-content\plugins\wp-data-access\WPDataAccess\Design_Table\WPDA_Design_Table_Form.php on line 2353 Call Stack # Time Memory Function Location 1 0.0000 413384 {main}( ) ...\admin.php:0 2 0.2945 12366240 do_action( ) ...\admin.php:259 3 0.2945 12366616 WP_Hook->do_action( ) ...\plugin.php:484 4 0.2945 12366616 WP_Hook->apply_filters( ) ...\class-wp-hook.php:316 5 0.2945 12367632 WP_Data_Access_Admin->data_designer_page( ) ...\class-wp-hook.php:292 6 0.2945 12367632 WPDataAccess\List_Table\WPDA_List_View->show( ) ...\class-wp-data-access-admin.php:587 7 0.2994 12383496 WPDataAccess\List_Table\WPDA_List_View->display_edit_form( ) ...\WPDA_List_View.php:462 8 0.3169 12416992 WPDataAccess\Design_Table\WPDA_Design_Table_Form->show( ) ...\WPDA_List_View.php:502 ( ! ) Warning: Invalid argument supplied for foreach() in ...\wordpress\wp-content\plugins\wp-data-access\WPDataAccess\Design_Table\WPDA_Design_Table_Form.php on line 2368 Call Stack # Time Memory Function Location 1 0.0000 413384 {main}( ) ...\admin.php:0 2 0.2945 12366240 do_action( ) ...\admin.php:259 3 0.2945 12366616 WP_Hook->do_action( ) ...\plugin.php:484 4 0.2945 12366616 WP_Hook->apply_filters( ) ...\class-wp-hook.php:316 5 0.2945 12367632 WP_Data_Access_Admin->data_designer_page( ) ...\class-wp-hook.php:292 6 0.2945 12367632 WPDataAccess\List_Table\WPDA_List_View->show( ) ...\class-wp-data-access-admin.php:587 7 0.2994 12383496 WPDataAccess\List_Table\WPDA_List_View->display_edit_form( ) ...\WPDA_List_View.php:462 8 0.3169 12416992 WPDataAccess\Design_Table\WPDA_Design_Table_Form->show( ) ...\WPDA_List_View.php:502 ``` * [charles godwin](https://wordpress.org/support/users/charlesgodwin/) * (@charlesgodwin) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14266873) * well then its a genuine problem. I’m not sure why but seems like a bug. You’ve done everything i would have tried. * Plugin Author [Passionate Programmer Peter](https://wordpress.org/support/users/peterschulznl/) * (@peterschulznl) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14267576) * Hi guys, * The Data Designer supports table and index management only. Views are not supported. Sorry! That is a feature planned for the future. As such you should not be able to reverse engineer views. In fact you should not even be able to select a view from the drop down list. * I did a little research to find out that the plugin allows to select views from a remote database. That was unintended! * [@busterland](https://wordpress.org/support/users/busterland/) I presume you are you using a remote database? * Surprisingly the Data Designer even lets you reverse engineer remote views and create a table from the reverse engineered definition. That bug is almost a feature! But what you get at the end is not a view, it is a table. An EMPTY table! * Sorry for the misunderstandig! I updated the code to make sure views are no longer added to the drop down list and prevent getting this error in the future. This is solved in the next release. * [@busterland](https://wordpress.org/support/users/busterland/) To create a view with WP Data Access you can write a script and execute it from the Data Explorer( button import data/execute script). * Thank you both for your contribution! 😊 Peter * Thread Starter [busterland](https://wordpress.org/support/users/busterland/) * (@busterland) * [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14268150) * Hi, * For the moment I’m working in localhost. * I have created the view from Data Explorer and now it’s working ! 🙂 Before that I created the view in phpMyAdmin but can’t see the data in data explorer. Don’t know why. * But it’s ok with Import functionnality. * Thanks a lot for your help, problem solved ! 😀 Viewing 8 replies - 1 through 8 (of 8 total) The topic ‘No data found when using a view’ is closed to new replies. * ![](https://ps.w.org/wp-data-access/assets/icon-256x256.png?rev=3299063) * [WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards](https://wordpress.org/plugins/wp-data-access/) * [Support Threads](https://wordpress.org/support/plugin/wp-data-access/) * [Active Topics](https://wordpress.org/support/plugin/wp-data-access/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/wp-data-access/unresolved/) * [Reviews](https://wordpress.org/support/plugin/wp-data-access/reviews/) * 8 replies * 3 participants * Last reply from: [busterland](https://wordpress.org/support/users/busterland/) * Last activity: [5 years, 2 months ago](https://wordpress.org/support/topic/no-data-found-when-using-a-view/#post-14268150) * Status: resolved