close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

I want to filter a recordset by a deabasket variable

Thread began 1/03/2014 6:29 am by John Langer | Last modified 1/09/2014 1:00 pm by Jason Byrnes | 919 views | 13 replies |

John LangerBeta Tester

I want to filter a recordset by a deabasket variable

I'm on the pp_confirm.php page and want to send an email to a list of people in a loop from a recordset.

My problem is I need the ID to filter the recordset by and the only place I can see that ID is returned by PayPal and shows up in the DEA Basket Variables as $DEAbasket->DisplayInfo("ID")

I've tried to add this to the recordset and tried SESSION GET POST COOKIE etc but none of them work. If I print the code on the page (echo $DEAbasket->DisplayInfo("ID");) it shows up correctly so I know that it is there to use so to speak but how do I filter the record set?

This is my URL parameter code (which doesn't work)

$colname_rsCourse = "-1";
if (isset($_GET['$DEAbasket->DisplayInfo("ID")'])) {
$colname_rsCourse = $_GET['$DEAbasket->DisplayInfo("ID")'];
}
mysql_select_db($database_connDeaLearning, $connDeaLearning);
$query_rsCourse = sprintf("SELECT CourseID, courseTeacher1, courseTeacher2, courseTeacher3 FROM course WHERE CourseID = %s", GetSQLValueString($colname_rsCourse, "int"));
$rsCourse = mysql_query($query_rsCourse, $connDeaLearning) or die(mysql_error());
$row_rsCourse = mysql_fetch_assoc($rsCourse);
$totalRows_rsCourse = mysql_num_rows($rsCourse);

Am I on the right track?

John

Sign in to reply to this post

Jason ByrnesWebAssist

in the recordset, set the filter to use a URL variable named DEAbasketID

that will cause the code to create the variable to look like this:

$colname_rsCourse = "-1";
if (isset($_GET['DEAbasketID'])) {
$colname_rsCourse = $_GET['DEAbasketID'];
}


add a line before that to set the DEAbasketID url variable to contain the value of $DEAbasket->DisplayInfo("ID"):

$_GET['DEAbasketID'] = $DEAbasket->DisplayInfo("ID");

Sign in to reply to this post

John LangerBeta Tester

Many thanks Jason, that did it.

All I've done so far though is get the variable to display on the page to prove it's picking it up. Tomorrow (UK time here) I'll try it out with the email addresses loop and let you know how I get on.

Sign in to reply to this post

Jason ByrnesWebAssist

you're welcome.

Sign in to reply to this post

John LangerBeta Tester

Hi Jason,

I need to pick your brains a little if I may. What I thought was a simple matter is proving a bit difficult for me.

Let me explain what the site is about and what I'm trying to do.

It's a teaching site (for a client) that sells online courses. The owner writes courses but there are also other course writers (teachers). The courses are for sale and I've used eCart 5 to construct this.

The "students" are allowed to buy more than one course at once (sometimes they are buyiig as many as 12) and each course has up to three teachers. The Teachers are just listed in the "users" table under the "UserGroups" field. This is held as an integer with a relationship to the UserGroups table.

We only know for sure when a course is sold when the "confirm payment" button is pressed on the "pp_confirm.php" page. At the moment that triggers an email (via UE) to a static email address (the owner of the site). That email lists ALL the courses sold.

What I'm trying to do is also either send that email or a new email to the teacher(s) of a course.

Supposing we have three teachers and three courses. On one PayPal payment transaction "Student A" buys three courses.

Course 1's teacher is Fred
Course 2's teacher is Fred and Ginger
Course 3's teacher is George

The course table has three fields "courseTeacher1", "courseTeacher2" and "courseTeacher3" These are populated from a list drawn from the users table filtered buy UserGroup (either 1-Administrator or 2-Teacher)..

So an email to is sent to Fred saying that his course 1 and course 2 has been sold to Student A
An email is sent to Ginger saying that her course 2 has been sold to Student A
And finally an email is sent to George saying that his course 3 has been sold to Student A (what the teachers then do is compile a monthly invoice based on this info and send to site owner for a percentage of the course fee).

On another occasion it might be only one course sold with one teacher or 12 courses sold with six teachers spead amongst them etc.

With your help (see the original query I made at the start of this thread) I can get the course ID('s) from the database but when I try to match the teachers to the courses and get an email to send with the info I'm lost.

Can you point me in the right direction on what is to me a very complicated issue. Perhaps I'm just going about it the wrong way.

Many thanks

Sign in to reply to this post

Jason ByrnesWebAssist

This is a little more than i can look into in the forum.

If you need help setting this uop, we can do this in a premiere support appointment:
http://www.webassist.com/premier_request.php

Sign in to reply to this post

John LangerBeta Tester

:-) Yeah I thought you'd say that.

OK I'll spend tomorrow trying to solve it myself otherwise I'll just have to set up the suppord request.I'm in the UK so my 5 PM tomorrow will be your 9 AM so I'll have all (my) evening to get the help.

Many thanks for your help.

Sign in to reply to this post

John LangerBeta Tester

Almost there

I've worked out how to get each individual course and then each teacher of that course printed on screen. Like this:

<?php while (!$DEAbasket->EOF()) { ?>

Course ID: <?php echo $DEAbasket->DisplayInfo("ID"); ?><br>
<?php $_GET['DEAbasketID'] = $DEAbasket->DisplayInfo("ID");
$colname_rsCourse = "40";
if (isset($_GET['DEAbasketID'])) {
$colname_rsCourse = $_GET['DEAbasketID'];
}
mysql_select_db($database_connDeaLearning, $connDeaLearning);
$query_rsCourse = sprintf("SELECT CourseID, CourseName, courseTeacher1, courseTeacher2, courseTeacher3, UserEmail FROM course INNER JOIN users ON courseTeacher1 = UserID OR courseTeacher2 = UserID OR courseTeacher3 = UserID WHERE CourseID = %s", GetSQLValueString($colname_rsCourse, "int"));
$rsCourse = mysql_query($query_rsCourse, $connDeaLearning) or die(mysql_error());
$row_rsCourse = mysql_fetch_assoc($rsCourse);
$totalRows_rsCourse = mysql_num_rows($rsCourse);?>
Course name: <?php echo $row_rsCourse['CourseName']; ?><br>
Course Teacher 1: <?php echo $row_rsCourse['courseTeacher1']; ?><br>
Course Teacher 2: <?php echo $row_rsCourse['courseTeacher2']; ?><br>
Course Teacher 3: <?php echo $row_rsCourse['courseTeacher3']; ?><br>

<?php $colname1_rsTeachersEmail = "7";
if (isset($row_rsCourse['courseTeacher1'])) {
$colname1_rsTeachersEmail = $row_rsCourse['courseTeacher1'];
}
$colname2_rsTeachersEmail = "9";
if (isset($row_reCourse['courseTeacher2'])) {
$colname2_rsTeachersEmail = $row_reCourse['courseTeacher2'];
}
$colname3_rsTeachersEmail = "-1";
if (isset($row_reCourse['courseTeacher3'])) {
$colname3_rsTeachersEmail = $row_reCourse['courseTeacher3'];
}
mysql_select_db($database_connDeaLearning, $connDeaLearning);
$query_rsTeachersEmail = sprintf("SELECT UserID, UserEmail, UserFirstName FROM users WHERE UserID = %s OR UserID = %s OR UserID = %s", GetSQLValueString($colname1_rsTeachersEmail, "int"),GetSQLValueString($colname2_rsTeachersEmail, "int"),GetSQLValueString($colname3_rsTeachersEmail, "int"));
$rsTeachersEmail = mysql_query($query_rsTeachersEmail, $connDeaLearning) or die(mysql_error());
$row_rsTeachersEmail = mysql_fetch_assoc($rsTeachersEmail);
$totalRows_rsTeachersEmail = mysql_num_rows($rsTeachersEmail);?>

<?php do { ?>
Teachers email: <?php echo $row_rsTeachersEmail['UserEmail']; ?><br>
<?php } while ($row_rsTeachersEmail = mysql_fetch_assoc($rsTeachersEmail)); ?>
<?php $DEAbasket->MoveNext();?><br>
<?php }?>

That last bit
<?php do { ?>
Teachers email: <?php echo $row_rsTeachersEmail['UserEmail']; ?><br>
<?php } while ($row_rsTeachersEmail = mysql_fetch_assoc($rsTeachersEmail)); ?>

gives me the emails. The whole thing gives me the emails needed for each course in a loop.

Now all I need to work out (and at the moment I haven't a clue) is how to get those emails into the BCC field of the email when they are not in a recordset.

Sign in to reply to this post

Jason ByrnesWebAssist

create a variable that stores the emails in a comma separated list, then use that variable in the UE behavior.
<?php $emails = "";
<?php do { ?>
Teachers email: <?php echo $row_rsTeachersEmail['UserEmail']; ?><br>
<?php $emails .= $row_rsTeachersEmail['UserEmail'].","; ?>
<?php } while ($row_rsTeachersEmail = mysql_fetch_assoc($rsTeachersEmail)); ?>

Sign in to reply to this post

John LangerBeta Tester

Thanks Jason, that's definitely a pointer. I'll have a go tomorrow. Bed time for me :-)

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...