mysql - Why the PHP code assigning to an array hangs? -
i'm new php. have prepared (based on various internet sources) page taking data mysql database , displaying them using flot graph. single database table loaded works great. i'm turning code 'multitable' load want have multiple graphs per single web page.
the mentioned code here:
<?php // retrieves data single page $servername = "dbserver"; $username = "dbuser"; $password = "dbpassword"; $dbname = "mydatabase"; $port = 5511; // create connection $conn = new mysqli($servername, $username, $password, $dbname, $port); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } // time in microseconds $timenow = time()*1e6; // start time 8 hours ago $timestart = intval($timenow - (8*60*60*1e6)); $tables = array("cfoua47bccmb1a_stat", "cfoua47bccmb2a_stat", "cfoua47bccmb1b_stat", "cfoua47bccmb2b_stat"); $sql = ""; foreach ($tables $table) { // add each select command single query $sql .= "select time, energy,abs1tmin,abs1tmax,abs1tavg,abs4tmin,abs4tmax,abs4tavg,abs16tmin,abs16tmax,abs16tavg,abs64tmin,abs64tmax,abs64tavg,abs256tmin,abs256tmax,abs256tavg,abs1024tmin,abs1024tmax,abs1024tavg,diff1tmin,diff1tmax,diff1tavg,diff4tmin,diff4tmax,diff4tavg,diff16tmin,diff16tmax,diff16tavg,diff64tmin,diff64tmax,diff64tavg,diff256tmin,diff256tmax,diff256tavg,diff1024tmin,diff1024tmax,diff1024tavg,thr1,thr4,thr16,thr64,thr256,thr1024 " . $table . " time >=" . $timestart . ";"; } $alldevices = array(); $index = 0; if ($conn->multi_query($sql)) { { /* store first result set */ if ($result = $conn->store_result()) { // generate each device separate dictionary echo $tables[$index]; $alldevices[$tables[$index]] = array(); while ($row = $result->fetch_row()) { printf("%s\n", $row[0]); // assign value dictionary $alldevices[$tables[$index]][] = $row; //var_dump ($alldevices); } $result->free(); } /* print divider */ if ($conn->more_results()) { $index = $index + 1; printf("-----------------\n"); } } while ($conn->next_result()); } var_dump ($alldevices); // code nicely works single graph , replaced // code above fetch , prepare json data // tables // $result = $conn->query($sql); // // json convert // $rows = array(); // if ($result->num_rows > 0) { // // output data of each row // while($row = $result->fetch_assoc()) { // // 1 one copy rows // $rows[] = $row; // } // } $conn->close(); ?> <!doctype html public "-//w3c//dtd html 4.01//en" "http://www.w3.org/tr/html4/strict.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"> <title>beam current change monitors</title> <link href="./examples.css" rel="stylesheet" type="text/css"> <!--[if lte ie 8]><script language="javascript" type="text/javascript" src="../../excanvas.min.js"></script><![endif]--> <script language="javascript" type="text/javascript" src="./jquery.js"></script> <script language="javascript" type="text/javascript" src="./flot/jquery.flot.js"></script> <script language="javascript" type="text/javascript" src="./flot/jquery.flot.time.js"></script> <script language="javascript" type="text/javascript" src="./flot/jquery.flot.navigate.js"></script> <script language="javascript" type="text/javascript" src="./flot-axislabels/jquery.flot.axislabels.js"></script> <script type="text/javascript"> $(function() { var graphdata = <?php echo json_encode( $rows ) ?>; var a1t = []; var a4t = []; var a16t = []; var a64t = []; var a256t = []; var a1024t = []; var d1t = [], d4t = [], d16t = [], d64t = [], d256t = [], d1024t = [], energy = []; // localtime conversion var nd = new date(); var nt = nd.gettimezoneoffset(); (var m in graphdata){ energy.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['energy']] ); a1t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['abs1tmax']] ); a4t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['abs4tmax'] / 4] ); a16t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['abs16tmax']/16 ] ); a64t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['abs64tmax']/64 ] ); a256t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['abs256tmax']/256 ] ); a1024t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['abs1024tmax']/1024 ] ); d1t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['diff1tmax']] ); d4t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['diff4tmax']/4 ] ); d16t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['diff16tmax']/16 ] ); d64t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['diff64tmax']/64 ] ); d256t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['diff256tmax']/256 ] ); d1024t.push ( [graphdata[m]['time'] / 1e3 - nt*60*1000,graphdata[m]['diff1024tmax']/1024 ] ); } // 2 plots produce $.plot("#abs", [ {data:energy, label:"energy", yaxis:2}, {data:a1t, label: "1t"}, {data:a4t, label: "4t"}, {data:a16t, label: "16t"}, {data:a64t, label: "64t"}, {data:a256t, label: "256t"}, {data:a1024t, label: "1024t"} ], { xaxes: [ { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true} ], yaxes: [ {min:0, axislabel: "adc bins [-]", axislabelusecanvas: true}, {min:0, max:7000, position: "right"} ], legend: {position: "nw"} }); $.plot("#diff", [ {data:d1t, label: "1t"}, {data:d4t, label: "4t"}, {data:d16t, label: "16t"}, {data:d64t, label: "64t"}, {data:d256t, label: "256t"}, {data:d1024t, label: "1024t"} ], { xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis: {axislabel: 'adc bins [-]', axislabelusecanvas: true, min:0, max: 1e5}, legend: {position: "nw"} }); }); </script> </head> <body> <div id="header"> <h3>cfo-ua47-bccm-b1a</h3> </div> <div id="content"> <div class="demo-container"> <div id="abs" class="demo-placeholder"></div> </div> <div class="demo-container"> <div id="diff" class="demo-placeholder"></div> </div> </body> </html> now mystery: in php section i'm trying assemble dictionary, each of processed $tables used key, , such dictionary contain array of results returned sql query.
problem is, when issue line:
$alldevices[$tables[$index]][] = $row; the php engine unknown reasons hangs. i.e. not load values table , stops working. when line removed code, code processed ok (hence 4 sql queries performed , entire page executed displaying 2 empty graphs). if leave code there , print example $row[0] in each iteration of fetch_row(), can see loads first table, partially second table, , hangs.
what doing wrong?
just sake of completeness give solution problem. main reason memory available. according developerjwk's advice have reimplemented engine 2 separate steps:
1) php script sitting in separate file, fetches needed data mysql database:
<?php function loaddata($tablename) { // retrieves data single page $servername = "myserver"; $username = "myusername"; $password = "somefancypassword"; $dbname = "databasename"; $port = 5511; // create connection $conn = new mysqli($servername, $username, $password, $dbname, $port); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } // time in microseconds $timenow = time()*1e6; // start time 8 hours ago $timestart = intval($timenow - (8*60*60*1e6)); // define array of variables select $variables = array("time", "energy","abs1tmin","abs1tmax","abs1tavg","abs4tmin","abs4tmax","abs4tavg","abs16tmin","abs16tmax","abs16tavg","abs64tmin","abs64tmax","abs64tavg","abs256tmin","abs256tmax","abs256tavg","abs1024tmin","abs1024tmax","abs1024tavg","diff1tmin","diff1tmax","diff1tavg","diff4tmin","diff4tmax","diff4tavg","diff16tmin","diff16tmax","diff16tavg","diff64tmin","diff64tmax","diff64tavg","diff256tmin","diff256tmax","diff256tavg","diff1024tmin","diff1024tmax","diff1024tavg","thr1","thr4","thr16","thr64","thr256","thr1024"); // loopvars - iterate when generating arrays $loopvars = array_slice($variables,1); // sql fetch data single system $sql = "select " . join(',', $variables) . " " . $tablename . " time >=" . $timestart . ";"; // generate dictionary of arrays foreach ($loopvars $var) { echo "myarray['".$var."']=[];"; } $result = $conn->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { // 1 one copy rows foreach ($loopvars $var) { echo "myarray['".$var."'].push( [". $row['time']/1e3 . "," . $row[$var] . "] );" ; } } } $conn->close(); } ?> as can see, php script not generate variables, generates javascript code, fills 'myarray' variable inside javascript.
then have setup new index.php page, consecutive calls php script generates data in myarray , plots them using flot:
<!doctype html public "-//w3c//dtd html 4.01//en" "http://www.w3.org/tr/html4/strict.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"> <title>beam current change monitors</title> <link href="./examples.css" rel="stylesheet" type="text/css"> <!--[if lte ie 8]><script language="javascript" type="text/javascript" src="../../excanvas.min.js"></script><![endif]--> <script language="javascript" type="text/javascript" src="./jquery.js"></script> <script language="javascript" type="text/javascript" src="./flot/jquery.flot.js"></script> <script language="javascript" type="text/javascript" src="./flot/jquery.flot.time.js"></script> <script language="javascript" type="text/javascript" src="./flot/jquery.flot.navigate.js"></script> <script language="javascript" type="text/javascript" src="./flot-axislabels/jquery.flot.axislabels.js"></script> <script type="text/javascript"> $(document).ready(function() { // tables array display accompanied ymax restriction var tables = new array (["cfoua47bccmb1a_stat", 1e8], ["cfoua47bccmb2a_stat", 1e8], ["cfoua47bccmb1b_stat", 1e8], ["cfoua47bccmb2b_stat", 1e8]); var myarray = new array(); // note: unfortunately need dumbly copy block 4 times. because not able change // argument in php function loaddata dynamically. function evaluated @ server side. // way use dynamically call php script through ajax, there issue passing // arguments javascript. instead, call loaddata generates javascript code, fills myarray variable // data loaded database. //console.log("my object: %o", myarray); <?php include 'fetchdata.php';loaddata("cfoua47bccmb2a_stat");?> //console.log("my object: %o", myarray); $.plot("#abscfoua47bccmb2a_stat", [{data:myarray['abs1tmax'], label: "1t"}, {data:myarray['abs4tmax'], label: "4t"},{data:myarray['abs16tmax'], label: "16t"},{data:myarray['abs64tmax'], label: "64t"},{data:myarray['abs256tmax'], label: "256t"},{data:myarray['abs1024tmax'], label: "1024t"}], {xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis:{axislabel: 'adc bins [-]', axislabelusecanvas: true}} ); $.plot("#diffcfoua47bccmb2a_stat", [{data:myarray['diff1tmax'], label: "1t"}, {data:myarray['diff4tmax'], label: "4t"},{data:myarray['diff16tmax'], label: "16t"},{data:myarray['diff64tmax'], label: "64t"},{data:myarray['diff256tmax'], label: "256t"},{data:myarray['diff1024tmax'], label: "1024t"}], {xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis:{axislabel: 'adc bins [-]', axislabelusecanvas: true, min:0, max: 1e8}} ); // make empty again , run b2a myarray = []; <?php loaddata("cfoua47bccmb1a_stat");?> $.plot("#abscfoua47bccmb1a_stat", [{data:myarray['abs1tmax'], label: "1t"}, {data:myarray['abs4tmax'], label: "4t"},{data:myarray['abs16tmax'], label: "16t"},{data:myarray['abs64tmax'], label: "64t"},{data:myarray['abs256tmax'], label: "256t"},{data:myarray['abs1024tmax'], label: "1024t"}], {xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis:{axislabel: 'adc bins [-]', axislabelusecanvas: true}} ); $.plot("#diffcfoua47bccmb1a_stat", [{data:myarray['diff1tmax'], label: "1t"}, {data:myarray['diff4tmax'], label: "4t"},{data:myarray['diff16tmax'], label: "16t"},{data:myarray['diff64tmax'], label: "64t"},{data:myarray['diff256tmax'], label: "256t"},{data:myarray['diff1024tmax'], label: "1024t"}], {xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis:{axislabel: 'adc bins [-]', axislabelusecanvas: true, min:0, max: 1e8}} ); // make empty again , run b1b myarray = []; <?php loaddata("cfoua47bccmb1b_stat");?> //console.log("my object: %o", myarray); $.plot("#abscfoua47bccmb1b_stat", [{data:myarray['abs1tmax'], label: "1t"}, {data:myarray['abs4tmax'], label: "4t"},{data:myarray['abs16tmax'], label: "16t"},{data:myarray['abs64tmax'], label: "64t"},{data:myarray['abs256tmax'], label: "256t"},{data:myarray['abs1024tmax'], label: "1024t"}], {xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis:{axislabel: 'adc bins [-]', axislabelusecanvas: true}} ); $.plot("#diffcfoua47bccmb1b_stat", [{data:myarray['diff1tmax'], label: "1t"}, {data:myarray['diff4tmax'], label: "4t"},{data:myarray['diff16tmax'], label: "16t"},{data:myarray['diff64tmax'], label: "64t"},{data:myarray['diff256tmax'], label: "256t"},{data:myarray['diff1024tmax'], label: "1024t"}], {xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis:{axislabel: 'adc bins [-]', axislabelusecanvas: true, min:0, max: 1e8}} ); // run b2b myarray = []; <?php loaddata("cfoua47bccmb2b_stat");?> //console.log("my object: %o", myarray); $.plot("#abscfoua47bccmb2b_stat", [{data:myarray['abs1tmax'], label: "1t"}, {data:myarray['abs4tmax'], label: "4t"},{data:myarray['abs16tmax'], label: "16t"},{data:myarray['abs64tmax'], label: "64t"},{data:myarray['abs256tmax'], label: "256t"},{data:myarray['abs1024tmax'], label: "1024t"}], {xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis:{axislabel: 'adc bins [-]', axislabelusecanvas: true}} ); $.plot("#diffcfoua47bccmb2b_stat", [{data:myarray['diff1tmax'], label: "1t"}, {data:myarray['diff4tmax'], label: "4t"},{data:myarray['diff16tmax'], label: "16t"},{data:myarray['diff64tmax'], label: "64t"},{data:myarray['diff256tmax'], label: "256t"},{data:myarray['diff1024tmax'], label: "1024t"}], {xaxis: { mode: "time", axislabel: 'time [h]',axislabelusecanvas: true}, yaxis:{axislabel: 'adc bins [-]', axislabelusecanvas: true, min:0, max: 1e8}} ); }); </script> </head> <body> <div id="header"> <h1 align="center">bccm status panel</h1> </div> <h2 align="center">operational systems</h2> <div class="demo-container" style="width:100%; display:table;"> <div style="display: table-row"> <div style="display:table-cell;"><h3 align="center">cfo-ua47-bccm-b1a</h3></div> <div style="display:table-cell;"><h3 align="center">cfo-ua47-bccm-b2a</h3></div> </div> <div style="display: table-row"> <div id="abscfoua47bccmb1a_stat" class="demo-placeholder" style="width:45%; display:table-cell;"></div> <div id="abscfoua47bccmb2a_stat" class="demo-placeholder" style="width:45%; display:table-cell;"></div> </div> </div> <div class="demo-container" style="width:100%; display:table;"> <div style="display: table-row"> <div id="diffcfoua47bccmb1a_stat" class="demo-placeholder" style="width:45%; display:table-cell;"></div> <div id="diffcfoua47bccmb2a_stat" class="demo-placeholder" style="width:45%; display:table-cell;"></div> </div> </div> <h2 align="center">development systems</h2> <div class="demo-container" style="width:100%; display:table;"> <div style="display: table-row"> <div style="display:table-cell;"><h3 align="center">cfo-ua47-bccm-b1b</h3></div> <div style="display:table-cell;"><h3 align="center">cfo-ua47-bccm-b2b</h3></div> </div> <div style="display: table-row"> <div id="abscfoua47bccmb1b_stat" class="demo-placeholder" style="width:45%; display:table-cell;"></div> <div id="abscfoua47bccmb2b_stat" class="demo-placeholder" style="width:45%; display:table-cell;"></div> </div> </div> <div class="demo-container" style="width:100%; display:table;"> <div style="display: table-row"> <div id="diffcfoua47bccmb1b_stat" class="demo-placeholder" style="width:45%; display:table-cell;"></div> <div id="diffcfoua47bccmb2b_stat" class="demo-placeholder" style="width:45%; display:table-cell;"></div> </div> </div> <div id="footer"> issues? mail to: <a href="mailto:david.belohrad@cern.ch?subject=issue%20with%20bccm%20web%20page" target="_top">david.belohrad@cern.ch</a> </div> </body> </html> as can see, bit flimsy i'm copying 4 times same loading , plotting block. unfortunately did not find other way how call php script variable passed function. php script server executed , hence way how maybe ajax.
anyways, works. results can seen (so far) on http://www.cern.ch/bccm
Comments
Post a Comment