Connecting to a remote database through a computed field

Rating

Preface

Sometimes it is appropriate to make a combination between nodes of a particular content type and a remote database.

I am thinking, for example of a combination of nodes and the database used by Webform. Also with very large databases where it is not designated to import them into the Drupal structure, because of their size. Stock lists of companies are sometimes generated by other programs ca be used in that way too. Even exchanging data between different sites (not necessarily a Drupal site) ... if of course, you can make the connection (passwords, etc ...).

I take a concrete example to deal with this matter.

There is a database with all details of the chess players in Belgium with 23,000 records and their data. It is about 2.5 GB. If you are a chess club manager and you want to show the line-up for the next round with other clubs, it would be interesting that the details of your own players and opponents come from this database.

I work out an example that when entering a player in our content type, through a computed field, the strength of this player show up automatically. This strength is kept in the official database of the Belgian Chess Federation .---- I have this database locally installed on my server because there is no permission to work directly on their server .---

When creating a node automatically the ranking will show (= kind of strength of the player) retrieved from the database with 23,000 records. At the node, you can of course use all possible fields (picture, membership payment, ..). I keep it simple here... Only a few fields.

Screen001865_0.png

Creating a database and add the content.

Let us first create the database.

Screen001853.png

You must also create a user with a password.
Screen001854.png

After that, link the database with the user.
Screen001855.png

You give yourself all rights. (You can create users who can only view the database and not change it ...)


Screen001856.png

In phpMyAdmin you see now the database 'freewe1q_schaken'. (schaken = chess)


Screen001857.png

I have created a database CSV file. Open it with Excel (or other) and save as CSV (UTF-8). And import into your database. Look at the settings  ... They are important.
Screen001858.png
Screen001859.png

You get an important status message. Here you see the options to change the table name. Otherwise the thing is called TABLE 1. Nobody is happy with that.

Screen001861.png


Screen001862.png

The table has been created in the database

Screen001863.png

And has numerous fields. For privacy reasons, I do not show all data ... I also filtered at the Lokerse chess club (club number = 436)

Screen001860.png

The content type and fields

I limit myself to a few fields. The computed field is important. It will pick up the ranking from the other database. Because this is an integer, i take the Computed type (integer). I can then sort on it in a view.

Screen001866.png

Now you are going to set the default value of the computed field. You understand that this is the most important step of this tutorial.

Screen001867.png

Let us look at the code more closely.

$conn = mysqli_connect("localhost","freewe1q_dirk","je_paswoord","freewe1q_schaken");

This is the connection string. It consists of four parts. The host. This can be a database on another server. They must give permission to do so and make their database open to external access. We work on our own  database so that localhost can be used. Then follows the user and password of the user. Finally, follows the database.

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

We build a security when no connection is made. In a computed field that is not very useful. You'll only see a flash with the text. We work with an integer field that does not handle text ..

$sql = "SELECT * FROM schaakgegevens";
$result = mysqli_query($conn, $sql);

These rules ensure that we take all fields (*) of the chess table data. All rows are stored in a variable $ result. $ Result is a special variable that was it.

if (mysqli_num_rows($result) > 0) {
    
    while($row = mysqli_fetch_assoc($result)) {
  
   if($row["TRICULE"]==$entity->field_stamnummer->value){
         $value=$row["ELO_CALCUL"];
                                                               }
                                                                      }
} else {
    $value= 0;
}

Here we go through all the rows of the variable $result. If there are 23,000 members, there are 23000 rows. For each row, we see if the member number corresponds to the to that we have put into our node. I could use the name, but probably there are several players with the same name.

mysqli_close($conn);

Finally, closing our connection.

The full code:

$value=0;

$conn = mysqli_connect("localhost","freewe1q_dirk","je_paswoord","freewe1q_schaken");

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql = "SELECT * FROM schaakgegevens";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    
    while($row = mysqli_fetch_assoc($result)) {
  
   if($row["TRICULE"]==$entity->field_stamnummer->value){
         $value=$row["ELO_CALCUL"];
                                                               }
                                                                      }
} else {
    $value= 0;
}

mysqli_close($conn);

 

As a test, I add our star player to our Drupal site. Yep .. its ranking comes automatically.


Screen001864.png

Computed fields are computed when saving the node. You can change this.

Screen001868.png

 

This method offers so many possibilities because you can combine both worlds. Those of Drupal with its structure (and views) in combination with something external list data.

In the following discussion, we go one step further and we will catch views data from an external database, without being stored in nodes.