Updating graph based by clicking on cell

Question

Samyx 36 Junior Poster in Training

13 Years Ago

Hello Everyone,

How can I save the field id/name on a variable, once an user clicks on it, so that later I can use that variable as a parameter to retrieve data from the database? In the code below, you can see that I am selecting information from 'ac' in the database. But what I want is, when a user click on a column name, the column id is selected and saved on a variable. The column names have the exact same names as the tables in the database.Then the variable containing the id will be used on the sql statemente to retrieve the corresponding data.

Example, user clicks on cell 'Checked', the id for that field is saved on a variable, and the variables is used as parameter on the sql statement.

See code:

<!DOCTYPE html>
<html >
<head>
<link rel="stylesheet" type="text/css" href="http://localhost/recipe/dijit/themes/claro/claro.css" />
<link rel="stylesheet" type="text/css" href="http://localhost/recipe/dojox/grid/resources/Grid.css" />
<link rel="stylesheet" type="text/css" href="http://localhost/recipe/dojox/grid/resources/claroGrid.css" />
<link rel="stylesheet" type="text/css" href="http://localhost/recipe/dojo/resources/dojo.css" />
<link rel="stylesheet" type="text/css" href="http://localhost/recipe/dojox/grid/enhanced/resources/EnhancedGrid_rtl.css" />
<link rel="stylesheet" type="text/css" href="http://localhost/recipe/dojox/grid/enhanced/resources/EnhancedGrid.css" />
<style type="text/css">

#gridContainer {
    width: auto;
    height: 250px;
    border: 4px double #333;
}


</style>
    <?php
    //connect to database
    $con = mysql_connect("localhost", "test", "test") or die('Sorry, could not connect to database server');
    mysql_select_db("brazil", $con) or die('Sorry, could not connect to database');

    //total number of participants
    $query1 = "SELECT participantsExpected from ac where identity =4";
    //number of participants in neoadjuvant chemoterapy
    $query2 = "Select participantsEnrolled from ac where identity=4";
    //number of participants in surgery
    $query3 = "SELECT participantsExpected from ac where identity =2";
    //number of participants off study
    $query4 = "Select participantsEnrolled from ac where identity=1";
    //number of participants expected
    $query5 = "Select participantsExpected from ac where identity=1";
    //number of participants unassigned
    $query6="Select participantsEnrolled from ac where identity=2";


    $result1 = mysql_query($query1) or die ('Could not find total');
    $res1= mysql_fetch_array($result1);
    $totalp = $res1[0];
    //$totalp=json_encode($totalp);
    //echo ($totalp);

    $result2 = mysql_query($query2) or die ('Could not find neo chemoterapy');
    $res2 = mysql_fetch_array($result2);
    $chemop = $res2[0];
    //$chemop= json_encode($chemop);

    $result3 = mysql_query($query3) or die ('Could not find surgery');
    $res3 = mysql_fetch_array($result3);
    $surgeryp = $res3[0];
    //$surgeryp = json_encode($surgeryp);

    $result4 = mysql_query($query4) or die ('Could not find off study');
    $res4 = mysql_fetch_array($result4);
    $offp = $res4[0];
    //$offp = json_encode($offp);
    //print_r($offp);

    $result5 = mysql_query($query5) or die ('Could not find expected');
    $res5 = mysql_fetch_array($result5);
    $expectp = $res5[0];
    //$expectp = json_encode($expectp);
    //print_r($expectp);

    $result6 = mysql_query($query6) or die ('Could not find unassigned');
    $res6 = mysql_fetch_array($result6);
    $unassignp = $res6[0];
    //$unassignp= json_encode($unassignp);
    //print_r($unassignp);
?>

    <link rel="stylesheet" type="text/css" href="./377815.css" />

<script>dojoConfig = {parseOnLoad: true}</script>
<script src="//ajax.googleapis.com/ajax/libs/dojo/1.8.1/dojo/dojo.js"
               data-dojo-config="async: true"></script>
<script>
require([
     // Require the basic chart class
    "dojox/charting/Chart", 
    // Require the theme of our choosing
    "dojox/charting/themes/MiamiNice", 
    // Charting plugins: 
    //  We want to plot Columns
    "dojox/charting/plot2d/Columns",    
     //  We want to plot StackedColumns
    "dojox/charting/plot2d/StackedColumns", 
    //  We want to use Markers
    "dojox/charting/plot2d/Markers", 
    //  We'll use default x/y axes
    "dojox/charting/axis2d/Default",    
    //we'll use Legend
    "dojox/charting/widget/SelectableLegend", 
    //we'll use Legend
    "dojox/grid/EnhancedGrid", 
    //we'll use Legend
    "dojo/data/ItemFileWriteStore", 
    //we'll use Legend
    "dojox/grid/enhanced/plugins/exporter/CSVWriter", 
    // Wait until the DOM is ready
    "dojo/domReady!"
],
 function(Chart, theme) {

    // Define the data

    var total= <?php print_r ($totalp); ?>;
    //alert(total);
    var chemo = <?php echo ($chemop); ?>;
    //alert(chemo);
    var surgery = <?php echo ($surgeryp); ?>;
    //alert(surgery);
    var offstudy = <?php echo ($offp); ?>;
    //alert(offstudy);
    var expected = <?php echo ($expectp); ?>;
    //alert(expected);
    var unassigned = <?php echo ($unassignp); ?>;
    //alert(unassigned);

    // Create the chart within it's "holding" node
    var chart = new Chart("chartNode");
    var chart2 = new Chart("chartNode2");

    // Set the theme
    chart.setTheme(theme);
    chart2.setTheme(theme);

    // Add the only/default plot
    chart.addPlot("default", {
       type: "Columns",
       markers: true,
       shadows: {dx:2, dy:2, dw:2},
       gap: 2
    });
    chart2.addPlot("default", {
       type: "Columns",
       markers: true,
       shadows: {dx:2, dy:2, dw:2},
       gap: 2
    });


    // Add axes
    chart.addAxis("x", {title:"Arms", titleOrientation: "away", minorLabels: false, labels:[{value:0, text:""}, {value:1, text:"NeoAdj Chemo"}, {value:2, text:"Surgery"},{value:3, text: "Unassigned"}, {value:4, text:"Off Study"}, {value:5, text:"Total (Actual)"}, {value:6, text:"Total (Expected)"}], rotation:-45});
    chart.addAxis("y", {title: "Participants", vertical: true, includeZero: true, ticks:true, majorLabels:true, minorLabels: false });

    chart2.addAxis("x", {title:"Arms", titleOrientation: "away", minorLabels: false, labels:[{value:0, text:""}, {value:1, text:"NeoAdj Chemo"}, {value:2, text:"Surgery"},{value:3, text: "Unassigned"}, {value:4, text:"Off Study"}, {value:5, text:"Total (Actual)"}, {value:6, text:"Total (Expected)"}], rotation:-90});
    chart2.addAxis("y", {title: "CRFs", vertical: true, includeZero: true, ticks:true, majorLabels:true, minorLabels: false });

    // Add the series of data for graph1-Participants
    chart.addSeries("NeoAdj Chemo", [chemo,0,0,0,0,0], {stroke:"#FF0099", fill:"#FEA9F3"});
    chart.addSeries("Surgery", [0,surgery,0,0,0,0], {stroke:"#FFFF00", fill:"#FFFF33"});
    chart.addSeries("Unassigned", [0,0,unassigned,0,0,0], {stroke:"#FF0000", fill:"#CC6600"});
    chart.addSeries("Off-Study", [0,0,0,offstudy,0,0], {stroke: "black", fill:"gray"});
    chart.addSeries("Total (Actual)",[0,0,0,0,total,0], {stroke:"black", fill:"black"});
    chart.addSeries("Total (Expected)", [0,0,0,0,0,expected], {stroke: "black", fill:"blue"});

    //Add the series of data for graph2-CRFs
    chart2.addSeries("NeoAdj Chemo", [chemo,0,0,0,0,0], {stroke:"#FF0099", fill:"#FEA9F3"});
    chart2.addSeries("Surgery ", [0,surgery,0,0,0,0], {stroke:"#FFFF00", fill:"#FFFF33"});
    chart2.addSeries("Unassigned ", [0,0,unassigned,0,0,0], {stroke:"#FF0000", fill:"#CC6600"});
    chart2.addSeries("Off-Study ", [0,0,0,offstudy,0,0], {stroke: "black", fill:"gray"});
    chart2.addSeries("Total(Actual)",[0,0,0,0,total,0], {stroke:"black", fill:"black"});
    chart2.addSeries("Total(Expected)", [0,0,0,0,0,expected], {stroke: "black", fill:"blue"});

    // Render the chart!
    chart.render();
    chart2.render();

    var legend1 = new dojox.charting.widget.SelectableLegend({chart: chart, horizontal:true}, "legend1");
    var legend2 = new dojox.charting.widget.SelectableLegend({chart: chart2, horizontal:true}, "legend2");


});
var data = {
    identifier: 'id',
    label: 'id',
    items: []
};
var data_list = [
    {"Heard": true, "Checked": "True", "Genre":"Easy Listening", "Artist":"Bette Midler", "Year":2003, "Album":"Bette Midler Sings the Rosemary Clooney Songbook", "Name":"Hey There", "Length":"03:31", "Track":4, "Composer":"Ross, Jerry 1926-1956 -w Adler, Richard 1921-", "Download Date":"1923/4/9", "Last Played":"04:32:49"},
    {"Heard": true, "Checked": "True", "Genre":"Classic Rock", "Artist":"Jimi Hendrix", "Year":1993, "Album":"Are You Experienced", "Name":"Love Or Confusion", "Length":"03:15", "Track":4, "Composer":"Jimi Hendrix", "Download Date":"1947/12/6", "Last Played":"03:47:49"},
    {"Heard": true, "Checked": "True", "Genre":"Jazz", "Artist":"Andy Narell", "Year":1992, "Album":"Down the Road", "Name":"Sugar Street", "Length":"07:00", "Track":8, "Composer":"Andy Narell", "Download Date":"1906/3/22", "Last Played":"21:56:15"},
    {"Heard": true, "Checked": "True", "Genre":"Progressive Rock", "Artist":"Emerson, Lake & Palmer", "Year":1992, "Album":"The Atlantic Years", "Name":"Tarkus", "Length":"20:40", "Track":5, "Composer":"Greg Lake/Keith Emerson", "Download Date":"1994/11/29", "Last Played":"03:25:19"},
    {"Heard": true, "Checked": "True", "Genre":"Rock", "Artist":"Blood, Sweat & Tears", "Year":1968, "Album":"Child Is Father To The Man", "Name":"Somethin' Goin' On", "Length":"08:00", "Track":9, "Composer":"", "Download Date":"1973/9/11", "Last Played":"19:49:41"},
    {"Heard": true, "Checked": "True", "Genre":"Jazz", "Artist":"Andy Narell", "Year":1989, "Album":"Little Secrets", "Name":"Armchair Psychology", "Length":"08:20", "Track":5, "Composer":"Andy Narell", "Download Date":"2010/4/15", "Last Played":"01:13:08"},
    {"Heard": true, "Checked": "True", "Genre":"Easy Listening", "Artist":"Frank Sinatra", "Year":1991, "Album":"Sinatra Reprise: The Very Good Years", "Name":"Luck Be A Lady", "Length":"05:16", "Track":4, "Composer":"F. Loesser", "Download Date":"2035/4/12", "Last Played":"06:16:53"},
    {"Heard": true, "Checked": "True", "Genre":"Progressive Rock", "Artist":"Dixie dregs", "Year":1977, "Album":"Free Fall", "Name":"Sleep", "Length":"01:58", "Track":6, "Composer":"Steve Morse", "Download Date":"2032/11/21", "Last Played":"08:23:26"},
    {"Heard": true, "Checked": "True", "Genre":"Classic Rock", "Artist":"Black Sabbath", "Year":2004, "Album":"Master of Reality", "Name":"Sweet Leaf", "Length":"05:04", "Track":1, "Composer":"Bill Ward/Geezer Butler/Ozzy Osbourne/Tony Iommi", "Download Date":"2036/5/26", "Last Played":"22:10:19"},
    {"Heard": true, "Checked": "True", "Genre":"Blues", "Artist":"Buddy Guy", "Year":1991, "Album":"Damn Right, I've Got The Blues", "Name":"Five Long Years", "Length":"08:27", "Track":3, "Composer":"Eddie Boyd/John Lee Hooker", "Download Date":"1904/4/4", "Last Played":"18:28:08"},
    {"Heard": true, "Checked": "True", "Genre":"Easy Listening", "Artist":"Frank Sinatra", "Year":1991, "Album":"Sinatra Reprise: The Very Good Years", "Name":"The Way You Look Tonight", "Length":"03:23", "Track":5, "Composer":"D. Fields/J. Kern", "Download Date":"1902/10/12", "Last Played":"23:09:23"}];

dojo.ready(function(){
    alert("test");
    var store = new dojo.data.ItemFileWriteStore({data: data});

    var layout = [
        { field: "id"},
        { field: "Heard"},
        { field: "Checked"},
        { field: "Genre"},
        { field: "Artist"},
        { field: "Album"},
        { field: "Name"},
        { field: "Track"},
        { field: "Download Date"},
        { field: "Last Played"}
    ];

    var grid = new dojox.grid.EnhancedGrid({
        id: 'grid',
        store: store,
        structure: layout
        });
    grid.placeAt('gridContainer');
    grid.startup();
}); 
</script>

<body class="Wetland">
    <center><img src="./lacrn.jpg"></center>
    <center><table cellpadding="0" cellspacing="6" ><tr><td><font size="5"><b>US-LA CRN OpenClinica Dashboard</font> </td></tr> </center>
    <tr><td><Center><font size="4"><b>Argentina</font> </td></tr><tr><td><Center>Página generada el: Wednesday, February 20, 2013 17:19</td></tr>
    <tr><td><Center>Datos actualizados al (yyyy-mm-dd): 2013-02-20</td></tr></table> 


    <table BORDER="2" BORDERCOLOR="#336699" CELLPADDING="2" CELLSPACING="2" WIDTH="100%">
        <tr><td>  
            <div id="chartNode" style="float:left"></div>
            <div id="legend1"></div>      
        </td>
        <td> 
            <div id="chartNode2" style="float:left"></div>
            <div id="legend2"></div>      
        </td>
        </tr>
    </table>

<center>
<iframe src="./textarea.php" cellpadding ="0" cellspacing="6" height="950" width="100%"></iframe>
</center>

</body>
</html>

data-structure html-css javascript

2 Contributors
3 Replies
211 Views
4 Days Discussion Span
Latest Post 13 Years Ago Latest Post by Member #949455

Dani AI

Generated 7 Months Ago

Short answer: capture the grid header click client‑side, keep the clicked column name in a JS variable, then call the server with that column name to run a SELECT. Persisting the click into the database (UPDATE/INSERT) is only needed if the click must be stored as a user preference; it is not required just to fetch data for the chart. (dojotoolkit.org)

A simple Dojo pattern (attach a header handler and POST the column name):

require(["dijit/registry","dojo/request","dojo/_base/lang","dojo/domReady!"],
function(registry, request, lang){
  var grid = registry.byId("grid");
  grid.onHeaderCellClick = function(evt){
    // evt is the decorated grid event; evt.cell holds the column info
    var field = (evt.cell && (evt.cell.field || evt.cell.name)) || null;
    window.clickedColumn = field; // keep for later use

    // AJAX -> server; return should be JSON used to update chart
    request.post("/get_column_data.php", {
      data: { column: field },
      handleAs: "json"
    }).then(function(json){
      /* update chart with json */
    });
  };
});

The grid event object is decorated with a cell property (so evt.cell.field or evt.cell.name gives the column identifier), and the grid layout is also inspectable via grid.layout.cells if mapping/indexing is needed. (dojotoolkit.org)

Server-side: never trust the raw column name. PDO/mysqli prepared statements protect values but cannot be used for identifiers (table/column names), so validate the incoming column against a whitelist and only then interpolate it into the SQL; use prepared statements for other parameters. Example (PDO):

$allowed = ['Checked','Heard','Genre']; // exact allowed column names
$col = $_POST['column'] ?? '';
if (!in_array($col, $allowed, true)) { http_response_code(400); exit; }

$pdo = new PDO(...);
$sql = "SELECT `$col` FROM ac WHERE identity = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id'=> $id]);
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

Prepared-statement caveat and migration note: parameter markers can’t bind identifiers (column/table names), so whitelist them first; also avoid the old mysql_* API (deprecated/removed) — use PDO or mysqli. (php.net)

Reference: this approach uses the grid’s header events rather than database UPDATE/INSERT to “save” the click; ’s suggestion to UPDATE only applies when the click itself must be persisted for future sessions. (dojotoolkit.org)

All 3 Replies

Member #949455

13 Years Ago

How can I save the field id/name on a variable, once an user clicks on it, so that later I can use that variable as a parameter to retrieve data from the database?

I know you got the dojo code from here:

http://dojotoolkit.org/reference-guide/1.8/dojo/index.html

You didn't write a Update & Insert statement. You only wrote Select statement.

You need to Update & Insert the data in the database then you select

There should be more than just a Select query:

"SELECT participantsExpected FROM ac where ID = '$id'";

"INSERT INTO ac VALUES (null,'$column1','$column2')";

"UPDATE ac SET column1 = '$column1', column2 = '$column2' WHERE ID = '$id'";

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Samyx 36 Junior Poster in Training · Answer 1 · 2013-04-02T14:37:35+00:00

Samyx 36 Junior Poster in Training

13 Years Ago

I am sorry to ask, why do I need to update and insert?

Member #949455 · Answer 2 · 2013-04-02T17:34:32+00:00

I am sorry to ask, why do I need to update and insert?

You want this to happen:

But what I want is, when a user click on a column name, the column id is selected and saved on a variable.

When a user click on the column name and the column id is selected and saved on the variable.

I don't see a query from your code that suggested it is saved?

In order to be save the variable you need a UPDATE query.

I also don't see how you INSERT query the data either.

Are you using a form to input the data to the database?

Are you planning to add more data? If you are planning to add more data then a INSERT query is needed.

Does that make sense?

Updating graph based by clicking on cell

Dani AI

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers