how to retrieve nodes and elements from mysql database in cytoscape.js?

Question

I have a table 'entrezgene' containing geneID, name which will used to create the nodes and another table 'interaction' containing geneID, geneID2 which will be used as the source and target respectively in cytoscape.js.

I have written a php file below (only for the entrezgene table):

<?php
include 'dbconnection.php';

$sqlentrezgene = "select geneID, name from entrezgene";
$resultentrezgene = mysqli_query($connection, $sqlentrezgene) or die("Error in Selecting " . mysqli_error($connection));

$json = array();

while($row = mysqli_fetch_array ($resultentrezgene))     
{
$entrezgene = array(
    'id' => $row['geneID'],
    'name' => $row['name']
);
array_push($json, $entrezgene);
}

$jsonstring = json_encode($json);
echo $jsonstring;
?>

Cytoscape.js File:

        $('#cy').cytoscape({
  style: cytoscape.stylesheet()
    .selector('node')
      .css({
        'content': 'data(name)',
        'text-valign': 'top',
        'color': 'white',
        'text-outline-width': 1,
        'text-outline-color': '#888'
      })
    .selector('edge')
      .css({
        'target-arrow-shape': 'triangle'
      })
    .selector(':selected')
      .css({
        'background-color': 'black',
        'line-color': 'black',
        'target-arrow-color': 'black',
        'source-arrow-color': 'black'
      })
    .selector('.faded')
      .css({
        'opacity': 0,
        'text-opacity': 0.25
      }),



  elements: {
    nodes: [

    //Manually entered
      { data: {id: '1', name: 'A1BG_HUMAN'} },
      { data: {id: '10549', name: 'PRDX4_HUMAN'} },
      { data: {id: '10935', name: 'PRDX3_HUMAN'} },
      { data: {id: '1192', name: 'CLIC1_HUMAN'} },
      { data: {id: '2923', name: 'PDIA3_HUMAN'} }

    ],
    edges: [
    //Manually entered
      { data: { source: '1', target: '10549' } },
      { data: { source: '10549', target: '10935' } },
      { data: { source: '10549', target: '1192' } },
      { data: { source: '10549', target: '2923' } }
    ]
  },

  ready: function(){
    window.cy = this;

    // giddy up...

    cy.elements().unselectify();

    cy.on('tap', 'node', function(e){
      var node = e.cyTarget; 
      var neighborhood = node.neighborhood().add(node);

      cy.elements().addClass('faded');
      neighborhood.removeClass('faded');
    });

    cy.on('tap', function(e){
      if( e.cyTarget === cy ){
        cy.elements().removeClass('faded');
      }
    });
  }
});

But the problem is that the table entrezgene has 16,388 rows and the table interaction has 225,287 rows. So entering them manually will be time consuming. Is there a solution to that?

Answer 1

I don't know your overall project structure, so I may not be able to give detailed instructions, but I can show you how I use cytoscape.js in one of my projects:

This is an except of my graphview.js

Main entry point is the init function (which you may call on document ready or a click on an element, ...). This performs an ajax call to your server, and in case the call is successful, it calls the function displaygraph with the received data.

var init = function() {
    //.. do some overall initialization
    $.ajax({
        url: '/url/to/getgraph.php',
        method: 'GET', 
        dataType: 'json',
        data: {},  //probably you have some parameters
        success: displaygraph, 
        error: function() {alert('error getting data');}
    });
}

The function displaygraph is then responsible for your graph initialization and display. It receives the data from the ajax call. This could be more or less your code above, just the nodes and edges are not preentered, but taken from the data received from the server.

var displaygraph = function(data) {
    $('#cy').cytoscape({
        style: // ... 

        elements: {
            nodes: data.nodes,
            edges: data.edges
        },

        ready: function(){
            //...
        }
    });

}

And on the serverside, you have to make sure, the data which is sent back to the client contains all necessary information, ie, the nodes and edges structured as cytoscape expects it. I assume your inital code for reading the database works (I'm not a PHP guy, so can't check it), so I just added reading the interactions from the other table and created on object which contains nodes and edges.

<?php
include 'dbconnection.php';

$sqlentrezgene = "select geneID, name from entrezgene";
$resultentrezgene = mysqli_query($connection, $sqlentrezgene) or die("Error in Selecting " . mysqli_error($connection));

$nodes= array();
while($row = mysqli_fetch_array ($resultentrezgene))     
{
    //create each entry in the array exactly as cytoscape expects it, ie with the data property
    $entrezgene = array('data' => array(
        'id' => $row['geneID'],
        'name' => $row['name']
    ));
    array_push($nodes, $entrezgene);
}

$sqlentrezinter = "select geneID1, geneID2 from interaction";
$resultentrezinter = mysqli_query($connection, $sqlentrezinter) or die("Error in Selecting " . mysqli_error($connection));

$edges= array();
while($row = mysqli_fetch_array ($resultentrezinter))     
{
    $entrezinter = array('data' => array(
        'source' => $row['geneID1'],
        'target' => $row['geneID2']
    ));
    array_push($edges, $entrezinter);
}

$json = array(
   'nodes' => $nodes,
   'edges' => $edges
);

$jsonstring = json_encode($json);
echo $jsonstring;
?>

I'm not a PHP guy, so there may be some glitches in the server part, but you should get the overall idea.