Want to post records from one section to another BUT filtered selections

Hi,

We have a solution that might work.

Instead of storing a student against a lesson, it might be easier to have a lesson against a student.

First you need to create a list drop down that contains all of the students. So add a field called students and select list as the field type, and select Get options from database (advanced). Select your student table for selection tablename, select the num field for the option values and what ever you want for the option labels.

Next create a lesson list field. Then in the list options pull down select Get Options from MySQL query (advanced).

From the advanced filter select the students field you created in the first step. As this contains the student record number, and this is what we are basing the content of the filtered lessons on.

In the list options you need to add something like this:

<?php
$student = mysql_get("students", $ESCAPED_FILTER_VALUE);
?>

SELECT num, title
FROM `<?php echo $TABLE_PREFIX ?>lessons`
WHERE gender = '<?php echo mysql_escape(@$student['gender']);?>'
AND age = '<?php echo mysql_escape(@$student['age']);?>'

The mysql_get function will select the current student entry from the student section and store it in the variable $student.

We can then filter out what lessons appear in the drop down using the student variable. In the example above only lessons with the same gender and age as the pupil will be selected.

I have made a lot of assumptions in this example. I've assumed that the sections are called student and lesson and that they both contain age and gender fields. You will probably have to make few changes to get this code working in your example.

Thanks!

Hi,

I think I need a bit more information so that I can give you a more precise answer. What is the name of the
section your trying to create? I'm guessing it's separate from both the student and lesson sections?

Thanks

Yes, Greg. There are different sections: Accounts, Lessons and Drills. The client has 100's of drills entered into the database section titled Drills and each drill is checkbox selected for whether it is for male, female, junior (or multichoice) and for left handed, right handed (or both). Each drill can be fairly complex with it's own image uploads, linked videos, etc. I've attached a visual (.jpg) to help delineate what I'm trying to accomplish.

All help is appreciated.

Hi,

I would have a list field for the user to select from that contains all of the possible options. I would set it up like I have in screenshot1.png (see attached). With the values being the possible field values for gender and left or right handed.

Then for the filtered drills field I would create a list field that uses the field you have created above for the advanced filter, and uses the PHP explode function to seperate the two values from it. See screenshot2.png.

This is the PHP code I used for the SQL filter:

SELECT num, title
FROM `<?php echo $TABLE_PREFIX ?>drills`
<?php
if($ESCAPED_FILTER_VALUE){
$values = explode('AND', $ESCAPED_FILTER_VALUE);
$type = $values[0];
$hand = $values[1]; ?>
WHERE type LIKE '%<?php echo $type; ?>%' AND hand LIKE '%<?php echo $hand; ?>%'
<?php } ?>

I've made a few assumptions here, for example that that handedness and gender are stored in separate fields. You'll have to adapt the code so that it works for your situation.

If your looking for something more advanced then you will probably have to create a custom plugin. This is something we could look into creating for you, just send an e-mail to consulting@interactivetools.com with your requirements.

Thanks!

I would have a list field for the user to select from

So, you are saying this list field goes in the User Account Section, the Lesson Section or the Drills Section? I'm thinking you are suggesting it goes in the Drills Section and then within the User Account have separate fields for handedness and gender...so that when a Lesson is created, and the admin clicks on "Drill to do" field she gets a prepopulated drill set based upon handednes/gender of the User she's creating the lesson for. Is this correct?

Sorry, I misunderstood, I've explained how to attach a lesson, but not based on a user account.

The process for creating a lesson based on a user account isn't actually that much different.

So in your lessons section I would create a drop down so that the user can select a user account using the get options from database (advanced) option from a list field. In this example it will be called account, the values for the field should be the num option.

Then I would create a second list field for the drills in the lessons section, using the get options from MySQL query (advanced) as the list type. I've attached an image of how I would set it up. The advanced filter should be the account field that you created above. The query you need to run should look something like this:

SELECT num, title
FROM `<?php echo $TABLE_PREFIX ?>drills`
<?php
if($ESCAPED_FILTER_VALUE){
$user = mysql_get('accounts', $ESCAPED_FILTER_VALUE); ?>
WHERE type LIKE '%<?php echo $user['gender']; ?>%' AND hand LIKE '%<?php echo $user['handed']; ?>%'
<?php } ?>

So if there is an escaped value, its value should be the users account num. So it retrieves the user data from the accounts field using the mysql_get function. Then filter the drills table based on if the user is left or right handed and their account type.

I've assumed that the values for if the user is left and right handed and the users gender are stored in both the drills and user account table.

Thanks!

Thanks so much, Greg! I'm a novice programmer (as far as MySQL goes) but have used CMSB for several years now with great success.

This last post was much more clarifying for me, thank you. I've had her lessons set up, for years now, to use the advanced function pulling info from the user accounts section and the drills section into the individual lessons. Modifying all three based upon what you sent should be okay for me. I'll let you know how it goes! Thanks!

Greg, your referenced screenshot here (and the two above) error when clicked on.