Jump to content

SLSCoder

Members
  • Posts

    46
  • Joined

  • Last visited

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

SLSCoder's Achievements

Member

Member (2/5)

2

Reputation

2

Community Answers

  1. gizmola, again thanks for your response. I am looking for ways to speed the app up so this is clearly something I'm going to have to study and implement. I'll look into Redis and learn what it will take to use it and where in my app it will help. Thanks again.
  2. I'm almost scared to ask this because I kind of expect you guys to chew me to bits - lol - but I have to try and find out. I have to be able to reasonably handle 50,000 records total (1 = Count + AvgX + AvgY) from this set of queries. I can't think of a better way to do it and it works fine with a few records (13 surveys total in the initial tests) but I'm concerned about it being efficient enough with more records. I'm wondering if putting it all into a stored proc would speed it up. I don't like stored procs. I prefer the code to be in my server side language (PHP in this case). If I have to change the database server stored procs make the job bigger, I have to rewrite all that code. The records are the Total count, Total X Avg, Total Y Avg and the Counts and the X and Y averages for each cell. The WHERE clauses are pretty straight forward; generally just a filter for answers.QuestionID and maybe a few other answers table fields. The FILTER can include combinations of any fields from the surveys and/or answers table. The FILTER params come from a client side form so must be protected from sql injection - thus PDO. $arParams is an array of the FILTER params. Here, I'm building a cross tab report for 2 params (ie QuestionID: 134 and QuestionID 78) in numeric ranges given: Min1 to Max1 by Interval1, Min2 to Max2 by Interval2. I know this is pretty complicated. If it's too much don't worry about it. Like I said, this code works, it's just an awful lot of queries so I suspect with a lot of records it'll be slow. //Get Total Count and Total X and Y averages try { $sql = "SELECT COUNT(DISTINCT surveys.RecordID) AS Cnt FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 . ") AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . ")"; if(!empty($Filter)) $sql .= " AND " . $Filter; $ar = $this->executePDOQuery($sql, $arParams); $Total = $ar[0]["Cnt"]; $sql = "SELECT AVG(" . $DataFld1 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE " . $Where1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . ")"; if(!empty($Filter)) $sql .= " AND " . $Filter; $ar = $this->executePDOQuery($sql, $arParams); if(is_numeric($ar[0]["Avg"])) $TotalXAvg = round($ar[0]["Avg"], 1); else $TotalXAvg = ""; $sql = "SELECT AVG(" . $DataFld2 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 . ") AND " . $Where2; if(!empty($Filter)) $sql .= " AND " . $Filter; $ar = $this->executePDOQuery($sql, $arParams); if(is_numeric($ar[0]["Avg"])) $TotalYAvg = round($ar[0]["Avg"], 1); else $TotalYAvg = ""; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp1n1Data 2: " . $exp->getMessage() . "<br>" . $sql); } //Get First X & First & Count and XAvg and YAvg try { $sqlA1 = "SELECT COUNT(DISTINCT surveys.RecordID) AS Cnt FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 ." AND " . $DataFld1 . " < " . $CurMax1 . ")"; $sqlA = $sqlA1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlA .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlA, $arParams); $arRec = []; $arRec["Cnt"] = $ar[0]["Cnt"]; if($arRec["Cnt"] > $MaxCnt) $MaxCnt = $arRec["Cnt"]; $sqlB1 = "SELECT AVG(" . $DataFld1 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE " . $Where1 . " AND " . $DataFld1 . " < " . $CurMax1; $sqlB = $sqlB1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlB .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlB, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["XAvg"] = round($ar[0]["Avg"], 1); if($arRec["XAvg"] > $MaxXAvg) $MaxXAvg = $arRec["XAvg"]; } else $arRec["XAvg"] = "n/a"; $sqlC1 = "SELECT AVG(" . $DataFld2 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 ." AND " . $DataFld1 . " < " . $CurMax1 . ")"; $sqlC = $sqlC1 . " AND " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2; if(!empty($Filter)) $sqlC .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlC, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["YAvg"] = round($ar[0]["Avg"], 1); if($arRec["YAvg"] > $MaxYAvg) $MaxYAvg = $arRec["YAvg"]; } else $arRec["YAvg"] = "n/a"; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp1n1Data 2: " . $exp->getMessage() . "<br>" . $sql); } $arData[$idxX] = []; $arData[$idxX][$idxY] = $arRec; $YLabels[$idxY] = $CurMin2 . " to < " . $CurMax2; $XLabels[$idxX] = $CurMin1 . " to < " . $CurMax1; //Get First X and Remaining Y Count and Averages while($CurMax2 <= $Max2) { $idxY++; $CurMin2 = $CurMax2; $CurMax2 = $CurMin2 + $Interval2; try { $sqlA = $sqlA1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlA .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlA, $arParams); $arRec = []; $arRec["Cnt"] = $ar[0]["Cnt"]; if($arRec["Cnt"] > $MaxCnt) $MaxCnt = $arRec["Cnt"]; $sqlB = $sqlB1 . " AND surveys.RecordID IN (" . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlB .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlB, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["XAvg"] = round($ar[0]["XAvg"], 1); if($arRec["XAvg"] > $MaxXAvg) $MaxXAvg = $arRec["XAvg"]; } else $arRec["XAvg"] = "n/a"; $sqlC = $sqlC1 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 ; if(!empty($Filter)) $sqlC .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlC, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["YAvg"] = round($ar[0]["Avg"], 1); if($arRec["YAvg"] > $MaxYAvg) $MaxYAvg = $arRec["YAvg"]; } else $arRec["YAvg"] = "n/a"; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp1n1Data 2: " . $exp->getMessage() . "<br>" . $sql); } $arData[$idxX][$idxY] = $arRec; $YLabels[$idxY] = $CurMin2 . " to < " . $CurMax2; } $Max2 = round($Max2); if($CurMax2 > $Max2) $YLabels[$idxY] = $CurMin2 . " to " . $Max2; //Get remaining X Counts & for each, First X and Y Average while($CurMax1 <= $Max1) { $CurMin2 = $Min2; $CurMax2 = $CurMin2 + $Interval2; $CurMin1 = $CurMax1; $CurMax1 = $CurMin1 + $Interval1; $idxX++; $idxY = 0; $arData[$idxX] = []; try { $sqlA1 = "SELECT COUNT(DISTINCT surveys.RecordID) AS Cnt FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 . " AND " . $DataFld1 . " >= " . $CurMin1 ." AND " . $DataFld1 . " < " . $CurMax1 . ")"; $sqlA = $sqlA1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlA .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlA, $arParams); $arRec = []; $arRec["Cnt"] = $ar[0]["Cnt"]; if($arRec["Cnt"] > $MaxCnt) $MaxCnt = $arRec["Cnt"]; $sqlB1 = "SELECT AVG(" . $DataFld1 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE " . $Where1 . " AND " . $DataFld1 . " >= " . $CurMin1 ." AND " . $DataFld1 . " < " . $CurMax1; $sqlB = $sqlB1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlB .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlB, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["XAvg"] = round($ar[0]["Avg"], 1); if($arRec["XAvg"] > $MaxXAvg) $MaxXAvg = $arRec["XAvg"]; } else $arRec["XAvg"] = "n/a"; $sqlC1 = "SELECT AVG(" . $DataFld2 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 . " AND " . $DataFld1 . " >= " . $CurMin1 ." AND " . $DataFld1 . " < " . $CurMax1 . ")"; $sqlC = $sqlC1 . " AND " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 ; if(!empty($Filter)) $sqlC .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlC, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["YAvg"] = round($ar[0]["Avg"], 1); if($arRec["YAvg"] > $MaxYAvg) $MaxYAvg = $arRec["YAvg"]; } else $arRec["YAvg"] = "n/a"; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp2NumData: " . $exp->getMessage() . "<br>" . $sql); } $arData[$idxX][$idxY] = $arRec; //Get Counts & Averages for Remaining Y while($CurMax2 <= $Max2) { $idxY++; $CurMin2 = $CurMax2; $CurMax2 = $CurMin2 + $Interval2; try { $sqlA = $sqlA1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlA .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlA, $arParams); $arRec = []; $arRec["Cnt"] = $ar[0]["Cnt"]; if($arRec["Cnt"] > $MaxCnt) $MaxCnt = $arRec["Cnt"]; $sqlB = $sqlB1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlB .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlB, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["XAvg"] = round($ar[0]["Avg"], 1); if($arRec["XAvg"] > $MaxXAvg) $MaxXAvg = $arRec["XAvg"]; } else $arRec["XAvg"] = "n/a"; $sqlC = $sqlC1 . " AND " . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2; if(!empty($Filter)) $sqlC .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlC, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["YAvg"] = round($ar[0]["Avg"], 1); if($arRec["YAvg"] > $MaxYAvg) $MaxYAvg = $arRec["YAvg"]; } else $arRec["YAvg"] = "n/a"; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp2NumData: " . $exp->getMessage() . "<br>" . $sql); } $arData[$idxX][$idxY] = $arRec; } }
  3. gizmola thanks for your response. If I understand memcached correctly it won't help me. The data is constantly changing, it's not static at all.
  4. OK thanks. I'll make that a preference as well.
  5. I have a table1 with an ID and a Name where the ID is found in a sql query on different table2. I have access to an existing PHP array containing all the IDs and Names of table1. For each of the records I find n the sql query on table2 I have a choice. I can either INNER JOIN table1 on table2 in the sql query and then include the Name in the query OR while looping through the query recordset, for each record found I can loop through the array of table1 and match the ID to the ID in the query record to get the Name. Which method is generally faster?
  6. I added the code in the page because it still would not display the errors. I wasn't confident that I had the 'right' php.ini file even though I did edit the one shown in php_info.php. Normally, that code wouldn't be there. I'm aware that & ~E_NOTICE turns off notices. My problem was that even if I have php.ini set as: error_reporting = E_ALL the page still wouldn't show the parse error. The error was caused by a missing semi colon and yesterday the page returned: This page isn’t working Today the error displays as it should. I've removed the error display code on the page and it still works fine. I *swear* I didn't change anything. Yesterday when I quit, it didn't work. Today, it works just fine. ?#)!@#$??? It's all good now. You guys are a lot of help. We appreciate you. Thanks
  7. This code works $sErr = "Hello World"; echo $sErr . "<br>"; This code makes the page display: This page isn’t working $sErr = "Hello World" echo $sErr . "<br>"; Why is it doing that and how can I make it show me the error?
  8. Yea, sorry. I'm working on it. It turns out that it seems to be doing it on compile time errors. What caused it to return This page isn't working was a missing semi-colon.
  9. I've been through this a hundred times. I cannot make php display errors consistently. My php.ini file has this: error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_STRICT display_errors = On display_startup_errors = On log_errors = On ignore_repeated_errors = Off ignore_repeated_source = Off report_memleaks = On Nothing exists below these params that would change them. I have a file https://dev.aecperformance.com/php_info.php which shows my php info. When I pull that up I see: Loaded Configuration File /etc/php/8.1/apache2/php.ini I don't understand why this is. It used to be in /etc/php/8.1/fpm php8.1-fpm is installed and is active. Whether or not php is actually using it I can't say. It should be using fpm. Nevertheless, I have uploaded the php.ini file to both: /etc/php/8.1/apache2/php.ini and to /etc/php/8.1/fpm/php.ini I've rebooted the server since making the changes to make sure that php is reloaded. On my web page I have: ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL); STILL when I try to load the page I get: This page isn’t working https://dev.aecperformance.com/test.php How can I make PHP display errors and suppress warnings?
  10. I figured this out. The problem was not with the at command, the user www-data did not have permissions to create the directory. This is resolved.
  11. I should add that: The file /etc/at.deny does not include www-data (which is $_Server["user"]) The file /etc/at.allow does include www-data.
  12. Ubuntu 20.04 apache2 php 8.0.14 php-fpm I'm trying to execute an at command from php. The code is: $out = shell_exec('echo mkdir /var/www/test | at now +1 minutes'); echo $out . "<br>"; If I run that command from the command line it works fine. When I run it from php I get nothing back into the $out variable and no directory is created. How can I run an at command from php?
  13. I changed the php code to: echo $_SERVER['USER'] . "<br>"; The response this time was: www-data So now I'm confused. Is the logged in user root or www-data? I need the logged in user to be www-data, not root.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.