Thread

Hi, I’m having trouble with the query module.  I’m trying to find out when the member who is logged in purchased an item from the simple commerce module (that will determine what I display to them in terms of content). 

I’m successfully putting the current member id in a php variable using this (parsing on input):

<?php
$member_id = "{member_id}";
?>

and then I’m trying to query the database using this:

{exp:query sql="purchase_date as mydate, item_cost as myprice FROM exp_simple_commerce_purchases WHERE member_id = '<?php echo $member_id;?>'"}
Purchase Date: {mydate}
Item Cost: {myprice}
{if no_results}
Sorry, no results!
{/if}
{/exp:query}

Nothing is being returned…not even the no results data.

Any help would be amazing!  Thanks!

Ok, I just fixed something…I had left off the SELECT.  But now it only works when I type in a member_id in the code, I can’t seem to use the PHP.

{exp:query sql="SELECT purchase_date FROM exp_simple_commerce_purchases WHERE member_id = '<?php echo $member_id;?>'"}<br /> Purchase Date: {purchase_date format="%Y %m %d"}<br /> {if no_results}<br /> Sorry, no results!<br /> {/if}<br /> {/exp:query}

I know the PHP has the right value in it as I echoed it before this and it comes out as the right number.  I tried it with the id in single quotes and without.  Neither works.

Thx!
Sharon

Try this”

{exp:query sql="SELECT 
purchase_date 

FROM 
exp_simple_commerce_purchases 

WHERE 
member_id LIKE '%<?php echo $member_id ?>%'
"}

One more thing - you do not really need to use a php variable for the member id, since it is already an EE global variable.  So, this should work:

{exp:query sql="SELECT
purchase_date

FROM
exp_simple_commerce_purchases

WHERE
member_id LIKE {member_id}
“}

Thanks for the suggestion, but that didn’t work either.  For some reason it’s not putting my member id in there…when I type it directly, the query works, but obviously, I need it to be dynamic.

-S.

Yes, originally I tried the {member_id} but saw in another post that it wouldn’t be parsed in time to use it in the query module call.  I do have the php parsing on input, so I thought that would work…but it doesn’t seem to be…this was the exact suggestion on other forum post, but it’s not working for me.

s.

I wonder if I’ve discovered part of the problem in my last answer.  I need the member_id, but I don’t think I’m getting it in time for the QUERY.  I also have tried this:

<?php

$member_id = $SESS->userdata['member_id'];

?>

Thinking that {member_id} wasn’t evaluating in time.  But this doesn’t work either…I wonder, am I a step closer?  Or should I just do this all in PHP and ignore the Query module!??

S.

Try:

{exp:query sql="SELECT purchase_date FROM exp_simple_commerce_purchases WHERE member_id = '{member_id}'"}

Note the quotes around {member_id}.

Nope, already tried that.  The issue isn’t the syntax of the SQL…I have it working when I type in a valid member ID.  The issue is that I can’t seem to get the member ID dynamically when the template loads.

Any other ideas out there?

Thx!
Sharon

It may be a parsing order. If you’re using EE1, as it looks like from your code sample, let’s back up a bit. When you use PHP and call the Sessions class, you also need to call the $SESS global:

<?php

global $SESS;
$member_id = $SESS->userdata['member_id'];

?>

I’m using the latest version EE2, why did you assume otherwise?  Just curious.  I got the code by digging through the forums…I find the documentation for EE to be less than sufficient, so I never know which version the support is for…I know they split the forums, but you don’t pay attention to that in search results when you finally find a post that slightly resembles the “simple” problem you’ve struggled with all day!  Sorry for the rant.  Have been a programmer for 18 years so I’m totally frustrated when I can’t do a simple db query like this.

If someone can just tell me, how do I get the member ID, then use it in a query, that would be lovely!  I tried skipping the QUERY tag altogether and using straight php and that didn’t work either!  Bah!

Thanks so much for your help.  (I already tried the global $SESS declaration with no luck.)

Best,
Sharon

I assumed EE1 because you wrote $SESS->userdata. That’s for EE1. EE2 uses something a bit different:

$member_id = $this->EE->session->userdata['member_id']

Sessions documentation. No need to call the superglobal this time, either.

Bless you!  It worked!

-Sharon

Cheers! Glad to get you sorted.

Hi Sharon,

Glad that you got this sorted.

Cheers

Greg

@Michael, thanks for helping out.