Numerical values – how sorting and filtering?

When you are working with currency (money) values in the database, it is a good idea to set the datatype to DECIMAL(10,2) so you are getting two places after the decimal point. This way, you can filter and sort the values correctly. If you don’t want the decimal paces, use BIGINT.

The filter you are using is correct, try testing it with only one of the statements to see if that is working. This is working in my tests, so you should be able to get this working.

Try this:

[pdb_total filter="debt_month!0"]

That will help you understand the results, because when you use an OR statement, you are likely to get all your records because more of them will match the filter.

Hi,

Thank you for answer. The decimal type will best for money values. Please explain what field in database I need to edit because I’m afraid to damage the database.

In database I found 3 tables from your plugin: wp_participants_database, wp_participants_database_fields and wp_participants_database_groups.

I think that need to look wp_participants_database_fields table. Further, for example I want to edit the debt_month field. This field contains next columns, types and values:

id int(3) 24
order int(3) 4
name varchar(64) debt_month
title tinytext Debt1
default tinytext 0
group varchar(64) My group1
help_text text Debts on membership fees
form_element tinytext text-line
values longtext
validation tinytext no
display_column int(3) 3
admin_column int(3) 0
sortable tinyint(1) 1
CSV tinyint(1) 1
persistent tinyint(1) 0
signup tinyint(1) 0
readonly tinyint(1) 1

Do I think correct that I must change the value of form_element column from “text-line” type to “DECIMAL(10,2)” or what changes I must do?

Regards,
Arnold

The table you want to change is the main table wp_paticipants_database because that is where the data is stored and you want to change how the data is stored. The datatype for text fields is TINYTEXT, for your currnecy fields, change it to DECIMAL(2,10).

Hi,

Thank you! I understood.
Main table wp_paticipants_database must open in tab “Structure”. I changed type to DECIMAL(10,2) and now shortcode works correctly.

Regards,
Arnold

Hi,

After last update to v.1.6.0 the construction, described above stopped working:
[pdb_total filter="debt_month!0|debt_goal!0"]
and
[pdb_total filter="debt_month!0"]

Regards,
Arnold

I’ve submitted a bug report, this should not be happening.

OK, here is your fix.

In the plugin file PDb_List_Query.class.php on line 635, you’ll see:

$operator = preg_match('#^([^\2]+)(\>|\<|=|!|~)(.*)$#', $statement, $matches);

change this to:

$operator = preg_match('#^([^\2]+)(!=|\>|\<|=|!|~)(.*)$#U', $statement, $matches);

Now, change your shortcode to use this filter:

filter="debt_month!=0"

that will filter out all records with a debt_month value of 0

Thank you very much!
It’s works!
Can you include this changes of PDb_List_Query.class.php to next release?

Regards,
Arnold

yes it’s part of the next update.

Thanx, you are great!