SQL – My PHP / Javascript / JQuery notes

HTML, Javascript, PHP, SQL

Javascript – Building arrays for Chart.js from JSON data

Posted on December 20, 2018 by edleightonprogrammer

function getPerformanceinfo (){

var myLabelarray = [];
var myActualarray = [];

$.post(‘inl_sev_078_J_vrs.php’, {“UNIQUENO” : myReportref }, function(response) {

for(var i=0; i<response.length; i++) {

myLabelarray[i] = response[i]._TAK;

myActualarray[i] = response[i]._PERCENTAGE;

}

$(“#_UNIQUENUMBER”).prop(“disabled”, true);

myChartjs (myLabelarray, myActualarray);

}, ‘json’)

}

// — Parameters input ——————————————————–

$uniqueno = (isset($_REQUEST[‘UNIQUENO’])) ? $_REQUEST[‘UNIQUENO’] : ”;

// — Define containers ——————————————————-

$terugvoer = array();
$tmp = array();

// — Extract the category names and weights ——————————

$sql = “SELECT _QUESTIONNAIRE, _VERSION, _TAK, _PERCENTAGE FROM performance WHERE _UNIQUENO = ‘$uniqueno’ “;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

foreach ($selectStatus as $row) {

$tmp[‘_TAK’] = $row[‘_TAK’];
$tmp[‘_PERCENTAGE’] = $row[‘_PERCENTAGE’];

$terugvoer[] = $tmp;

}

// echo ‘<pre>’;
// print_r($terugvoer);
// echo ‘</pre>’;

echo json_encode($terugvoer);

Javascript, JSON, PHP, SQL

Javascript – Building arrays from JSON response data

Posted on December 18, 2018 by edleightonprogrammer

function getPerformanceinfo (){

var myLabelarray = [];
var myActualarray = [];

$.post(‘inl_sev_078_J_vrs.php’, {“UNIQUENO” : myReportref }, function(response) {

for(var i=0; i<response.length; i++) {

myLabelarray[i] = response[i]._TAK;

myActualarray[i] = response[i]._PERCENTAGE;

}

myChartjs (myLabelarray, myActualarray);

}, ‘json’)

}

// — Parameters input ——————————————————–

$uniqueno = (isset($_REQUEST[‘UNIQUENO’])) ? $_REQUEST[‘UNIQUENO’] : ”;

// — Define containers ——————————————————-

$terugvoer = array();
$tmp = array();

// — Extract the category names and weights ——————————

$sql = “SELECT _QUESTIONNAIRE, _VERSION, _TAK, _PERCENTAGE FROM performance WHERE _UNIQUENO = ‘$uniqueno’ “;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

foreach ($selectStatus as $row) {

$tmp[‘_QUESTIONNAIRE’] = $row[‘_QUESTIONNAIRE’];
$tmp[‘_VERSION’] = $row[‘_VERSION’];
$tmp[‘_TAK’] = $row[‘_TAK’];
$tmp[‘_PERCENTAGE’] = $row[‘_PERCENTAGE’];

$terugvoer[] = $tmp;

}

// echo ‘<pre>’;
// print_r($terugvoer);
// echo ‘</pre>’;

echo json_encode($terugvoer);

PHP, SQL

PHP – The switch statement

Posted on December 12, 2018 by edleightonprogrammer

switch ($event) {

case “TAK”:
$sql = “SELECT DISTINCT _TAK FROM questionnaire WHERE _TEMPLATE = ‘$questionnaire’ AND _VERSION = ‘$version’ AND _FINAL = ” “;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

break;

case “FIN”:
$sql = “SELECT DISTINCT _TAK FROM questionnaire WHERE _TEMPLATE = ‘$questionnaire’ AND _VERSION = ‘$version’ AND _FINAL = ‘Y’ “;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

break;

default:
echo “Fout / Fault”;

}

JSON, PHP, SQL

MySQL – Retrieving data for EasyUI datagrid

Posted on December 5, 2018December 5, 2018 by edleightonprogrammer

<?php

require_once($_SERVER[‘DOCUMENT_ROOT’].’/xxx/db/MysqliDb.php’);

// — Define containers ——————————————————-

$terugvoer = array();
$tmp = array();

// — Retrieve the list of questions by DEPARTMENT —————————–

$sql = “SELECT _DATE, _TIME, _USERID, _NAME, _ACTIVITY FROM audit”;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

if ($selectStatus > 0) {

$tmp[‘message’] = ‘Central evaluation system – Questions loaded successfully’;
$tmp[‘success’] = ‘Y’;

foreach ($selectStatus as $row) {

$tmp[‘_DATE’] = $row[‘_DATE’];
$tmp[‘_TIME’] = $row[‘_TIME’];

$tmp[‘_USERID’] = $row[‘_USERID’];
$tmp[‘_NAME’] = $row[‘_NAME’];

$tmp[‘_ACTIVITY’] = $row[‘_ACTIVITY’];

$terugvoer[] = $tmp;

}

echo json_encode($terugvoer);

Javascript, JQuery, SQL

Populate select 2 with JSON data

Posted on November 28, 2018November 28, 2018 by edleightonprogrammer

BranchSelect

function getTak (questionnaire, version) {

$(‘#_TAK’).children(‘option:not(:first)’).remove();

$.post(‘inl_sev_055_J_nvg.php’,{“QUESTIONNAIRE” : questionnaire, “VERSION” : version}, function(data) {

var i;

for (i = 0; i < data.length; i++) {

text = data[i];

newOption = new Option(text, i, false, false);

$(‘#_TAK’).append(newOption).trigger(‘change’);

}

}, ‘json’);

}

// — Parameters input ——————————————————–

$questionnaire = (isset($_REQUEST[‘QUESTIONNAIRE’])) ? $_REQUEST[‘QUESTIONNAIRE’] : ”;
$version = (isset($_REQUEST[‘VERSION’])) ? $_REQUEST[‘VERSION’] : ”;

// — Define containers ——————————————————-

$terugvoer = array();

// — Retrieve the present list of versions ———————————–

$sql = “SELECT DISTINCT _TAK FROM questionnaire WHERE _TEMPLATE = ‘$questionnaire’ AND _VERSION = ‘$version’ “;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

if ($selectStatus > 0) {

foreach ($selectStatus as $row) {

$terugvoer[] = $row[‘_TAK’];

}

echo json_encode($terugvoer);

Javascript, PHP, SQL

PHP – SQL data stored in array passed to javascript

Posted on November 27, 2018November 28, 2018 by edleightonprogrammer

// — Retrieve the list of categories —————————————–

$sql = “SELECT _CATEGORY, _SUBCATEGORY FROM categories WHERE _DEPT = ‘$dept'”;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql); /

if ($selectStatus > 0) {

foreach ($selectStatus as $row) {

$categoryArray[] = $row[‘_CATEGORY’];
$subcategoryArray[] = $row[‘_SUBCATEGORY’];

}

PHPArrayToJSArray

$(document).ready( function () {

var subcategoryArray = <?php echo ‘[“‘ . implode(‘”, “‘, $subcategoryArray) . ‘”]’ ?>;

On select

$(‘#_CATEGORY_LIST0’).on(‘select2:select’, function (e) {
$(‘#SUB_CATEGORY_LIST0’).val(subcategoryArray[e.params.data.id]);
});

PHP, SQL

PHP – serialise data for storage in MySQL

Posted on November 27, 2018November 28, 2018 by edleightonprogrammer

$cwarray = array($cw0, $cw1, $cw2, $cw3, $cw4, $cw5, $cw6, $cw7, $cw8, $cw9, $cw10, $cw11, $cw12, $cw13, $cw14, $cw15, $cw16, $cw17, $cw18, $cw19, $cw20, $cw21, $cw22, $cw23, $cw24, $cw25, $cw26, $cw27, $cw28, $cw29);

$upto30catw = serialize($cwarray); // MySQL field must be TEXT!!!

$sql = “UPDATE templates SET _UPTO30CATW=’$upto30catw’ WHERE temp_id=$isn”;

EasyUI, HTML, PHP, SQL

PHP – unserialise and JSON data for EasyUI

Posted on November 27, 2018November 28, 2018 by edleightonprogrammer

After034_E

require_once($_SERVER[‘DOCUMENT_ROOT’].’/xyz/db/MysqliDb.php’);

// — Parameters input ——————————————————–

$dept = (isset($_REQUEST[‘DEPT’])) ? $_REQUEST[‘DEPT’] : ”;
$template = (isset($_REQUEST[‘TEMPLATE’])) ? $_REQUEST[‘TEMPLATE’] : ”;

// — Define containers ——————————————————-

$terugvoer = array();
$tmp = array();

// — Retrieve the present list of questions ———————————-

$sql = “SELECT _QUESTION, _YN, _COMMENTONLY FROM questions WHERE _DEPT = ‘$dept'”;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

if ($selectStatus > 0) {

foreach ($selectStatus as $row) {

$questionArray[] = $row[‘_QUESTION’];
$ynArray[] = $row[‘_YN’];
$commentonlyArray[] = $row[‘_COMMENTONLY’];

}

// — Retrieve the list of categories —————————————–

$sql = “SELECT _CATEGORY, _SUBCATEGORY FROM categories WHERE _DEPT = ‘$dept'”;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

if ($selectStatus > 0) {

foreach ($selectStatus as $row) {

$categoryArray[] = $row[‘_CATEGORY’];
$subcategoryArray[] = $row[‘_SUBCATEGORY’];

}

// — Retrieve the TEMPLATE selection data ————————————

$sql = “SELECT _TEMPLATE, _UPTO30CAT, _UPTO30CATW FROM templates WHERE _TEMPLATE = ‘$template'”;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

if ($selectStatus > 0) {

foreach ($selectStatus as $row) { // Only one row ….. [0]

$tmp[‘_TEMPLATE’] = $row[‘_TEMPLATE’];
$tmp[‘_UPTO30CAT’] = unserialize($row[‘_UPTO30CAT’]);
$tmp[‘_UPTO30CATW’] = unserialize($row[‘_UPTO30CATW’]);

foreach ($tmp[‘_UPTO30CAT’] as $key=>$value) {

if ($value != ”) {

$tmp[‘_CATEGORY’] = $categoryArray[$value];
$tmp[‘_SUBCATEGORY’] = $subcategoryArray[$value];

$tmp[‘_CWEIGHT’] = $tmp[‘_UPTO30CATW’][$key];

$searchVal = $tmp[‘_CATEGORY’];

$sql = “SELECT _CATEGORY, _SUBCATEGORY, _UPTO15QSUBHEADING, _UPTO15QUES, _UPTO15PUNT, _UPTO15WEIGHTS FROM categories WHERE _CATEGORY = ‘$searchVal'”;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

if ($selectStatus > 0) {

foreach ($selectStatus as $row) {

// — Store questions as HTML representation ——————–

foreach (unserialize($row[‘_UPTO15QUES’]) as $key=>$value) {

if ($value != ”) {

$tmp[‘myHeadingsArray’] = unserialize($row[‘_UPTO15QSUBHEADING’]);

$tmp[‘_QSUBHEADING’] = $tmp[‘myHeadingsArray’][$key];

$tmp[‘myPointsArray’] = unserialize($row[‘_UPTO15PUNT’]);

$tmp[‘_QWEIGHT’] = $tmp[‘myPointsArray’][$key];

$tmp[‘_QUESTION’] = $questionArray[$value];

$terugvoer[] = $tmp;

// ————————————
$tmp[‘_CATEGORY’] = ”;
$tmp[‘_SUBCATEGORY’] = ”;
$tmp[‘_CWEIGHT’] = ”;
// ————————————

} // End if

} // End store questions as HTML representation

} // End while

} // End of Question code

}

echo json_encode($terugvoer);

PHP, SQL

Create an array from a SQL query in PHP

Posted on November 27, 2018 by edleightonprogrammer

// — Retrieve the list of questions ——————————————

$sql = “SELECT _QUESTION FROM questions WHERE _DEPT = ‘$dept'”;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

$questionArray = [];

if ($selectStatus > 0) {

$tmp[‘message’] = ‘Central evaluation system – Questions loaded successfully’;
$tmp[‘success’] = ‘Y’;

foreach ($selectStatus as $row) {

$questionArray[] = $row[‘_QUESTION’];

}

EasyUI, Font awesome, PHP, SQL

EasyUI Datagrid JSON information

Posted on November 27, 2018November 28, 2018 by edleightonprogrammer

require_once($_SERVER[‘DOCUMENT_ROOT’].’/xyz/db/MysqliDb.php’);

// — Parameters input ——————————————————–

$questionnaire = (isset($_REQUEST[‘QUESTIONNAIRE’])) ? $_REQUEST[‘QUESTIONNAIRE’] : ”;
$version = (isset($_REQUEST[‘VERSION’])) ? $_REQUEST[‘VERSION’] : ”;
$tak = (isset($_REQUEST[‘TAK’])) ? $_REQUEST[‘TAK’] : ”;

// — Define containers ——————————————————-

$terugvoer = array();
$tmp = array();

// — Retrieve the list of UPLOADID’s by DEPARTMENT —————————

$sql = “SELECT upload_id, _UPLOADID FROM uploads
WHERE _TAK = ‘$tak’ AND _TEMPLATE = ‘$questionnaire’ AND _VERSION = ‘$version’ “;

$db = new MysqliDb(‘CES’);

$selectStatus = $db->rawQuery($sql);

if ($selectStatus > 0) {

foreach ($selectStatus as $row) {

$tmp[‘_UPLOADID’] = $row[‘_UPLOADID’];

$uploadid = $row[‘_UPLOADID’];
$isn = $row[‘upload_id’];

// — Add buttons with event parameters ———————————

$tmp[‘view’] = ‘<a onclick=”viewDoc (\”.$uploadid.’\’)”
<button class=”btn btn-info fa fa-info-circle”> </button></a>’;

// ———————————————————————–

$tmp[‘delete’] = ‘<a onclick=”Delete (\”.$uploadid.’\’)”
<button class=”btn btn-danger fa fa-trash”> </button></a>’;

// ———————————————————————–

$terugvoer[] = $tmp;

}

echo json_encode($terugvoer);