building a wordpress api that calls multiple tables

1 Views

TL;DR – How do I tie information together from different tables to create an API for a plugin.

To give a run down of what I am doing. We are building an app and a website for a clients gym, the app, for now, only pulls in blog posts, calendar activities, allows them to add the activities to their phone, and a description of the facility, while the website goes more in depth about the gym, the trainers, and any events going on. It was decided that we should use WordPress for the admin panel for the website and the app, so they only need to do the work in one place.

Read More

For the app I am using WP REST API v2 to pull in the blog posts, which is the easy part. But the calendar has become extremely difficult. The theme we bought is Symetrio Gym and Fitness which comes with a calendar that we could use, and it almost matches what we were using in the app. So what we need to do is pull in the data that is entered into the calendar and use an API Call in the app to display the data.

So here is where things get complicated, the calendar uses multiple tables to pull in all it’s data. For the Trainer, Classes, and Room it creates a custom post type, that just houses the name of the trainer, class, or room. But for the dates, and times, and the data that it references, it uses two separate tables, one called schedule_ap_multi that has the date, the reference id, and the id of the class, that is referenced in posts.

The next table is called schedule_ap_multi_fields, that has the start time, the end time, the id that references which row to use in schedule_ap_multi, the id of the room post, the id of the trainer, and a description of the activity, but these are all in their own row, the only thing really tying them together in the ID for schedule_ap_multi.

here are some images of how the two tables are set up.

schedule_api_multi

schedule_api_multi_fields

I started to build an API for the calendar, but I can’t get any data from the tables, it seems I can only get data from Posts, and since the dates and start and end time are not posts, its difficult to get them. for the JSON object I am creating its structured like this: 

 protected function make_data( $post, $data ) {

 global $wpdb;

 $classesID = $wpdb->get_results('SELECT ID FROM wp_wtr_schedule_ap_multi',ARRAY_A);
 $classes = $wpdb->get_results('SELECT * FROM wp_posts WHERE post_type = "classes"',OBJECT);

 $date = $wpdb->get_results('SELECT * FROM wp_wtr_schedule_ap_multi');
 $data[ $post->ID ] = array(
            'title'             => 'the title',
            'type'              => $post->post_type,
            'room'              => 'the room',
            'instructor'        => 'the instructor',
            'description'       => 'the description',
            'start_date'        => 'start date',
            'end_date'          => 'end date',
            'image'             => 'image',
            'style'             => 'style',
   };
  return $date;

when I do $post-> it only gets the post_type of posts, and it’s been a challenge getting any other post_type that isn’t a post, when I use any of the wpdb commands I get the query back, but in full, and only if I use it as one of the JSON objects, it won’t return an array when I put it in

$data[ here ]=array()

What I am trying to do, to summarize, is tie all these objects together, to make one api call, hopefully, and fill in the calendar on the app side, to display the data I need for each activity created in WordPress. I am at a complete loss now to what I need to do, and no one else on my team has any experience with wordpress plugins or creating a custom API for wordpress.

Post Views: 1

Related posts

1 comment

  1. So after some 1-on-1 with a part-time senior dev where I work we finally figured it out. Here is our solution. 

    protected function make_data() {

        global $wpdb;

        $date = date("Y-m-d");//returns current date in Year-Month-Day format
        $queryResult = $wpdb->get_results("SELECT wp_wtr_schedule_ap_multi.id ,wp_posts.post_title, wp_wtr_schedule_ap_multi.date FROM wp_wtr_schedule_ap_multi INNER JOIN wp_posts ON wp_wtr_schedule_ap_multi.id_classes=wp_posts.ID WHERE wp_wtr_schedule_ap_multi.date >= '$date'");//Only pulls date on or after current date, so we don't pull everything back at once.

        $data = array();

        foreach ( $queryResult as $post ) {
            $fields = $wpdb->get_results("SELECT * FROM wp_wtr_schedule_ap_multi_fields WHERE id_ap_multi ='$post->id' ");// gets all th essential data from multi_fields that is associated with ID
            $trainNum = $fields[5]->value; //recieves Trainer ID for regex
            $trainerNum = preg_replace('/[^0-9]/s', '', $trainNum);//pulls out the random ";" and returns just the trainer number
            $trainer = $wpdb->get_row("SELECT * FROM wp_posts WHERE ID = '$trainerNum'");//gets all the data from the trainer row number
            $roomNum = $fields[4]->value;//gets the room number, for query function. did not like $fields[4]->value for some reason
            $room = $wpdb->get_row("SELECT * FROM wp_posts WHERE ID = '$roomNum' ");//returns the row info for the room id

            $class_data = array(    );
            $class_data['_id'] = $post->id;
            $class_data['title'] = $post->post_title;
            $class_data['room'] = $room->post_title;
            $class_data['instructor'] = $trainer->post_title;
            $class_data['start'] = date("{$post->date} {$fields[0]->value}:{$fields[1]->value}:00.000Z");
            $class_data['end'] = date("{$post->date} {$fields[2]->value}:{$fields[3]->value}:00.000Z");

            //$class_data['ref'] = $fields;//used to reference items returned
            $data[] = $class_data;
        };
    return $data;
    }

    the solution works, This is our reference point for how we created the API and endpoints to communicate to the calendar plugin. I still have a few items I need to call but otherwise it works pretty well. It’s a big sluggish when making the call, so I will need to come back to it at some point to see if I can clean it up so I am not making so many calls, but it seems kind of unavoidable to me. But I am probably wrong.

Comments are closed.