Making a view with Drupal- and non-Drupal tables

Rating

Creating a view in php in Drupal is not necessary unless you want to do things that are not default.

So you can not make a view, right now, with tables that are not generated by Drupal. Sometimes this is interesting. So you can use stock lists or other large tables that you do not want to import into a Drupal content type because they are too big.

I work with an example from which I use the members of our chess club (= Drupal content type) in combination with the large database of the Belgian Chess Federation (23 000 players). I'll get there the strength of the players. (ranking)

Screen001892.png

I integrate or import directly the table into my Drupal database.

Screen001888.png

One can use an external table, but this needs more programming. You can see the table (schaakgegevens (=chess info)) between the Drupal tables.


Screen001889.png

A glimpse of the table.
Screen001891.png

Now we will create a node from a content type that has a body field. We set the format in PHP (PHP filter module)

We go through the php code.

First we go to the first two lines using Drupal Entities to be able to apply the imagestyles on the images. We also connect with the database (host, user, password, database) and build a security if the connection fails.

use Drupal\file\Entity\File;
use Drupal\image\Entity\ImageStyle;

$conn = mysqli_connect("localhost","freewe1q_proj","your password","freewe1q_proj");


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

Then we build the SQL that will be using the view. For each field in Drupal that you use in the view, contrary to the title field, you should create a rule with the nid as join (= connection). Note that I already make the connection with the non-Drupal table. (= schaakgegevens). I also set a condition. (Type = content type) must be 'schakers (=chess)'.

$sql="SELECT * FROM dr8a_node_field_data
LEFT OUTER JOIN dr8a_node__body ON dr8a_node__body.entity_id = dr8a_node_field_data.nid
LEFT OUTER JOIN dr8a_node__field_stamnummer ON dr8a_node__field_stamnummer.entity_id = dr8a_node_field_data.nid
LEFT OUTER JOIN dr8a_node__field_image ON dr8a_node__field_image.entity_id = dr8a_node_field_data.nid
LEFT OUTER JOIN schaakgegevens ON dr8a_node__field_stamnummer.field_stamnummer_value=schaakgegevens.TRICULE
WHERE type='schakers'";

$result = mysqli_query($conn, $sql);

Now you go through all the rows that generates the SQL. With the echo command in php I let this also appear on the page. $ Row [field] allows you to display all the field values. Also that of the external table. If for one title, sthere are several values in a field (the image field is multiple) then you get multiple rows. I do not want this. Therefore, I check if the title is not the same as the last one.

  while($row = mysqli_fetch_assoc($result)) {

$nieuwetitel=$row[title];
if ($nieuwetitel!=$vorigetitel){
     echo "<div style='width:50%;float:left;'>";
        echo "<div><H2>".$row[title]."</H2>" . $row[body_value]." ". $row[field_stamnummer_value]."<br>"; 
       echo "Ranking vanuit de niet-Drupal tabel: ".$row[ELO_CALCUL]."<br>";
        
$vorigetitel=$nieuwetitel;

}

Finally, I discuss the image field. This is a reference field. That's why I have some programming to do. Look how easily I set the image style.

  $fid= $row[field_image_target_id];

      if (!empty($fid)) {
     
       $file_object = File::load($fid);
       $file_uri = $file_object->uri->value;
       $file_url = file_create_url($file_uri);    
       $file_naam= $file_object->filename->value;        
       $style = ImageStyle::load('vierkant_250');   
       $image_url = $style->buildUrl($file_object->uri->value);      
       echo "</br><img src='$image_url'>";

                      }

The complete code:

<?php

use Drupal\file\Entity\File;
use Drupal\image\Entity\ImageStyle;

$conn = mysqli_connect("localhost","freewe1q_proj","your password","freewe1q_proj");


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

$sql="SELECT * FROM dr8a_node_field_data
LEFT OUTER JOIN dr8a_node__body ON dr8a_node__body.entity_id = dr8a_node_field_data.nid
LEFT OUTER JOIN dr8a_node__field_stamnummer ON dr8a_node__field_stamnummer.entity_id = dr8a_node_field_data.nid
LEFT OUTER JOIN dr8a_node__field_image ON dr8a_node__field_image.entity_id = dr8a_node_field_data.nid
LEFT OUTER JOIN schaakgegevens ON dr8a_node__field_stamnummer.field_stamnummer_value=schaakgegevens.TRICULE
WHERE type='schakers'";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row

Last $ title = "dummy";

    while($row = mysqli_fetch_assoc($result)) {

$nieuwetitel=$row[title];
if ($nieuwetitel!=$vorigetitel){
     echo "<div style='width:50%;float:left;'>";
        echo "<div><H2>".$row[title]."</H2>" . $row[body_value]." ". $row[field_stamnummer_value]."<br>"; 
       echo "Ranking vanuit de niet-Drupal tabel: ".$row[ELO_CALCUL]."<br>";
        
$vorigetitel=$nieuwetitel;

}


        $fid= $row[field_image_target_id];

      if (!empty($fid)) {
     
       $file_object = File::load($fid);
       $file_uri = $file_object->uri->value;
       $file_url = file_create_url($file_uri);   
       $file_naam= $file_object->filename->value;       
       $style = ImageStyle::load('vierkant_250');   
       $image_url = $style->buildUrl($file_object->uri->value);         
       echo "</br><img src='$image_url'>";

                      }
echo "</div>";

    }

} else {
    echo "0 results";
}

mysqli_close($conn);

?>