How to get data from Wordpress $wpdb into React Gutenberg Blocks Frontend?

Question

I am using $wpdb to do a mysql query and get the data from a php file.

$results = $wpdb->get_results("SELECT * FROM table_name"); 

Now, I have registered a Gutenberg block with the wordpress api..

the component is a .js file, which uses (Wordpress React) under the hood:

import { registerBlockType } from "@wordpress/blocks";
import { __ } from "@wordpress/i18n";
import edit from "./pruefungenEdit";

registerBlockType("mytheme-blocks/pruefungen", {
    title: __("Pruefungen Block", "mytheme-blocks"),
    description: __("Display a Table of exams", "mytheme-blocks"),
    category: "mytheme-category",
    icon: {
        background: "#f03",
        foreground: "#fff",
        src: "dashicons-database",
    },
    keywords: [__("pruefungenBlock", "mytheme-blocks")],
    edit: edit,
    save: () => {
        return <p>Test</p>;
    },
});

This is my React component that I use for above edit() function:

import { Component } from "@wordpress/element";
import { withSelect } from "@wordpress/data";
import { __ } from "@wordpress/i18n";

class PruefungenEdit extends Component {

    render() {
        //where to get the data from the query in .php file??
        return <div>Data...</div>;
    }
}

export default PruefungenEdit;

I can't use php code obviously inside my .js react file.. I thought about setting up a small nodejs server that queries my localhost mysql table, and send that data via custom api route. THen I query that route on my react frontend and display the data... but that looks like too much workaround to me, I mean php is itself some kind of backend, does Wordpress not offer that possibility to query mysql data and send it to the frontend somehow?

My question now is, how do I get the data from the php file into my gutenberg Block that I registered for the block editor frontend?

EDIT - SOLUTION: It was easy to do with registering a custom endpoint and just querying that endpoint inside react frontend...

add_action( 'rest_api_init', function () {
  register_rest_route( 'wp/v1', '/employees', array(
    'methods' => 'GET',
    'callback' => 'get_employees'
    ));
});

function get_employees() {
    global $wpdb;
    $xmlArr = getparsedXMLArray();
    $uniqueIDArr = getUniqueID($xmlArr);

    $kurs_db = $wpdb->get_results(" SELECT DISTINCT kurs_ort_id, kurs_ort FROM ".$wpdb->prefix . "bsa_location ");
    $objJsonDocument = json_encode($kurs_db);
    $pruefungen = json_decode($objJsonDocument, TRUE);
    $foundPlaces = getAssociatedPlaces($pruefungen, $uniqueIDArr);

    $finishedData = addPlace($xmlArr, $foundPlaces);
    return $finishedData;
}

Frontend stuff:

import { Component } from "@wordpress/element";
import { __ } from "@wordpress/i18n";
import apiFetch from "@wordpress/api-fetch";

class PruefungenEdit extends Component {
    constructor(props) {
        super(props);
        this.state = {
            error: null,
            isLoaded: false,
            employees: [],
        };
    }

    componentDidMount() {
        try {
            apiFetch({
                path: "/wp/v1/employees",
            }).then((employees) => {
                this.setState({ employees, isLoaded: true });
            });
        } catch (error) {
            console.log(error);
            this.setState({
                error: error,
                isLoaded: true,
            });
        }
    }

    render() {
        const { error, isLoaded, employees } = this.state;
        if (error) {
            return <div>Error: {error.message}</div>;
        } else if (!isLoaded) {
            return <div>Loading...</div>;
        } else {
            return (
                <div className={"container"}>
                    <div className="row">
                        <div className="col-xs-12">
                            <table
                                id="pruefungentable"
                                className="table striped dark"
                                style={{ width: "100%" }}
                            >
                                <thead className="thead-dark ">
                                    <tr scope="row" className="table-danger">
                                        <th>#</th>
                                        <th>{__("KursID")}</th>
                                        <th>{__("KursOrt")}</th>
                                        <th>{__("Modul")}</th>
                                        <th>{__("Datum")}</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    {employees.map((employee, index) => (
                                        <tr key={index}>
                                            <td>{index}</td>
                                            <td>{employee.KursID}</td>
                                            <td>{employee.KursOrt}</td>
                                            <td>{employee.Modul}</td>
                                            <td>{employee.Datum}</td>
                                        </tr>
                                    ))}
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            );
        }
    }
}

export default PruefungenEdit;

Answer 1

Actually, there are many ways to do what you asked, but I'm going to give you an example based on this example on the official React website, which uses AJAX with a class-based component (but there's also an example there using function component with hooks).

So for the PHP part, we'll create a custom REST API endpoint and just for testing purposes, we're simply going to return an array with two items, each having three properties: id, name and price.

As for the AJAX part, we'll use the apiFetch() in the @wordpress/api-fetch package to fetch data from the custom endpoint — you could just use the native fetch() function in JavaScript, but with apiFetch(), it makes it easy if your endpoint requires/needs authentication because you don't need to worry about the cookie nonce since it's already included by apiFetch().

The Code

• PHP — Register the custom REST API endpoint:

  add_action( 'rest_api_init', 'my_rest_api_init', 10, 1 );
  function my_rest_api_init() {
      register_rest_route( 'my-plugin/v1', '/items', array(
          'methods'             => 'GET',
          'permission_callback' => '__return_true', // *always set a permission callback
          'callback'            => function ( $request ) {
              return array(
                  array( 'id' => 1, 'name' => 'Apples', 'price' => '$2' ),
  
                  // I used is_user_logged_in() so you can see that apiFetch() by default
                  // sends the cookie nonce when making an API request.
                  array( 'id' => 2, 'name' => 'Peaches', // wrapped
                      'price' => is_user_logged_in() ? 'FREE :)' : '$5' ),
              );
          },
      ) );
  }
  

• JS and AJAX — The PruefungenEdit component:

  // don't forget to import the apiFetch()
  import apiFetch from '@wordpress/api-fetch';
  
  class PruefungenEdit extends Component {
      constructor( props ) {
          super( props );
          this.state = {
              error: null,
              isLoaded: false,
              items: [],
          };
      }
  
      componentDidMount() {
          apiFetch( { path: 'my-plugin/v1/items' } )
              .then(
                  ( items ) => {
                      this.setState( {
                          isLoaded: true,
                          items,
                      } );
                  },
                  // Note: It's important to handle errors here instead of a catch() block
                  // so that we don't swallow exceptions from actual bugs in components.
                  ( error ) => {
                      this.setState( {
                          isLoaded: true,
                          error,
                      } );
                  }
          );
      }
  
      render() {
          const { error, isLoaded, items } = this.state;
  
          // If there's an error in fetching the remote data, display the error.
          if ( error ) {
              return <div>Error: { error.message }</div>;
          // If the data is still being loaded, show a loading message/icon/etc.
          } else if ( ! isLoaded ) {
              return <div>Loading...</div>;
          // Data loaded successfully; so let's show it.
          } else {
              return (
                  <ul>
                  { items.map( item => (
                      <li key={ item.id }>
                          { item.name } <i>{ item.price }</i>
                      </li>
                  ) ) }
                  </ul>
              );
          }
      }
  }
  

So I hope that helps and just let me know if you need any clarification. However, do take your time to review the first, third and fourth links above. :)