I have set up several custom fields as checkboxes through amember 'add fields'. For example, one of the fields asks where they play poker online, and the checkboxes are the major poker sites (e.g. Full Tilt, Poker Stars, etc.) How do I use this data? I can see that it is stored in the amember_members database as serialized data. But say I wanted to do a query based on what they input. For example, I want to find the average amount spent at my site by users who play at full tilt. I know how to do sql queries to get the $$ and join info between tables, but I don't know how to determine if a user checked the box that said "full tilt" given the data format. Does anybody know any way to use the serialized checkbox data? I know how to unserialize data, but from there, all I know how to do is printf it to the screen, but I need to know how to use parts of it in a query (e.g. Select all users who play at full tilt). Second (somewhat related) question - can I add fields to amember_members through phpmyadmin or will that mess up the script? If I do my checkbox data that way, seems like it would be much easier. But if I do it that way and collect it as post data on the signup page, on which page would I put my php to save this data to the database? Thanks in advance for any help, this software is outstanding.
I have a similar type of setup (only not related to poker :-p) I export ALL of the data to CSV and do the reporting in Excel.
Unfortunately, that won't work for me, because I actually need to do it for queries the users will see, the $ was just one example (e.g., a dynamic "top 10" list of average scores on a game ranked by site where the person generally plays poker, so the users can compete for which site has the smartest players). BTW, other than my site being about poker, our customizations sound very similar. Just about every question I've had I've already found a topic through search where you either asked the same question I had, or answered it based on your setup.
A suggestion may be to executes the raw SQL query as a cron job (hourly, daily, weekly, whatever makes sense for your site) and dump the results to a different table altogether (with matching keys to allow for where user clauses).. This aggregation and normalization of the data will keep a heavy query from happening many times and provide a much faster response for your users executing them. On a side note, I'm doing a forklift to WordPress at the moment. Once the new site is up we should compare notes