How to create an Select Statement using an Additional field in appointments.php

Hi,

I have created an Additional fields in the admin by activating the "Additional Fields" add on. I have called the field Membership Number.

I wish to get the user id for a given Membership Number in appointments.php. Please can you tell me how to accomplish this? I cannot find which table my Additional fields are entered when I save an appointment?

Many thanks,
Ellis

  • Michael Bissett

    Hey @Ellis, hope you're doing well today! :slight_smile:

    I wish to get the user id for a given Membership Number in appointments.php. Please can you tell me how to accomplish this? I cannot find which table my Additional fields are entered when I save an appointment?

    Unfortunately, I can't say that I'm aware of a way presently for you to get the user ID from an additional field, this would require some custom coding.

    As for which table your additional fields are entered, you're looking inside the Dashboard, correct?

    While additional fields aren't shown as separate columns inside the "Appointments" page inside the Dashboard, If you click on "See Details and Edit", you should see the additional fields information there.

    Let me know if I can be of further assistance here please. :slight_smile:

    Regards,
    Michael

  • Ellis

    Hi Michael,

    I am looking at custom coding. My question is what database table is my Additional fields stored. They are not in the appointments table.

    I need to get the value from the Additional field entered in the Appointment confirmation page then perform a Select Statement in appointments.php to get the user_id.

    Obviously this wont work but something like.......
    $users = $this->db->get_results($this->db->prepare("SELECT * FROM {TABLE NAME???} WHERE member =%s", $membershipnumber));

  • Ellis

    Hi Ash,

    Thanks for your reply. My knowledge is obviously not good enough :slight_smile:

    Its good to know about the app-users-additional_fields.php file but I dont think that it actually answers my question.

    I am allowing my users to login as themselves but to book for their friends if they are members also.

    One of my additional fields "membership number" is unique to each user. I need to be able to return the user_id for a given membership_number. Would this be possible please? Ideally I will make this call in appointments.php.

    Many thanks,

  • Jose

    Hi @Ellis,

    Hope you are doing great today :slight_smile:

    I'm not clear in some points. I understand what you are asking, but I'm not sure why would you need that actually.

    From what you explained above, you already should be storing in some sort of relation table the unique 'membershipnumber' for each user. If not, how do the user knows what value to enter in that field?

    Now, if you are actually trying to retrieve the appointment created for a specific 'membershipnumber', the following code will do the trick:

    public function get_appointment_by_additional_field_value($field_name, $field_value){
                global $appointments;
                $apps_list = array();
                $appointments_data = get_option('appointments_data', array());
                if(empty($appointments_data))return false;
                foreach($appointments_data as $key => $additional_fields){
                    if($additional_fields[$field_name] == $field_value){
                        $apps_list[] = $appointments->get_app( $key );
                    }
                }
                return $apps_list;
            }
    
    $member_apps = $this->get_appointment_by_additional_field_value('additional1', '568');
                foreach($member_apps as $member_app){
                    print_r($member_app->user);//Print the ID of the user that booked the app.
                }

    In the example above, the function will return all the appointments that were booked for the user associated to the 'membershipnumber' 568.
    As showed in the example, you can get the user ID of the user who booked the appointment, as well as any other property of the appointment.

    Hope this helps!

    Please let me know if I missed something.

    Cheers!
    Jose

  • Ellis

    Thanks for your reply, it's really helpful!!

    Yes I am storing the membership-number, its a unique custom field collected during registration with another pluggin. I then copy it into the appointments+ Additional field - membership-number.

    The reason I want this code is that I am using the following select in my appointments.php and I need to populate the $user_id with either the logged-in user or another user if a membership-number is specified different to the logged-in user. Obviously getting the logged-in user is easy.

    $appts = $this->db->get_results($this->db->prepare("SELECT * FROM {$this->app_table} WHERE start>=%s AND end<=%s AND user=%d AND status='confirmed'", $startDate, $endDate, $user_id));

    So I think I can make a slight change to your code so that I break as soon as I find the first appointment for the given membership-number as all I really want is a user_id. Then insert it into my select statement?

    However, I was expecting a select statement in your solution so that makes me think I'm doing things wrongly. I must ask, should I really be writing select statements or should I be using the global arrays which seem to be present like you do in your solution. What would be the best way of achieving this? Sorry its been at least 10 years since I last wrote php!!!

    Many thanks,

  • Jose

    @Ellis,

    My solution above assumes that you know the 'membership-number', and it will bring all the appointments related to that membership-number.

    Now, in your last post, it would be the inverse; you are getting a list of appointments from the DB, and you want to include the membership-number additional field in the result. -just correct me if I'm wrong-

    This can't be done with a single SQL query, since the additional fields are stored as a serialized string, and therefore you can't relate/join the rows.

    That said, you need to first run your query as you are doing now, and then attach the corresponding additional field value to each appointment.

    You can do something like this:

    public function testsql(){
            //This is your code
            $appts = $this->db->get_results($this->db->prepare("SELECT * FROM {$this->app_table} WHERE start>=%s AND end<=%s AND user=%d AND status='confirmed'", $startDate, $endDate, $user_id));
    
            //Now loop through the result appointments and get the additional fields for each one
            foreach($appts as $app){
                $additional_fields = $this->get_additional_fields($app->ID);
                foreach($additional_fields as $field_name => $field_value){
                    $app->$field_name = $field_value;// attach the additional fields
                }
            }
            var_dump($appts);//This will print the modified result object. You should see now the 'membership-number' field on each appointment.
        }
    
        public function get_additional_fields ($appointment_id) {
        $appointments_data = get_option('appointments_data', array());
        return empty($appointments_data[$appointment_id])
            ? array()
            : $appointments_data[$appointment_id];
        }

    However, I was expecting a select statement in your solution so that makes me think I'm doing things wrongly. I must ask, should I really be writing select statements or should I be using the global arrays which seem to be present like you do in your solution. What would be the best way of achieving this? Sorry its been at least 10 years since I last wrote php!!!

    Using custom queries is fine as long as you use the abstraction provided by wordpress. This way you ensure that your queries are sanitized and your code will make use of any improvement when WP core is updated. You are doing this fine already, so no reason to change it.
    Of course, if there is already a defined method that is running the same query, you may want to use it and avoid duplicating code. This is basically to keep maintenance as simple as possible.

    With the option stored as serialized strings, there are a few methods that allows you to update, insert, and read the data easily. These methods, of course, are running SQL queries in the background. But there is really no need to re invent the wheel.
    You might like to take a look at this document:
    http://codex.wordpress.org/Options_API

    In a nutshell, your code looks fine and I don't see big issues with your PHP. You only need to get used to take advantage of wordpress as library/framework.

    Please let me know if the code above works as expected. It does in my lab. :wink:

    Cheers!
    Jose

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.