Databases
StackOverflow2013
Postsdbo
POGenerating a google graph appears to time out sometimes and not others
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGenerating a google graph appears to time out sometimes and not others
    primarykey
    data
    text
    <p>I have created a survey system using php and mysql. I have a table of answers called "answer" which stores all the responses submitted by users. I have created a script to generate a google graph to show statistics on 2 questions at a time. This script seems to work sometimes with very little response time and sometimes it takes forever and even returns an internal server error (i think because its taking to long to get the results but im not sure) </p> <p>Here is the php script</p> <pre><code>&lt;? // DB CONFIG FILE require("../db/config.inc.php"); // DB CLASS FILE require("../db/Database.class.php"); // CREATE THE $DB OBJECT $db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE); // DB CONNECTION $db-&gt;connect(); error_reporting(-1); // GET THE SERVER ID VIA POST $sID = 1342625918; // HARD NUMBER FOR TESTING // GET THE QUESTION IDS VIA POST $question1 = 5; // HARD NUMBER FOR TESTING $question2 = 17; // HARD NUMBER FOR TESTING //GET THE NAME OF THE SURVEY $sql = "SELECT * FROM survey WHERE sID = '$sID'"; $rows = $db-&gt;query($sql); while ($record = $db-&gt;fetch_array($rows)) { $sName = $record["sName"]; } //GET QUESTION ONE $sql = "SELECT * FROM questions WHERE qID = '$question1'"; $rows = $db-&gt;query($sql); while ($record = $db-&gt;fetch_array($rows)) { $q1Text = $record["qName"]; } //GET QUESTION TWO $sql = "SELECT * FROM questions WHERE qID = '$question2'"; $rows = $db-&gt;query($sql); while ($record = $db-&gt;fetch_array($rows)) { $q2Text = $record["qName"]; } //THIS GETS THE NAMES FOR THE GOOGLE GRAPH $sql006 = "SELECT * from survey_selected_question, survey_selected_question_selected_option, options WHERE survey_selected_question.qID = '$question1' AND survey_selected_question_selected_option.ssqID = survey_selected_question.ssqID AND options.oID = survey_selected_question_selected_option.oID AND survey_selected_question.sID = '$sID'"; $rows006 = $db-&gt;query($sql006); $numRes006 = $db-&gt;affected_rows; $names = ""; while ($record006 = $db-&gt;fetch_array($rows006)) { $names .= "'".$record006["oName"]."',"; } // CLEAN UP THE NAMES VAR $names = substr($names, 0, -1); // START FUNCTION FOO function foo($answer1, $answer2, $sID){ // CREATE THE DB OBJECT $dbOne = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE); // DB CONNECTION $dbOne-&gt;connect(); $returnVal = 0; //GET THE DISTINCT SUBMISSIONS FROM THE ANSWERS TABLE FOR THIS SPECIFIC SURVEY $sql0 = "SELECT DISTINCT(submissionID) FROM answer WHERE sID = '$sID'"; $rows0 = $dbOne-&gt;query($sql0); while ($record0 = $dbOne-&gt;fetch_array($rows0)) { $submissionID = $record0["submissionID"]; // SELECT SUBMISSIONS WHERE ANSWER = PASSED ANSWER 1 or 2 $sql01 = "SELECT * from answer WHERE submissionID = '$submissionID' AND (answer = '$answer1' OR answer = '$answer2')"; $rows01 = $dbOne-&gt;query($sql01); $numRes = $dbOne-&gt;affected_rows; if($numRes=='2'){ $returnVal = $returnVal + 1; } } return $returnVal; $dbOne-&gt;close(); } //END FUNCTION FOO // START FUNCTION FOO2 function foo2($oID, $sID, $question1){ // CREATE THE DB OBJECT $dbTwo = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE); // DB CONNECTION $dbTwo-&gt;connect(); // GET OPTION IDS AND BUILD AN OUTWARD STRING FOR GOOGLE GRAPH OF RESULTS $sql2 = "SELECT * from survey_selected_question, survey_selected_question_selected_option, options WHERE survey_selected_question.qID = '$question1' AND survey_selected_question_selected_option.ssqID = survey_selected_question.ssqID AND options.oID = survey_selected_question_selected_option.oID AND survey_selected_question.sID = '$sID'"; $rows2 = $dbTwo-&gt;query($sql2); $returnVal2 = ""; while ($record2 = $dbTwo-&gt;fetch_array($rows2)) { $returnVal2 .= "".foo($record2["oID"], $oID, $sID).","; } //END FUNCTION FOO2 // CLEAN UP THE RETURNVAL2 substr($returnVal2, 0, -1); // RETURN THE VAL HERE return $returnVal2; $dbTwo-&gt;close(); } // START BUILDING THE google Array $sql003 = "SELECT * from survey_selected_question, survey_selected_question_selected_option, options WHERE survey_selected_question.qID = '$question2' AND survey_selected_question_selected_option.ssqID = survey_selected_question.ssqID AND options.oID = survey_selected_question_selected_option.oID AND survey_selected_question.sID = '$sID'"; $rows003 = $db-&gt;query($sql003); $numRes2 = $db-&gt;affected_rows; $googleArray = ""; while ($record003 = $db-&gt;fetch_array($rows003)) { $googleArray .= "['".$record003["oName"]."',".foo2($record003["oID"], $sID, $question1)."],"; ?&gt; &lt;? } // CLEAN UP THE GOOGLE ARRAY $googleArray = substr($googleArray, 0, -1); ?&gt; &lt;!DOCTYPE HTML&gt; &lt;html&gt; &lt;head&gt; &lt;meta name = "viewport" content = "width = device-width"&gt; &lt;meta name = "viewport" content = "initial-scale = 1.0, user-scalable = no"&gt; &lt;script type="text/javascript" src="https://www.google.com/jsapi"&gt;&lt;/script&gt; &lt;script type="text/javascript"&gt; google.load("visualization", "1", {packages:["corechart"]}); google.setOnLoadCallback(drawChart); function drawChart() { var data = google.visualization.arrayToDataTable([ ['Name',&lt;?= $names; ?&gt;], &lt;?= $googleArray; ?&gt; ]); var options = { title: 'Line graph for question "&lt;?= $q1Text; ?&gt;" against question "&lt;?= $q2Text; ?&gt;"' , colors:['#5D0F99','#2F543B','#BD2115','#D67314','#F92985','#0D85C7','#000D9C','#D9A5F6', '#27E80C', '#725F53'] }; var chart = new google.visualization.LineChart(document.getElementById('chart_div')); chart.draw(data, options); } &lt;/script&gt; &lt;style&gt; div#wrapper { max-width: 700px; overflow:hidden; background:#eee; margin: 0 auto; padding: 8px 8px 8px 8px; border:1px solid black; } a{ color:#000; } input.goback { background-color: #ccc; border: 1px outset #000; color: #000; padding: 4px; margin-left: 2px; } &lt;/style&gt; &lt;/head&gt; &lt;body&gt; &lt;div id="wrapper"&gt; &lt;h1&gt;&lt;?= $sName; ?&gt;&lt;/h1&gt; &lt;form method="post" action="main.php"&gt; &lt;p&gt;Show me average(s) on &lt;select name="oID"&gt; &lt;? //GET QUESTION ONE $sql004 = "SELECT * from survey_selected_question, survey_selected_question_selected_option, options WHERE survey_selected_question.qID = '$question1' AND survey_selected_question_selected_option.ssqID = survey_selected_question.ssqID AND options.oID = survey_selected_question_selected_option.oID AND survey_selected_question.sID = '$sID'"; $rows004 = $db-&gt;query($sql004); while ($record004 = $db-&gt;fetch_array($rows004)) { ?&gt; &lt;option value="&lt;?= $record004["oID"]; ?&gt;"&gt;&lt;?= $record004["oName"]; ?&gt;&lt;/option&gt; &lt;? } ?&gt; &lt;/select&gt; with these survey type(s): &lt;select name="types"&gt; &lt;option value="0"&gt;All&lt;/option&gt; &lt;? //GET THIS SURVEYS TYPES $sql005 = "SELECT * from survey_types, survey_selected_types WHERE survey_selected_types.sID = '$sID' AND survey_selected_types.stID = survey_types.stID"; $rows005 = $db-&gt;query($sql005); while ($record005 = $db-&gt;fetch_array($rows005)) { ?&gt; &lt;option value="&lt;?= $record005["stID"]; ?&gt;"&gt;&lt;?= $record005["stName"]; ?&gt;&lt;/option&gt; &lt;? } ?&gt; &lt;/select&gt; over a 12 month period. &lt;input type="hidden" name="sID" value="&lt;?= $sID; ?&gt;"&gt; &lt;input type="hidden" name="question1" value="&lt;?= $question1; ?&gt;"&gt; &lt;input type="hidden" name="edit" value="average"&gt; &lt;input type="hidden" name="question2" value="&lt;?= $question2; ?&gt;"&gt; &lt;input type="submit" name="submit" value="Go" class="goback"&gt; &lt;/p&gt; &lt;/form&gt; &lt;div id="chart_div" style="width: 675px; height: 500px;"&gt;&lt;/div&gt; &lt;form method="post" action="main.php"&gt; &lt;p&gt; &lt;input type="hidden" name="edit" value="survey"&gt; &lt;input type="hidden" name="sID" value="&lt;?= $sID; ?&gt;"&gt; &lt;input type="submit" value="Back To Statistics" class="goback"&gt; &lt;/p&gt; &lt;/form&gt; &lt;/div&gt; &lt;/body&gt; &lt;/html&gt; &lt;? $db-&gt;close(); ?&gt; </code></pre> <p>and here is the html it outputs (if and when it works... like i said sometimes it returns fairly quickly and sometimes not at all with an internal server error)</p> <pre><code>&lt;!DOCTYPE HTML&gt; &lt;html&gt; &lt;head&gt; &lt;meta name = "viewport" content = "width = device-width"&gt; &lt;meta name = "viewport" content = "initial-scale = 1.0, user-scalable = no"&gt; &lt;script type="text/javascript" src="https://www.google.com/jsapi"&gt;&lt;/script&gt; &lt;script type="text/javascript"&gt; google.load("visualization", "1", {packages:["corechart"]}); google.setOnLoadCallback(drawChart); function drawChart() { var data = google.visualization.arrayToDataTable([ ['Name','None','$1 - $50','$51 - $200','Over $200'], ['Yes',85,100,87,82,],['No',70,82,81,74,],['Undecided ',76,90,90,84,]]); var options = { title: 'Line graph for question "Not including this event, please estimate how much will you spend in the community during your visit, including accommodations, transportation, food, and gas, etc.)" against question "Would you describe this event as providing "good value?"' , colors:['#5D0F99','#2F543B','#BD2115','#D67314','#F92985','#0D85C7','#000D9C','#D9A5F6', '#27E80C', '#725F53'] }; var chart = new google.visualization.LineChart(document.getElementById('chart_div')); chart.draw(data, options); } &lt;/script&gt; &lt;style&gt; div#wrapper { max-width: 700px; overflow:hidden; background:#eee; margin: 0 auto; padding: 8px 8px 8px 8px; border:1px solid black; } a{ color:#000; } input.goback { background-color: #ccc; border: 1px outset #000; color: #000; padding: 4px; margin-left: 2px; } &lt;/style&gt; &lt;/head&gt; &lt;body&gt; &lt;div id="wrapper"&gt; &lt;h1&gt;Test Survey One&lt;/h1&gt; &lt;form method="post" action="main.php"&gt; &lt;p&gt;Show me average(s) on &lt;select name="oID"&gt; &lt;option value="33"&gt;None&lt;/option&gt; &lt;option value="34"&gt;$1 - $50&lt;/option&gt; &lt;option value="35"&gt;$51 - $200&lt;/option&gt; &lt;option value="36"&gt;Over $200&lt;/option&gt; &lt;/select&gt; with these survey type(s): &lt;select name="types"&gt; &lt;option value="0"&gt;All&lt;/option&gt; &lt;option value="13"&gt;One week&lt;/option&gt; &lt;option value="16"&gt;Paid&lt;/option&gt; &lt;option value="18"&gt;Fair&lt;/option&gt; &lt;option value="50"&gt;Live performance(s)&lt;/option&gt; &lt;option value="51"&gt;Exhibition&lt;/option&gt; &lt;option value="59"&gt;Community Pride&lt;/option&gt; &lt;option value="54"&gt;Local foods / goods (grown or produced)&lt;/option&gt; &lt;option value="53"&gt;Agriculture&lt;/option&gt; &lt;option value="52"&gt;Art and Culture&lt;/option&gt; &lt;option value="61"&gt;Industry&lt;/option&gt; &lt;option value="62"&gt;Shop local&lt;/option&gt; &lt;option value="39"&gt;Simcoe&lt;/option&gt; &lt;option value="48"&gt;Fall&lt;/option&gt; &lt;option value="65"&gt;Sale(s)&lt;/option&gt; &lt;option value="66"&gt;Vendors&lt;/option&gt; &lt;option value="67"&gt;Midway / rides&lt;/option&gt; &lt;option value="68"&gt;Live music&lt;/option&gt; &lt;option value="70"&gt;Live performance (not music or theatre)&lt;/option&gt; &lt;/select&gt; over a 12 month period. &lt;input type="hidden" name="sID" value="1342625918"&gt; &lt;input type="hidden" name="question1" value="5"&gt; &lt;input type="hidden" name="edit" value="average"&gt; &lt;input type="hidden" name="question2" value="17"&gt; &lt;input type="submit" name="submit" value="Go" class="goback"&gt; &lt;/p&gt; &lt;/form&gt; &lt;div id="chart_div" style="width: 675px; height: 500px;"&gt;&lt;/div&gt; &lt;form method="post" action="main.php"&gt; &lt;p&gt; &lt;input type="hidden" name="edit" value="survey"&gt; &lt;input type="hidden" name="sID" value="1342625918"&gt; &lt;input type="submit" value="Back To Statistics" class="goback"&gt; &lt;/p&gt; &lt;/form&gt; &lt;/div&gt; &lt;/body&gt; &lt;/html&gt; </code></pre> <p>Any thoughts on why this works sometimes quickly and others basically it appears to be timing out or something?</p> <p>NOTE: it seams that the first time i pass a question1 and question2 number and load the page it times out. If i hit refresh it loads</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser520300
    1. USuser520300
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload