• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 55
  • Last Modified:

Problem creating a where clause in MySQL

Hi,
      I have a piece of MySQL code which looks like:

SELECT
    u.id,
    u.user_registered,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_title' limit 1) as title,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = 'first_name' limit 1) as FirstName,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = 'last_name' limit 1) as LastName,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_email' limit 1) as Email,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_1' limit 1) as Address1,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_2' limit 1) as Address2,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_3' limit 1) as Address3,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_city' limit 1) as town,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_county' limit 1) as county,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_zip' limit 1) as postcode,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_phone' limit 1) as telephone,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_children_s_details' limit 1) as childrens_details,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_buzz_pdf' limit 1) as newsbuzz,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_bus_review' limit 1) as newsbom,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_monthly_e_newsletter' limit 1) as enewsletter,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_hear_about_us' limit 1) as hear_about_us,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_gift_aid' limit 1) as gift_aid,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_data_protection_options' limit 1) as data_protection,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = 'mgm_member_options' limit 1) as member_options
    FROM bbct_users u
	WHERE user_registered BETWEEN DATE_SUB(CURDATE(),INTERVAL 30 DAY) AND CURDATE()

Open in new window


Within the member_options is a serialised value for last_pay_date which gets populated if somebody renews their membership.  What I need to do is rather than check the registered users for the last 30 days, is check how many records have had the last_pay_date changed in the last 7 days?

How can I achieve this?

Thanks

Damian
0
damianb123
Asked:
damianb123
  • 3
1 Solution
 
NerdsOfTechTechnology ScientistCommented:
 WHERE `last_pay_date` BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE()

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
 SELECT COUNT(*)
 FROM bbct_users u
 WHERE u.last_pay_date BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE()

Open in new window

0
 
damianb123Author Commented:
Hi NerdsOfTech,
     The trouble with this is, the field for last_pay_date is within a serialised field called Member_Options, so it's not quite as easy as you've outlined above :-(

Damian
0
 
NerdsOfTechTechnology ScientistCommented:
Now the question is, which epoch is the serialized date derived from?

Please provide an example serialized date here and its actual date counter-part (YYYY-MM-DD). We will determine if the epoch of the serialized date is Microsoft (Excel, etc.) or Unix (mySQL, etc) based; then we can convert it and plug it into a query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now