No data found when using a view

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.

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

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;

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?

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

well then its a genuine problem. I’m not sure why but seems like a bug. You’ve done everything i would have tried.

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 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 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

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 ! ??