PHP - auto-mate/CheatSheetWiki GitHub Wiki
Access Object Variables
Arrays
Autoloading and spl_autoload_register
Classes
Constants
Cookies
Dates
Documentor
Directory List
Show SqlView TSQL
Any File As <xmp>
Foreach Array Item
Functions and Globals
If and $_GET
Include Once
JSON Encode and stdClass Object
JSON For Plotly
LDAP Validation
Login Code SQL Server
Number Format
Namespaces
Parse
PHPSpreadsheet
PHP Tags
Run Function
Sessions
Timeout current script only
Try Catch
SSE
shell_exec
SQL Server Error
Switch
var_dump
Variables
Variable Variables
Using Data From SQL Server
Using Data From SQL Server Procedure
XML
XDebug
<?php
// LINE COMMENT
/*
MULITLINE COMMENT
All php wrapped in <?php ?> Tag set
*/
?>
loadGlobals();
e.g. Checks for "https://someaddress.php?sFilter=SomeDataPassedToThePage" OR "https://someaddress.php"
if(ISSET($_GET["sFilter"])) {$someVar=$_GET["sFilter"];} else {$someVar="";}
All Start with $
e.g to use a variable called $sqlP1 it can be called as follows where for example $i = 1
echo ${'sqlP'.$i};
example loads Login Details to Globals..
function loadGlobals() {
//This File = GLOBALS.php
$GLOBALS['serverName'] = 'LOCATION\SERVER';
$GLOBALS['UID']="uid";
$GLOBALS['PWD']="password";
$GLOBALS['Database']="dbName";
}
example uses Globals in function to open SQL Server Connection
function getSQLOpen($sql) {
$GLOBALS['connection'] = array( "UID"=>$GLOBALS['UID'], "PWD"=>$GLOBALS['PWD'], "Database"=>$GLOBALS['Database']);
$GLOBALS['conn'] = sqlsrv_connect( $GLOBALS['serverName'] , $GLOBALS['connection']);
//CHECK CONNECTION IS OK
if ($GLOBALS['conn'] === false) {
echo "error!";
die( print_r( sqlsrv_errors(), true));
return false;
}
else
{
$GLOBALS['stmt'] = sqlsrv_query($GLOBALS['conn'], $sql);
$GLOBALS['fld_cnt']=sqlsrv_num_fields($GLOBALS['stmt']);
$GLOBALS['fld_dta']=sqlsrv_field_metadata( $GLOBALS['stmt']);
return true;
}
}
example uses Globals in function to close SQL Server Connection
function getSQLClose() {
sqlsrv_free_stmt($GLOBALS['stmt']);
sqlsrv_close($GLOBALS['conn']);
}
switch ($Var_Name) {
case 'A':
$SomeVar="Value1";
break;
case 'B':
$SomeVar="Value2";
break;
}
$sql = "select * from table " . $sFilter . " order by Field1,Field2...";
// SEE EXAMPLE FUNCTION getSQLOpen
//================================
if (getSQLOpen($sql)) {
// Create Table With BOOTSTRAP classes
//=====================================
echo "<table class=\"table table-hover\">";
// Write Header Row
//=================
echo "<tr>";
// Add Field Names To Headers [SEE EXAMPLE FUNCTION getSQLOpen for WHERE $fld_dta is sourced from]
//=================================================================================================
for ($i=0;$i<$fld_cnt-1;$i++) {
echo "<th>".$fld_dta[$i]["Name"]."</th>";
}
// End Header ROW
//===============
echo "</tr>";
//Write Data Rows [For $fld_cnt SEE EXAMPLE FUNCTION getSQLOpen]
//==============================================================
// * SQLSRV_FETCH_NUMERIC gets fields by Number a Named Option is Available too
//=============================================================================
While ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
// START ROW
//==========
echo "<tr>";
// FILL ROW
//==========
for ($i=0;$i<$fld_cnt-1;$i++) {
echo "<td>".$row[i]."</td>";
}
// END ROW
//==========
echo "</tr>";
}
//END TABLE
//==========
echo "<table>";
}
example show var
echo "\$ ".number_format($rowTot,0,'.',',');
OR
For Zero fill - just use the sprintf() function
$pr_id = 1;
$pr_id = sprintf("%'03d", $pr_id);
echo $pr_id;
//outputs 001
-----------------
$pr_id = 10;
$pr_id = sprintf("%'03d", $pr_id);
echo $pr_id;
//outputs 010
-----------------
You can change %'03d to %'04d, etc.
example make $d as current date time take off 7 days and show as previous week number.
$d=new DateTime();
date_modify($d, '-7 day');
echo date_format($d,"W");
example format date in $row[$i] as [YYYY]-[01 to 12]-[01 to 31]
echo date_format($row[$i],'Y-m-d');
•d - The day of the month (from 01 to 31)
•D - A textual representation of a day (three letters)
•j - The day of the month without leading zeros (1 to 31)
•l (lowercase 'L') - A full textual representation of a day
•N - The ISO-8601 numeric representation of a day (1 for Monday, 7 for Sunday)
•S - The English ordinal suffix for the day of the month (2 characters st, nd, rd or th. Works well with j)
•w - A numeric representation of the day (0 for Sunday, 6 for Saturday)
•z - The day of the year (from 0 through 365)
•W - The ISO-8601 week number of year (weeks starting on Monday)
•F - A full textual representation of a month (January through December)
•m - A numeric representation of a month (from 01 to 12)
•M - A short textual representation of a month (three letters)
•n - A numeric representation of a month, without leading zeros (1 to 12)
•t - The number of days in the given month
•L - Whether it's a leap year (1 if it is a leap year, 0 otherwise)
•o - The ISO-8601 year number
•Y - A four digit representation of a year
•y - A two digit representation of a year
•a - Lowercase am or pm
•A - Uppercase AM or PM
•B - Swatch Internet time (000 to 999)
•g - 12-hour format of an hour (1 to 12)
•G - 24-hour format of an hour (0 to 23)
•h - 12-hour format of an hour (01 to 12)
•H - 24-hour format of an hour (00 to 23)
•i - Minutes with leading zeros (00 to 59)
•s - Seconds, with leading zeros (00 to 59)
•u - Microseconds (added in PHP 5.2.2)
•e - The timezone identifier (Examples: UTC, GMT, Atlantic/Azores)
•I (capital i) - Whether the date is in daylights savings time (1 if Daylight Savings Time, 0 otherwise)
•O - Difference to Greenwich time (GMT) in hours (Example: +0100)
•P - Difference to Greenwich time (GMT) in hours:minutes (added in PHP 5.1.3)
•T - Timezone abbreviations (Examples: EST, MDT)
•Z - Timezone offset in seconds. The offset for timezones west of UTC is negative (-43200 to 50400)
•c - The ISO-8601 date (e.g. 2013-05-05T16:34:42+00:00)
•r - The RFC 2822 formatted date (e.g. Fri, 12 Apr 2013 12:01:05 +0200)
•U - The seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)
start with session_start() function
session_start() must be the absolute first thing in any document. <?php session_start(); .....
<?php session_start(); .....
session_unset() Removes Vars
session_unset();
session_destroy() destroys the session
session_destroy();
session variables set with $_SESSION
$_SESSION["VarName"]="VarValue"
See Also foreach
$ArrVar = array("A", "B", "C");
echo $ArrVar[0] . ", " . $ArrVar[1] . " and " . $ArrVar[2] . ".";
echo "<BR>";
echo count($ArrVar);
OR
define("SOME_DEFINED_CONSTANT","Yum");
$request = [
'WebAuthenticationDetail' => [
'UserCredential' => [
'Key' => "SomeKey",
'Password' => SOME_DEFINED_CONSTANT
],
]
];
var_dump($request);
PUSH
Add "D" and "E" to the end of an array...
$testArray = array( "A", "B", "C" );
array_push( $testArray, "D", "E" );
print_r( $testArray );
NB Can use arsort($someArray,0 to 5 as below);
0 = SORT_REGULAR - Default. Compare items normally (don't change types)
1 = SORT_NUMERIC - Compare items numerically
2 = SORT_STRING - Compare items as strings
3 = SORT_LOCALE_STRING - Compare items as strings, based on current locale
4 = SORT_NATURAL - Compare items as strings using natural ordering
5 = SORT_FLAG_CASE -
**Array Sort Reverse Order **
arsort($someArray);
print_R($someArray);
NB Can use arsort($someArray,0 to 5 as below);
0 = SORT_REGULAR - Default. Compare items normally (don't change types)
1 = SORT_NUMERIC - Compare items numerically
2 = SORT_STRING - Compare items as strings
3 = SORT_LOCALE_STRING - Compare items as strings, based on current locale
4 = SORT_NATURAL - Compare items as strings using natural ordering
5 = SORT_FLAG_CASE -
Array Sort Ascending Order
asort($someArray);
print_R($someArray);
Changing Associative Arrays
/* SAMPLE ARRAY */
$thing = [
'one' => 'MyText',
'two' => 3,
'three' => ['subd1'=>1,'subd2'=>2]
];
/* VIEW */
print_r($thing);
echo "<BR>";
/* CHANGE SIMPLE VALUE */
$thing['two'] = 'COOL';
/* VIEW */
print_r($thing);
echo "<BR>";
/* CHANGE SUB ARRAY VALUE NB Also works where Item is not currently array e.g. try $thing['one'] = ... */
$thing['three'] = ['newstuff1'=>'owt','newstuff2'=>'moreCrap','embeddedExtra'=>['a'=>1,'b'=>2,'c'=>3]];
/* VIEW */
print_r($thing);
echo "<BR>";
/* Change Item for Unknown Array Structure */
for ($n=0;$n<5;$n++) {
$ThreeReplace['Var'.$n] = $n;
if ( $n==2 ) {
$ThreeReplace['Var'.$n] = Array(); //NEED TO CREATE ARRAY WHERE IT DOESNT EXIST HERE (USE is_array(<ARRAY[ITEM]>) if required)
$ThreeReplace['Var'.$n]['NEW1']='OK';
$ThreeReplace['Var'.$n]['NEW2']='OK2';
}
}
$thing['three']=$ThreeReplace;
/* VIEW */
print_r($thing);
echo "<BR>";
/* Append Items */
$someNewArray = ['someNewArrayItem'=>'someNewArrayItemVALUE'];
$thing = array_merge($someNewArray,$thing);
/* VIEW */
print_r($thing);
echo "<BR>";
include_once("..\.......\FileName.php");
Write
setcookie("nameOfCookie","valueToStore",<expire as int e.g. "time()+(60*60*24*365*5)" for 5 years from now>);
Read
echo $_COOKIE["nameOfCookie"];
<?php
$root='C:\inetpub\wwwroot';
echoHeader($root);
recglob($root,'list');
recglob($root,'report');
echo "</div></body></html>";
function recglob($path,$listORreport) {
if ($listORreport != "list" && $listORreport != "report" ) {$listORreport='list';}
$x=glob($path);
foreach($x as $link)
{
if (is_dir($link))
{
recglob($link . "\*",$listORreport);
}
else
{
if ((substr($link, -3) == 'php' ||
substr($link, -3) == 'htm' ||
substr($link, -4) == 'html') &&
substr($link,-12) != 'Document.php')
{
if ($listORreport == "list")
{
echo "<a href=\"#$link\">$link</a><br>";
}
else
{
ripComment(file_get_contents($link) ,$link);
}
}
}
}
}
function ripComment($fc,$fname) {
$fcLine=explode(PHP_EOL,$fc);
$out = template();
$flinks = '';
$version='';
$multilineFlag = false;
$replacements = array();
$linksflag = false;
$versionflag = false;
foreach($fcLine as $line) {
$str_start=0;
$hiidencomment = false;
$printFlag = false;
if (substr(trim($line),0,9) == "#@links={")
{
$linksflag = true;
}
if (substr(trim($line),0,11) == "#@version={")
{
$versionflag = true;
}
if (substr(trim($line),0,2) == "//" )
{
$printFlag = true;
$str_start = 2;
}
if (substr(trim($line),0,3) == "///")
{$hiidencomment = true;}
if (substr(trim($line),0,2) == "/*")
{
$printFlag = true;
$multilineFlag = true;
$str_start = 2;
}
if (substr(trim($line),0,2) == "*/")
{
$printFlag = false;
$multilineFlag = false;
$str_start = 2;
}
if (($multilineFlag == true ||
$printFlag == true) &&
$hiidencomment == false) {
if (substr(trim($line),0,1) == "@" ) {
$atArray = explode("=",trim($line));
array_push($replacements,$atArray);
}
elseif ($linksflag == true) {
$flinks = $flinks . trim(substr($line,$str_start,1000));
}
elseif ($versionflag == true) {
$version = $version . trim(substr($line,$str_start,1000));
}
else {
$out = $out ."<p>". trim(substr($line,$str_start,1000))."</p>";
}
}
if (substr(trim($line),0,8) == "}#@links")
{
$linksflag = false;
}
if (substr(trim($line),0,10) == "}#@version")
{
$versionflag = false;
}
}
foreach($replacements as $replacement) {
$out=str_replace($replacement[0],$replacement[1],$out);
}
echo "<h2 id=\"$fname\"><u>".$fname."</u></h2>";
echo "";
$flinks = tidyflinks($flinks);
$version = tidyversion($version);
$out= str_replace('@#links',$flinks,$out);
$out= str_replace('@#version',$version,$out);
echo $out;
///print_r($replacements);
}
function template() {
$outF =
"
<h2>Details</h2>
<table class=\"table table-striped\">
<tr><th>Author</th><th>Date</th><th>Description</th></tr>
<tr><td>@author</td><td>@date</td><td>@procdesc</td></tr>
</table>
<br>
<h2>Links</h2>
<table class=\"table table-striped\">
<tr><th>Source</th><th>From</th><th>To</th><th>Details</th></tr>
@#links
</table>
<br>
<h2>Version Info</h2>
<table class=\"table table-striped\">
<tr><th>Version</th><th>Date</th><th>By</th><th>Comment</th></tr></tr>
@#version
</table>
<br>";
return $outF;
}
function tidyflinks($flinks) {
$flinks=str_replace('#@links={','<tr><td>', $flinks);
$flinks=str_replace(',','</td><td>', $flinks);
$flinks=str_replace('}#@links','</td></tr>', $flinks);
return $flinks;
}
function tidyversion($version) {
$version=str_replace('#@version={','<tr><td>', $version);
$version=str_replace(',','</td><td>', $version);
$version=str_replace('}#@version','</td></tr>', $version);
return $version;
}
function echoHeader($root)
{
echo "<!doctype html>";
echo "<head>
<!-- Latest compiled and minified CSS -->
<link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css\">
<!-- jQuery library -->
<script src=\"https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js\"></script>
<!-- Latest compiled JavaScript -->
<script src=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js\"></script>
</head>";
echo "<body>
<div style=\"margin-left:5%;margin-right:5%;\">";
echo "<h2>Documentor For <i>$root</i></h2>";
echo "<h3>File List</h3>";
}
?>
/* SAMPLE TEXT FOR DOCUMENTATION*/
<?php
/*
///Hidden Comment
@author=AM
@date=10/03/2018
@procdesc=Set Up SQL Links
/// 4 col version date author desc
#@version={
version 1.0,
10/03/2017,
AM,
Initial File Release
}#@version
#@version={
version 1.1,
10/03/2017,
AM,
Minor Change,
}#@version
/// 4 col LinkTo(file etc) From(y/n) To(y/n) Desc
#@links={
SQL2014,
Y,
Y,
Main Database Reuseable Connection
}#@links
#@links={
Table Test,
Y,
Y,
Main Database Table in SQL2014
}#@links
*/
// <h3>Documented Comment</h3>
// <span style="color:blue;">Comment Detail</span>
// <h3>2nd Documented Comment</h3>
// <span style="color:blue;">2nd Comment Detail</span>
<?php
function loadGlobals() {
$GLOBALS['serverName'] = "Server";
$GLOBALS['UID']="User";
$GLOBALS['PWD']="password";
$GLOBALS['Database']="Database";
}
function getSQLOpen($sql) {
$GLOBALS['connection'] = array( "UID"=>$GLOBALS['UID'], "PWD"=>$GLOBALS['PWD'], "Database"=>$GLOBALS['Database']);
$GLOBALS['conn'] = sqlsrv_connect( $GLOBALS['serverName'] , $GLOBALS['connection']);
///CHECK CONNECTION IS OK
if ($GLOBALS['conn'] === false) {
echo "error!";
die( print_r( sqlsrv_errors(), true));
return false;
}
else
{
$GLOBALS['stmt'] = sqlsrv_query($GLOBALS['conn'], $sql);
$GLOBALS['fld_cnt']=sqlsrv_num_fields($GLOBALS['stmt']);
$GLOBALS['fld_dta']=sqlsrv_field_metadata( $GLOBALS['stmt']);
return true;
}
}
function getSQLClose() {
sqlsrv_free_stmt($GLOBALS['stmt']);
sqlsrv_close($GLOBALS['conn']);
}
loadGlobals();
$ViewName="";
if (isset($_GET['sFilter'])) {
$ViewName=$_GET['sFilter'];
}
echo "<title>".$ViewName."</title>";
$sql="select [definition] from [database].sys.sql_modules where object_id = object_id('".$ViewName."')";
getSQLOpen($sql);
While ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo "<xmp>".$row['definition']."</xmp>";
}
getSQLClose();
?>
<?php
// ### Exremely Insecure !!! ###
$FileName="NoFile";
if (isset($_GET['sFilter'])) {
$FileName=$_GET['sFilter'];
}
echo "<title>".$FileName."</title>";
if (file_exists($FileName)) {
$fc=file_get_contents($FileName);
echo "<xmp>".$fc."</xmp>";
}
?>
<?php
$x=array();
$y=array();
array_push($x,1);
array_push($x,2);
array_push($x,3);
array_push($x,4);
array_push($y,2);
array_push($y,4);
array_push($y,6);
array_push($y,8);
$object = new stdClass();
$object->x = $x;
$object->y = $y;
$object->z = "Some Text";
$json=json_encode($object);
echo $json;
?>
example displays $e on error.
set_error_handler(function() { throw new Exception('SelectNameForYourError');});
try {
$a=1/0;
echo "OK";
}
catch (Exception $e) {
echo $e;
}
restore_error_handler();
<?php
// Import File From Web
$fc=file_get_contents('http://addressToWebFileReturningJsonString');
// Where File is JSON string Convert To Object and Dump Output to Page
$jd=json_decode($fc);
print_r($jd);
// get a single value from object
echo $jd->{"nameOfVariableInObjectReturningA_SingleValue"};
//dump an item in the object
print_r($jd->{"nameOfVariableInObjectReturningAnArray"});
$counter=1;
// get the $counter value from the array in the object
echo $jd->{"nameOfVariableInObjectReturningAnArray"}{$counter};
?>
Create an Array $N by parsing "h,j" where "," is the delimiter.
$N=explode(',','h,j');
NB implode rejoins a row with delimiter of your choice.
use scandir("path") to return an array of filenames
print_r( scandir("C:\\temp\\") );
Use of foreach to loop an array.
$dir=scandir("C:\\temp\\");
foreach ($dir as $str) {echo $str . "\n";}
<?php
// Include <?xml version='1.0' encoding='UTF-8'?> straight after [ " ]
//********************************************************************
$myXMLData =
"<?xml version='1.0' encoding='UTF-8'?>
<notes>
<note>
<to>To You 01 </to>
<from> From 01 </from>
<heading> Title 01 </heading>
<body> Body 01 </body>
</note>
<note>
<to> To You 02 </to>
<other>Other 2</other>
</note>
</notes>";
// Best to use or die
//*******************
$xml=simplexml_load_string($myXMLData) ; //or die("Error: Cannot create object");
// Ref by obj->name[index no]->valuedItemName
//********************************************
echo "<BR>";
echo $xml->note[0]->from;
echo "<BR>";
echo $xml->note[0]->to;
echo "<BR>";
echo $xml->note[1]->to;
echo "<BR>";
// Use foreach to loop all
//*************************
foreach ($xml->note as $note) {
echo $note->to . "<BR>";
echo $note->from . "<BR>";
echo $note->other . "<BR>";
}
?>
Use shell_exec to run a shell command. exec is similar but only returns the last line of any output. Example to put the contents of textFile.txt into variable $x
$x = shell_exec("TYPE C:\\folder\\textFile.txt");
*** NB!!! review $regex and preferably change SQL Actions to parameterized ***
Use $_SESSION['UID']
$_SESSION['rst']
$_SESSION['utype']
To manage access
<form method="post" action="LogInCheck.php">
<label>User ID:</label>
<input type="text" autocomplete="on" name="nmUID" id="uid">
<label>Password:</label>
<input type="password" autocomplete="on" name="nmPwd" id="pwd">
<button type="submit">Login</button>
</form>
session_start();
require_once 'header.php'; // Some Header with Links for Use When you have access
require_once 'Globals.php'; // See Below
function loadFailPage($message) {
echo "<div>".$message."<a href=\"Login.php\"> Click here to Retry!</a></div>";
}
function loadSuccessPage() {
echo
"<div>WELCOME ". $_SESSION['UID']."</div>";
}
if ( isset( $_POST[ 'nmPwd' ] ) && isset( $_POST[ 'nmUID' ])) {
$nmPwd = trim( $_POST[ 'nmPwd' ] );
$nmUID = trim( $_POST[ 'nmUID' ] );
$regex = "[(\"+)|(\'+)|(\.+)|(\[+)|(\]+)|(\*)|(\%+)|";
$regex .= "(\_+)|(\n+)|(\r+)|(\t+)|(\\\+)|(,+)|(;+)|(`+)]";
$nmUID = preg_replace($regex, "", $nmUID);
$nmPwd = preg_replace($regex, "", $nmPwd );
/// Get Hashed Value of Password Inputed By User
$hashed = hash( "sha256" , $nmPwd, $raw_output = null );
/// Add Retries Where UID Correct and 15 mins past
sqlAction( "UPDATE ".$GLOBALS['Database'].".dbo.users " .
"SET [retriesLeft] = 5 " .
"WHERE ( releaseTime < CURRENT_TIMESTAMP OR releaseTime is Null ) and " .
"[retriesLeft] = 0 and uid='".$nmUID."'" );
/// Look For UID/PwdHash Combo
if ( getSQLOpen( "SELECT uid, utype, resetNeeded, retriesLeft " .
"FROM " . $GLOBALS['Database'] . ".dbo.users " .
"WHERE uid ='" . $nmUID . "' and pwd='" . $hashed . "'")) {
if ( sqlsrv_has_rows( $stmt ) ) {
/// Rows Returned i.e. UID/Password Found
while ( $row = sqlsrv_fetch_array( $stmt , SQLSRV_FETCH_ASSOC ) ) {
///Confirm Some Retries Left
if ($row[ 'retriesLeft' ] >= 1) {
///Set Retries to 5
sqlAction( "UPDATE " . $GLOBALS['Database'] . ".dbo.users SET retriesLeft = 5 WHERE uid = '".$nmUID."'");
///Set Session Values
$_SESSION['UID'] = $nmUID;
$_SESSION['rst'] = $row['resetNeeded'];
$_SESSION['utype'] = $row['utype'];
loadSuccessPage();
} else {
sqlAction( "UPDATE ".$GLOBALS['Database'].".dbo.users SET [retriesLeft] = 0 WHERE uid='".$nmUID."'" );
loadFailPage( "no retries left please wait 15 mins" );
}
}
getSQLClose();
} else {
/// No Rows Returned matching UID and PWD
$rCounter = 0;
$message = "";
/// As login failed check retries left - returns no rows if UID not on file else retry count
if ( getSQLOpen("SELECT retriesLeft FROM " . $GLOBALS['Database'] . ".dbo.users where uid = '" . $nmUID . "'" ) ) {
/// Reduce retries as login failed where UID exists
while ( $row = sqlsrv_fetch_array( $stmt , SQLSRV_FETCH_ASSOC ) ) {
/// Reduce retries
sqlAction( "UPDATE ".$GLOBALS['Database'].".dbo.users SET retriesLeft = retriesLeft - 1 WHERE uid='".$nmUID."'" );
/// keep at zero if would be < 0
if ( $row[ 'retriesLeft' ] <= 1 ) {
/// Force zero
sqlAction("UPDATE " . $GLOBALS[ 'Database' ] . ".dbo.users SET retriesLeft = 0 WHERE uid='".$nmUID."'");
/// Set Lockout Time NB 0.0104167 = 15 minutes as fraction of a day
sqlAction("UPDATE " . $GLOBALS[ 'Database' ] . ".dbo.users " .
"SET [releaseTime] = CURRENT_TIMESTAMP + 0.0104167 " .
"WHERE ([releaseTime] is null or [releaseTime] < CURRENT_TIMESTAMP) and uid='".$nmUID."'");
$message=" Locked Out For 15 minutes";
}
loadFailPage( "Password Error Attempts Left " . strval( $row[ 'retriesLeft' ] - 1 ).$message );
$rCounter = $rCounter + 1;
}
if ( $rCounter == 0 ) { loadFailPage( "Password/UID Error" ); }
getSQLClose();
}
}
} else {
///Query Failed
loadFailPage( "Error" );
}
} else {
loadFailPage( "UID or Password Missing!" );
}
loadGlobals();
function loadGlobals() {
$GLOBALS['serverName'] = '';
$GLOBALS['UID']=" ";
$GLOBALS['PWD']=" ";
$GLOBALS['Database']=" ";
}
function getSQLOpen($sql) {
$GLOBALS['connection'] = array( "UID"=>$GLOBALS['UID'], "PWD"=>$GLOBALS['PWD'], "Database"=>$GLOBALS['Database']);
$GLOBALS['conn'] = sqlsrv_connect( $GLOBALS['serverName'] , $GLOBALS['connection']);
///CHECK CONNECTION IS OK
if ($GLOBALS['conn'] === false) {
echo "error!";
die( print_r( sqlsrv_errors(), true));
return false;
}
else
{
$GLOBALS['stmt'] = sqlsrv_query($GLOBALS['conn'], $sql);
$GLOBALS['fld_cnt']=sqlsrv_num_fields($GLOBALS['stmt']);
$GLOBALS['fld_dta']=sqlsrv_field_metadata( $GLOBALS['stmt']);
return true;
}
}
function getSQLClose() {
sqlsrv_free_stmt($GLOBALS['stmt']);
sqlsrv_close($GLOBALS['conn']);
}
function SQLaction($sql) {
$GLOBALS['connection'] = array( "UID"=>$GLOBALS['UID'], "PWD"=>$GLOBALS['PWD'], "Database"=>$GLOBALS['Database']);
$GLOBALS['conn'] = sqlsrv_connect( $GLOBALS['serverName'] , $GLOBALS['connection']);
///CHECK CONNECTION IS OK
if ($GLOBALS['conn'] === false) {
echo "error!";
die( print_r( sqlsrv_errors(), true));
return false;
}
else
{
$GLOBALS['stmt'] = sqlsrv_query($GLOBALS['conn'], $sql);
return true;
}
sqlsrv_free_stmt($GLOBALS['stmt']);
sqlsrv_close($GLOBALS['conn']);
}
CREATE TABLE [dbo].[users](
[uid] [nchar](50) NULL,
[utype] [nchar](50) NULL,
[resetNeeded] [nchar](50) NULL,
[retriesLeft] [int] NULL,
[pwd] [nchar](100) NULL,
[releaseTime] [datetime] NULL
)
<?php
/* PhpSpreadsheet downloaded from github to "C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\
Psr\SimpleCache; downloaded from github to "C:\\inetpub\\wwwroot\\frameworks\\simple-cache-master\\ */
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Psr\SimpleCache;
require_once ("C:\\inetpub\\wwwroot\\frameworks\\simple-cache-master\\src\\CacheInterface.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\ReferenceHelper.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Calculation.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Category.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Engine\\CyclicReferenceStack.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Engine\\Logger.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Functions.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Token\\Stack.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\MathTrig.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Statistical.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\IComparable.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Shared\\StringHelper.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Shared\\XmlWriter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Shared\\Date.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\Worksheet.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Spreadsheet.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\HashTable.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\WorkSheet\\Iterator.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\IWriter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\BaseWriter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\WriterPart.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\StringTable.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\ContentTypes.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\DocProps.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Rels.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Theme.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Style.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Workbook.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Worksheet.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Drawing.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Comments.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Chart.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\RelsVBA.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\RelsRibbon.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\Hyperlink.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Collection\\CellsFactory.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Collection\\Cells.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Settings.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Collection\\Memory.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\PageSetup.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\PageMargins.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\HeaderFooter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\SheetView.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\Protection.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\Dimension.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\RowDimension.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\ColumnDimension.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\AutoFilter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Document\\Properties.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Document\\Security.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Supervisor.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Style.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Font.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Color.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Fill.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Borders.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Border.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Alignment.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\NumberFormat.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Protection.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\Coordinate.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\Cell.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\DataType.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\IValueBinder.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\DefaultValueBinder.php");
/* Create New Sheet */
$spreadsheet = new Spreadsheet();
/* Get Active Sheet */
$sheet = $spreadsheet->getActiveSheet();
/* Set Sheet Title */
$sheet->setTitle('AutoSheet');
/* Set Row Col Default Sizes */
$sheet->getDefaultColumnDimension()->setWidth(25);
$sheet->getDefaultRowDimension()->setRowHeight(25);
/* Set Zoom */
$sheet->getSheetView()->setZoomScale(75);
/* Set Tab Color */
$sheet->getTabColor()->setRGB('FFFF00');
/* Create New Sheet */
$sheet2 = $spreadsheet->createSheet();
/* Set Sheet Title */
$sheet2->setTitle('Another Sheet');
// Option to access sheet by Name.....
//$spreadsheet->getSheetByName('Autosheet');
// Access sheet by Name and Set Value of Cell
$spreadsheet->getSheetByName('Another Sheet')->setCellValue('A1', 'AS Data');
/* // Hide Sheet
$spreadsheet->getActiveSheet()
->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN);
*/
/* Set Global Font */
$sheet->getParent()->getDefaultStyle()->getFont()->setName('Times New Roman');
/* Set Cell Font Size */
$sheet->getStyle("A1")->getFont()->setSize(16);
/* Creat eand Format Hyperlink */
$sheet->setCellValue('A2', 'PhpSpreadsheet Help');
$sheet->getCell('A2')
->getHyperlink()
->setUrl('http://https://phpspreadsheet.readthedocs.io/en/latest/')
->setTooltip('Help');
$sheet->getStyle("A2")->getFont()->setColor( new \PhpOffice\PhpSpreadsheet\Style\Color( \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE ) );
/* Add Values to Some Cells */
$sheet->setCellValue('A1', 'Hello World !');
$sheet->setCellValue('D1', 10);
$sheet->setCellValue('D2', 20);
$sheet->setCellValue('D3', '=SUM(D1:D2)');
$sheet->setCellValue('D4', '=AVERAGE(D1:D2)');
$sheet->setCellValue('D5', 'Yahooooo !');
/* Create Style Array For Borders */
$styleArray = [
'borders' => [
'outline' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
'color' => ['argb' => 'FFFF0000'],
],
],
];
/* Apply Style */
$sheet->getStyle('D1:D2')->applyFromArray($styleArray);
/* Modify Style Array */
$styleArray['borders']['outline']['color']=['argb' => '00000000'];
/* Apply Modified Style Array */
$sheet->getStyle('A1')->applyFromArray($styleArray);
/* Aplly Top/Bottom Borders to Cell */
$sheet->getStyle('D4')
->getBorders()->getBottom()
->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$sheet->getStyle('D4')
->getBorders()->getTop()
->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
/* Select Active Sheet Name Or Index */
$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->setActiveSheetIndexByName('Another Sheet');
/* Create And Save with Name ... xlsx */
$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');
?>
<?php
/* PhpSpreadsheet downloaded from github to "C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\
Psr\SimpleCache; downloaded from github to "C:\\inetpub\\wwwroot\\frameworks\\simple-cache-master\\ */
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use Psr\SimpleCache;
require_once ("C:\\inetpub\\wwwroot\\frameworks\\simple-cache-master\\src\\CacheInterface.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\ReferenceHelper.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\IOFactory.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Calculation.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Category.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Engine\\CyclicReferenceStack.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Engine\\Logger.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Functions.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Token\\Stack.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\MathTrig.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Calculation\\Statistical.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\IComparable.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Shared\\StringHelper.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Shared\\XmlWriter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Shared\\Date.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Shared\\File.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\Worksheet.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Spreadsheet.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\HashTable.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\WorkSheet\\Iterator.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\IReader.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\BaseReader.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\Theme.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\Properties.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\BaseParserClass.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\Styles.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\SheetViews.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\SheetViewOptions.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\ColumnAndRowAttributes.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\PageSetup.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Xlsx\\Hyperlinks.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\IReadFilter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\DefaultReadFilter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Security\\XmlScanner.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Reader\\Security\\XmlScanner.php");
/*
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\IWriter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\BaseWriter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\WriterPart.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\StringTable.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\ContentTypes.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\DocProps.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Rels.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Theme.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Style.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Workbook.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Worksheet.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Drawing.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Comments.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\Chart.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\RelsVBA.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Writer\\Xlsx\\RelsRibbon.php");
*/
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\Hyperlink.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Collection\\CellsFactory.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Collection\\Cells.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Settings.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Collection\\Memory.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\PageSetup.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\PageMargins.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\HeaderFooter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\SheetView.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\Protection.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\Dimension.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\RowDimension.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\ColumnDimension.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Worksheet\\AutoFilter.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Document\\Properties.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Document\\Security.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Supervisor.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Style.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Font.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Color.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Fill.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Borders.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Border.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Alignment.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\NumberFormat.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Style\\Protection.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\Coordinate.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\Cell.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\DataType.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\IValueBinder.php");
require_once ("C:\\inetpub\\wwwroot\\frameworks\\PhpSpreadsheet-master\\src\\PhpSpreadsheet\\Cell\\DefaultValueBinder.php");
$inputFile = "Hello World.xlsx";
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFile);
$spreadsheet->setActiveSheetIndexByName('AutoSheet');
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
// print_r($sheetData);
?>
<style>
td {
border: solid;
width: 160px;
}
.cB {
background-color: #ccc;
text-align: center;
}
</style>
<h3>Data From HW.xlsx</h3>
<table>
<tr>
<td class="cB"></td>
<td class="cB">A</td>
<td class="cB">B</td>
<td class="cB">C</td>
<td class="cB">D</td>
</tr>
<tr>
<td class="cB">1</td>
<td><?=$sheetData[1]['A']?></td>
<td><?=$sheetData[1]['B']?></td>
<td><?=$sheetData[1]['C']?></td>
<td><?=$sheetData[1]['D']?></td>
</tr>
<tr>
<td class="cB">2</td>
<td><?=$sheetData[2]['A']?></td>
<td><?=$sheetData[2]['B']?></td>
<td><?=$sheetData[2]['C']?></td>
<td><?=$sheetData[2]['D']?></td>
</tr>
<tr>
<td class="cB">3</td>
<td><?=$sheetData[3]['A']?></td>
<td><?=$sheetData[3]['B']?></td>
<td><?=$sheetData[3]['C']?></td>
<td><?=$sheetData[3]['D']?></td>
</tr>
<tr>
<td class="cB">4</td>
<td><?=$sheetData[4]['A']?></td>
<td><?=$sheetData[4]['B']?></td>
<td><?=$sheetData[4]['C']?></td>
<td><?=$sheetData[4]['D']?></td>
</tr>
<tr>
<td class="cB">5</td>
<td><?=$sheetData[5]['A']?></td>
<td><?=$sheetData[5]['B']?></td>
<td><?=$sheetData[5]['C']?></td>
<td><?=$sheetData[5]['D']?></td>
</tr>
</table>
Use define
<?php
define("MY_CONST","Yum");
ECHO MY_CONST;
?>
Some Constants fro phpfiddle
PHP_BINDIR /opt/alt/php73/usr/bin
PHP_CONFIG_FILE_PATH /opt/alt/php73/etc
PHP_CONFIG_FILE_SCAN_DIR /opt/alt/php73/link/conf
PHP_DEBUG 0
PHP_EOL
PHP_EXTENSION_DIR /opt/alt/php73/usr/lib64/php/modules
PHP_EXTRA_VERSION
PHP_LIBDIR /opt/alt/php73/usr/lib64
PHP_MAJOR_VERSION 7
PHP_OS Linux
PHP_PREFIX /opt/alt/php73
PHP_SESSION_ACTIVE 2
PHP_SHLIB_SUFFIX so
PHP_SYSCONFDIR /opt/alt/php73/etc
PHP_URL_PATH 5
PHP_URL_QUERY 6
PHP_URL_USER 3
PHP_VERSION 7.3.17
PHP_VERSION_ID 70317
php_ini_loaded_file() /opt/alt/php73/etc/php.ini
php_ini_scanned_files() /opt/alt/php73/link/conf/alt_php.ini
php_sapi_name() litespeed
php_uname() Linux sv26.byethost26.org 3.10.0-962.3.2.lve1.5.32.el7.x86_64 #1 SMP Fri Feb 28 07:18:51 EST 2020 x86_64
phpversion() 7.3.17
show full length vars when using xdebug by adding this to the top of a script
ini_set("xdebug.var_display_max_children", -1);
ini_set("xdebug.var_display_max_data", -1);
ini_set("xdebug.var_display_max_depth", -1);
sqlsrv_query can return incorrect results when multiple steps are taken in a query or cursor. Use "SET Nocount ON" before each statement/section
<?php
$z = array();
$x = new stdClass();
for ($i=0; $i < 10 ; $i ++) {
$x->name="name" . $i ;
$x->stackgroup="stack";
$x->type ="scatter";
$x->x =array(1,2,3,4);
$x->y =array(1,2,$i,4);
$x->fill= "tonexty";
$x->showlegend=false;
array_push($z,$x);
}
echo json_encode($z);
]";
?>
outputs [{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false},{"name":"name9","stackgroup":"stack","type":"scatter","x":[1,2,3,4],"y":[1,2,9,4],"fill":"tonexty","showlegend":false}]
Suitable for use ad data in Plotly.newPlot(element,data,layout,{displaylogo: false}) or Plotly.reDraw(element,data,layout,{displaylogo: false})
<?php
namespace FRED\WILMA\BAMBAM { // Create Namespace and add const & function
const FW = 2;
function fw() {
echo "<br>fw<br>";
}
}
namespace BARNEY\BETTY { // Create Namespace and add function
function bb() {
echo "<br>bb<br>";
}
}
namespace { // use global namespace
FRED\WILMA\BAMBAM\fw(); // call namespace function
echo FRED\WILMA\BAMBAM\FW;
BARNEY\BETTY\bb();
}
namespace { // use global namespace with use ... as
use FRED\WILMA\BAMBAM AS FWB;
use BARNEY\BETTY AS BB; //add alias
BB\bb(); // use alias
FWB\fw();
}
namespace { // use global namespace with (last path element only)
use FRED\WILMA\BAMBAM;
use BARNEY\BETTY;
BETTY\bb();
BAMBAM\fw();
}
namespace FRED\WILMA\BAMBAM { // use given namespace and code from external namespace
use BARNEY\BETTY;
BETTY\bb();
fw();
}
// echo "THIS IS FATAL ALL CODE MUST BE IN A NAMESPACE";
?>
Create a procedure
USE [<DBNAME>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: AUTOMATE
-- Create date: NA
-- Description: DEMO
-- =============================================
CREATE PROCEDURE [dbo].[TEST]
@PARAM1 varchar(max),
@PARAM2 varchar(max)
AS
BEGIN
SET NOCOUNT ON;
----------------------------------------------------------------------------------------------------------------
-- NB add Execute permission to Web Account for any Procedure Required
-- Execute in SQL with >> EXEC TEST '<VALUE_FOR_PARAM1>', '<VALUE_FOR_PARAM2>'
SELECT * FROM [<SOME_TABLE>] WHERE [<SOME_FIELD>] = @PARAM1 AND [<SOME_FIELD>] like '%' + @PARAM2 + '%'
----------------------------------------------------------------------------------------------------------------
END
GO
Add php function to open the procedure
function getParamsSQLOpen( $sql, $params ) {
$GLOBALS[ 'connection' ] = array( "UID" => $GLOBALS[ 'UID' ],
"PWD" => $GLOBALS[ 'PWD' ],
"Database" => $GLOBALS[ 'Database' ] );
$GLOBALS[ 'conn' ] = sqlsrv_connect( $GLOBALS[ 'serverName' ] ,
$GLOBALS[ 'connection' ]);
/// CHECK CONNECTION IS OK
if ( $GLOBALS[ 'conn' ] === false ) {
echo "error!";
die( print_r( sqlsrv_errors(), true ) );
return false;
}
else
{
$GLOBALS[ 'stmt' ] = sqlsrv_query( $GLOBALS['conn'], $sql, $params );
$GLOBALS[ 'fld_cnt' ] = sqlsrv_num_fields( $GLOBALS['stmt'] );
$GLOBALS[ 'fld_dta' ] = sqlsrv_field_metadata( $GLOBALS['stmt'] );
return true;
}
}
Add php function to close SQL con
function getSQLClose() {
if ($GLOBALS[ 'stmt' ]) {
sqlsrv_free_stmt( $GLOBALS[ 'stmt' ] );
}
if ($GLOBALS[ 'conn' ]) {
sqlsrv_close( $GLOBALS[ 'conn' ] );
}
}
Execute in php
$GLOBALS['serverName'] = "<SOME_SERVER>";
$GLOBALS['UID'] = "<SOME_USER>";
$GLOBALS['PWD'] = "<SOME_PASSWORD>";
$GLOBALS['Database'] = "<SOME_DATABASE_NAME>";
getParamsSQLOpen( "EXEC [TEST] ?, ?", ['<VALUE_FOR_@PARAM1>', '<VALUE_FOR_@PARAM2>'] );
// For Non Proceedure with parameters use...
// getParamsSQLOpen( "SELECT * FROM [<SOME_TABLE>] WHERE [<SOME_FIELD>] = ? AND [<SOME_FIELD>] like '%' + ? + '%'",['<VALUE_FOR_@PARAM1>', '<VALUE_FOR_@PARAM2>'] );
while ( $row = sqlsrv_fetch_array( $GLOBALS['stmt'], SQLSRV_FETCH_NUMERIC )) {
echo $row[0] . "," . $row[1] . "," . $row[2] . "<br>"; // OR WHATEVER
}
getSQLClose();
Ensure LDAP is added in php.ini and server restarted.
Check a login:
function ldap_auth( $username, $password ) {
// $username = NT username without domain
// $password = NT password for given NT username
$adAddress = "ldap://<LDAP_SERVER_ADDRESS>.com";
$ldapConn = ldap_connect( $adAddress );
$ldapRDN = '<SERVER_NAME>' . "\\" . $username;
ldap_set_option( $ldapConn, LDAP_OPT_PROTOCOL_VERSION, 3 );
ldap_set_option( $ldapConn, LDAP_OPT_REFERRALS, 0 );
$ldapBind = @ldap_bind( $ldapConn, $ldapRDN, $password );
if ( $ldapBind ) {
$ldapFilter = "(sAMAccountName=$username)";
$ldapSearchResult = ldap_search( $ldapConn, "dc=<SERVER_NAME>,dc=COM", $ldapFilter );
$ldapEntries = ldap_get_entries( $ldapConn, $ldapSearchResult );
if ( $ldapEntries["count"] == 1 ) {
@ldap_close( $ldapConn );
return true;
} else {
@ldap_close( $ldapConn );
return false;
}
}
}
check a username is valid:
<?php
/* S E T T I N G S */
// Create a tree area to search
$ldaptree = "OU=<YOUR_CRITERIA>,OU=<YOUR_EXTRA_CRITERIA>,OU=<ECT>,DC=<YOUR_DOMAIN exclude .com etc>,DC=<COM or other per your domain>";
// User Login to Search For
$sAMAccountName = "<LOGIN_TO_SEARCH_FOR>";
// Credentials
$LDAP_Address = "LDAP://<YOURDOMAIN>.com";
$domainSlashUser = "<YOUR_DOMAIN>\<USER_ID>";
$passwordFordomainSlashUser = "<YOUR_PASSWORD FOR <YOUR_DOMAIN>\<USER_ID>";
/* C O N N E C T S E T U P P R O T O C O L A N D B I N D */
// Get A Connection Object with the Address
$ldapconn = ldap_connect( $LDAP_Address );
// Set Protocol to Version 3
ldap_set_option( $ldapconn, LDAP_OPT_PROTOCOL_VERSION, 3 );
// Bind with current users login
$ldapbind = ldap_bind( $ldapconn, $domainSlashUser, $passwordFordomainSlashUser);
/* A D D F I L T E R A N D R U N */
// Set A Filter
$ldapFilter = "(sAMAccountName=".$sAMAccountName.")";
// Run the Search
$result = ldap_search( $ldapconn, $ldaptree, $ldapFilter ) or die ("Oh No!");
/* L O A D R E S U L T S T O A R R A Y A N D O U T P U T l o g i n n a m e s e a r c h e d f o r o r " n o t f o u n d " */
// Echo the result
$data = ldap_get_entries( $ldapconn, $result );
if ( count( $data ) == 1 ) {
echo "not Found";
} else {
echo $data[0]["samaccountname"][0];
}
class TestClass {
const BEANS = 100;
public function hello() {
echo "Hello BARRYBEANS\n";
}
public static function oy() {
echo "OYOY!\n";
}
}
// ACCESS Static Functions And Constants ::
TestClass::oy();
echo TestClass::BEANS;
// CREATE CLASS INSTANCE (new)
$classInstance = new TestClass();
// ACCESS Functions -> And Constants :: FROM INSTANCE
$classInstance->hello();
echo $classInstance::BEANS;
/*
An alternative to
require_once "a.php";
require_once "b.php";
require_once "c.php";
require_once "d.php";
" " etc
By using spl_autoload_register, when a class is requested and has not already been required e.g in
$TestClassInstance = new TestClass;
the spl_autoload_registered function will automatically be called
we have told it here that when this occurs we want it to run the anonymous function
function($class) {
require_once($class.".php");
}
"TestClass" the class name will be provided as variable $class to the anonymous function therefore
the function code will become
require_once("TestClass.php");
So as long as TestCLass is in TestClass.php and in the path it will be loaded as required.
We can then carry on and use the instantiated class object as normal e'g. calling some function in the object.
$TestClassInstance->someFunctionInTestClass();
*/
// NB function does not have to be autonomous you can write a function separately and add its the name in the ()
spl_autoload_register(function($class) {
require_once($class.".php");
});
$TestClassInstance = new TestClass; // If not loaded the function in spl_autoload_register will be run first loading the class file
$TestClassInstance->someFunctionInTestClass();
Client Page
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>SSE Example</title>
</head>
<body>
<div id="sseOut">
</div>
</body>
<script>
var sseSource = new EventSource("sse.php");
sseSource.onmessage = function(event) {
document.getElementById("sseOut").innerHTML = event.data;
};
</script>
</html>
Server Page sse.php
<?php
// Server Send Every 10 sec
header('Content-Type: text/event-stream');
header('Cache-Control: no-cache');
$time = date('G:i');
echo "retry: 10000\n"; // default is 3 seconds - here its 10
echo "data: (any message between data: and ending with \\n\\n) "
."e.g. Current Time :"
.$time.
"\n\n";
flush(); // force output
?>
ini_set('max_execution_time', '300'); // 300 seconds = 5 minutes
ini_set('max_execution_time', '0'); // for infinite time of execution
To debug use
xdebug_break(); and run listen
OR
run listen then add as GET
? or & XDEBUG_SESSION_START= (remember to add break point)
and use
? or & XDEBUG_SESSION_STOP to end (remember to stop listener)