Thread

Here’s my scenario:

My client offers 2 programs. Let’s call them Apples and Oranges.

The Apples program is sold to organizations, totally separate from the website. The client then manually registers the members, who are put in the Apples group. They have access only to the Apples template.

The Oranges program is sold through the site. Since Simple Commerce can’t do PayPal subscriptions, I have the PayPal code generated from the subscription button on the PayPal site in the template. They click to subscribe, pay on the PayPal site, and then I send them back to our site to a “Thank You” page, which gives them a link to register. When they register, they are put in the Oranges group.

The Oranges group can see everything in their group, plus everything in the Apples group. All this works fine.

Here’s the bugaboo.

My client is marketing the Oranges program to the Apples group.

Since I can’t have members in multiple groups (which would solve this problem), I need a way to change the group ID of an Apple member who purchases the Orange program.

I thought I would run an update query on the Thank You page, first using {if_logged_in} as a conditional.

The query I want is this:
UPDATE exp_members set group_id = ‘8’ where member_id = ‘{member_id}’

It works in phpMyAdmin, and from the SQL Query manager. I just can’t get it to work in the template, most likely because I’m coding it incorrectly because I don’t speak PHP very well.

So, some help on that would be appreciated.

The next step is figuring out if they are already registered on the site, because they might come and buy the Oranges program without ever logging in. If they try to register using their existing name and password, they’ll get an error.

So, on that same Thank You page, I thought I’d ask them if they are a member of the Apples group, and present the login form, and then return them to the same page, which I assumed would then run the update query and change their group because now they meet the conditional {if_logged_in}.

Is this approach stupid? Is there some easier way?

Thanks for any suggestions and the correct way to execute an update query in a template.

I’m a little disappointed that nobody’s helped me, but I’ve been muddling along.

I’ve been looking around, and found someone who had the exact same issue as I’m having, but alas, nobody replied to him, either!

Here’s the code in my template. If the member is logged in, I want to change their member group.

{if logged_in}
<?php
global $DB;
$mid = ‘{member_id}’;
$gid = ‘8’;
$data = array(‘group_id’ => $gid);
$sql = $DB->update_string(‘exp_members’,  $data,  “member_id = ‘$mid’”);
$DB->query($sql);
echo $sql;
?>
{/if}

The echo is:

UPDATE `exp_members` SET `group_id` = ‘8’ WHERE member_id = ‘47’

...which is correct, but the database is not updating.

IF I hardcode in the member id:

<?php
global $DB;
$mid = ‘{member_id}’;
$gid = ‘8’;
$data = array(‘group_id’ => $gid);
$sql = $DB->update_string(‘exp_members’,  $data,  “member_id = ‘47’ “);
$DB->query($sql);
echo $sql;
?>

It works, but it’s useless, as I won’t know the member id. What am I doing wrong???

My version of EE:
ExpressionEngine 1.6.0 - Build:  20070627

Any help is much appreciated, as I am going bananas.

Could be a parse order issue, meaning that the {member_id} is supplanted only after the PHP has been parsed: PHP literally gets to see “{member_id}”, not “47”. Play with PHP on In-/Output options.

Thanks Ingmar, for taking a look.

However, I get the same results whether PHP is on input or output.

I can also echo out the member id variable on just about every line, and it sees it.

Plus, if the query wasn’t being read, why would it echo out the correct thing?

Did you try to run the actual query in PHPMySQL or similar?

UPDATE `exp_members` SET `group_id` = ‘8’ WHERE member_id = ‘47’

I believe the back ticks around group_id are, at least, superfluous, try removing them.

Never mind, seems to be a PHP function. I still think only column names should be quoted that way, have you tired running that query directly?

yep, but it still doesn’t update.

{if logged_in}
<?php
global $DB;
$myid = ‘{member_id}’;
$sql = “UPDATE exp_members set group_id = ‘8’ where member_id = ‘$myid’”;
$DB->query($sql);
echo $sql;
echo ‘<br>’;
echo ‘member id=’. $myid;
?>
<br />
{exp:query sql="SELECT screen_name, group_id FROM exp_members WHERE member_id = '47' "}
{screen_name}

{group_id}
{/exp:query}

The echo:

UPDATE exp_members set group_id = ‘8’ where member_id = ‘47’
member id=47
debratest
6 ——should be ‘8’

Yes, if I run it in the SQL query manager, or in PHPMyAdmin, or in a separate PHP file, it all works. But I need it to work in a template!!!

Well, just in case anyone ever comes across this issue, I did get this working.

The problem was that EE wasn’t seeing the member_id variable. After much forum trolling, I stumbled upon the session class, and gave it go:

<?php
global $SESS, $DB;
$myid = $SESS->userdata[‘member_id’];
$query = $DB->query(“SELECT screen_name, group_id FROM exp_members WHERE member_id = ‘$myid’”);
$sql = “UPDATE exp_members set group_id = ‘8’ where member_id = ‘$myid’”;
$DB->query($sql);
?>

I have no idea WHY it works, but it IS working, so I’m a happy camper!