Filter a backend <select> list based upon logged in user

8 posts by 2 authors in: Forums > CMS Builder
Last Post: January 9, 2015   (RSS)

By pgplast - January 8, 2015

I have a section editor that contains a <select> field called "patient," which presents a drop down list of names from a "patients" table. I would like to be able to filter the list of patients based upon which doctor user is logged in and using the backend interface. I have set up a many-to-many table ("doctor_facility") that relates doctor user nums to facilities, and the "patients" table has a "facility" field that assigns each patient to a facility. I want the doctor user to see only the patients from the facilities to which he is assigned (in the "doctor_facility" table).

For the patient pulldown menu I have chosen
"Get options from MySQL query " and have used the following code.


<?php
$doctor_facilityRecords = mysql_select("doctor_facility","doctor=".$CURRENT_USER['num']);
$the_list = "";
foreach($doctor_facilityRecords as $doctor_facilityRecord)   {
   $the_list .= $doctor_facilityRecord['facility'] . ", ";
}
?>  
SELECT num,CONCAT_WS(", ",last_name,first_name)
  FROM `<?php echo $TABLE_PREFIX ?>patients`
WHERE facility IN <?php echo $the_list;?>
ORDER BY last_name

This produces an error :
There was an error creating the list field 'test_list'. MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1, ORDER BY last_name' at line 3

Note: 1 is the correct num for the facility that the logged in doctor is assigned to

----------
Is it possible to do what I am trying to do, and if so, can you help with my MySQL error?

Thanks.

pgplast

By claire - January 8, 2015

Hey there

You're missing a set of round brackets there in the WHERE clause. Change the query like so:

SELECT num,CONCAT_WS(", ",last_name,first_name)
  FROM `<?php echo $TABLE_PREFIX ?>patients`
WHERE facility IN (<?php echo $the_list;?>)
ORDER BY last_name

You might need to take off the last comma in $the_list as well if you're still seeing errors.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By pgplast - January 8, 2015

Hi, Claire... and thanks!

I tried to address your suggestions, but still find error. Here is what I used........

----------------

<?php $doctor_facilityRecords = mysql_select("doctor_facility","doctor=".$CURRENT_USER['num']);
$the_list = "";
foreach($doctor_facilityRecords as $doctor_facilityRecord)   {
   $the_list .= $doctor_facilityRecord['facility'] . ", ";
}
if (substr($the_list, -1, 1) == ',')
{
  $the_list = substr($the_list, 0, -1);
}
?>  
SELECT num,CONCAT_WS(", ",last_name,first_name)
  FROM `<?php echo $TABLE_PREFIX ?>patients`
WHERE facility IN (<?php echo $the_list;?>)
ORDER BY last_name

---------------------

Tell me what you think... thanks!

By claire - January 9, 2015

Can you let me know what error you see?

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By pgplast - January 9, 2015

I created a new field called "test_list" inside the table that also has the "patient" dropdown.

I used the code in my last posting. When you click "create" to make a new record, the application fails to make the test_list, and gives the errror:

There was an error creating the list field 'test_list'. MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY last_name' at line 3

Thanks.

By claire - January 9, 2015

I think I'd better see this myself. It's likely a quick fix though. Can you send in a support request using the email form?

https://www.interactivetools.com/support/email_support_form.php

I'll pick it up and take a look at the list code. I think it's probably a quick fix.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By claire - January 9, 2015

It's always something small - I was just looking at the ticket you sent in as well! I'll close it for now. Please let us know if you have any more issues.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/