SQL Coding Standards

A Sample of My Coding Functions, for Quick Reference.Updated 1 year ago

Coding and Naming Standards:

On every project I've worked on, people have discussed coding & Naming Standards, but we always eventually realize that nobody has written these things down. So I have started these proposed standards:

Names: like_this
ID: Must be (INT) Named like: table_id

Coding Analogies

Variable- Cooking ingredient
Switch - Train switching station. or a choose your own adventure
If - For in the road
Break - Exit out of current Loop
Exit (or Die)- Quit IMMEDIATELY, and print something to the screen

Regex GEdit:

Delete every other line.

([^\n]*\n)[^\n]*\n

Regex tester for Search AND Replace:

RegExe.com

Make all Links open in a New Window jquery.

SQL Queries

Count all Table Rows

SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = "adamwith_main";

Hwo to assign an array to two variables

I always forget :P
list($first, $second)=array(1,2);

Geo IP Location:

You are in: Columbus, United States

My Commonly Used Functions:

addorremovebitmapperm

cleanfordatabasefield

cleanforshorturl

cleanfortitle

cleanupmultifilearray

createslideshowquickthumbnaildirectories

findpicfromanyextension

getcollageandimagemap

getcollageimg

getcurrencyrates

getcurrentquerystring

getimagefromandsaveto

getscriptnamewithoutparams

getthumbnailoffilesin

imagecolorallocatebyname

imagettfstroketext

in_arrayresursive

incrementfilenameifexists

isprivatecookieloggedin

returnshortenedstring

subtractyearfrommovie

View Function: makecollage

';
if (isset($arr['title']) ){
$toReturn.=''.$arr['title'].'
'."\n";
}
if (isset($arr['description']) ){
$toReturn.=''.$arr['description'].'
'."\n";
}
if (! is_array($arr) ){
return'

Error: Not an Array.

';
}
if(isset($_GET['answers'])&&$_GET['answers']=='true'){
$arr['picTitles']='filename';
//echo 'Showing Answers: '.$_GET['answers'].'
';
}
if(isset($arr['colsOrWidth']) && $arr['colsOrWidth']=='original'){
$fontSize=isset($arr['fontSize'])?$arr['fontSize']:0;
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&($arr['colsOrWidth']>13)&&isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){//The provided both colsOrWidth and fontSize
$eachWidthofPic=$arr['colsOrWidth'];
if($arr['colsOrWidth']>200){
$fontSize=19;
}else{
$fontSize=14;
}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==13){$eachWidthofPic=60;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=6;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==12){$eachWidthofPic=65;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=6;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==11){$eachWidthofPic=78;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=6;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==10){$eachWidthofPic=90;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=7;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==9){$eachWidthofPic=1000;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=8;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==8){$eachWidthofPic=110;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=9;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==7){$eachWidthofPic=120;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=10;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==6){$eachWidthofPic=133;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=11;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==5){$eachWidthofPic=160;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=12;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==4){$eachWidthofPic=210;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=14;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==3){$eachWidthofPic=280;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=17;}
}else if(isset($arr['colsOrWidth'])&&is_numeric($arr['colsOrWidth'])&&$arr['colsOrWidth']==2){$eachWidthofPic=426;if(isset($arr['fontSize'])&&is_numeric($arr['fontSize']) ){$fontSize=$arr['fontSize'];}else{$fontSize=18;}

}else{$eachWidthofPic=390;$fontSize=18;}//426 x 213

$arrNoImage=array();//Images in the Array, that we can't find in the Directory
$newDeaths=array();//if checkNewDeaths we will write these.

if (isset($arr['random'])&&$arr['random']===true ){
$arr=shuffleAssoc($arr);
}
$i=1;
$iRow=0;
$debugPrintOut='

';
// $debugPrintOut.='makeCollage using :: eachPicWidth# : '.$eachWidthofPic.'
FontSize: '.$fontSize.'
'.prettyArrayReturn($arr) );
foreach ($arr as $eachPic=>$eachHeading){
$deathDate=false;
if(in_array($eachPic,$GLOBALS['collageParamsToIgnore'] ) ){
continue;
$debugPrintOut.='Ignoring: '.$eachPic.'
';
}
$divStyle='';
if($eachHeading=='NEWLINE'){
if(isset($arr['otherDirectory']) ){
$divStyle='height:604px;';
}else{
$divStyle='height:354px;';
}
$toReturn.='

'."\n";
$debugPrintOut.='';
$iRow=0;
continue;
}
if(strpos($eachPic,'*')!==false){//The person has died
[$deathDate, $eachPic] =explode('*',$eachPic);
}
if(isset($arr['directory'])&&file_exists($_SERVER['DOCUMENT_ROOT'].$arr['directory'].$eachPic.'.jpg')){
//die( 'a' ) ;
}
if(isset($arr['otherDirectory'])&& strpos($arr['otherDirectory'],'movies')!==false ){
$debugPrintOut.='We have a second directory, that contains Movie files.
';
$potentialPicFile=$arr['directory'].$eachPic.'.jpg';
$potentialOtherFile=$arr['otherDirectory'].$eachPic.'['.$eachHeading.'].jpg';
}else if(is_string($eachHeading)&&isset($arr['otherDirectory']) ) {
$debugPrintOut.='We have a second directory, that is NOT a movie file.
';
$potentialPicFile=$arr['directory'].$eachPic.'.jpg';
$potentialOtherFile=$arr['otherDirectory'].$eachHeading.'.jpg';
}else if(isset($arr['directory'])&&strpos($arr['directory'],'movies')!==false&&($eachHeading!=='****') ){$potentialPicFile=$arr['directory'].$eachPic.'['.$eachHeading.'].jpg';
}else if(isset($arr['directory'])&&getFileExtension($eachPic)!=''&& file_exists($_SERVER['DOCUMENT_ROOT'].$arr['directory'].$eachPic)){$potentialPicFile=$arr['directory'].$eachPic;
}else if(isset($arr['directory'])&&file_exists($_SERVER['DOCUMENT_ROOT'].$arr['directory'].$eachPic.'.jpg')){$potentialPicFile=$arr['directory'].$eachPic.'.jpg';
}else if(isset($arr['directory'])&&file_exists($_SERVER['DOCUMENT_ROOT'].$arr['directory'].$eachPic.'.gif')){$potentialPicFile=$arr['directory'].$eachPic.'.gif';
}else if(isset($arr['directory'])&&file_exists($_SERVER['DOCUMENT_ROOT'].$arr['directory'].$eachPic.'.png')){$potentialPicFile=$arr['directory'].$eachPic.'.png';
}else if(isset($arr['directory'])&&isset($arr['lookup']) && $arr['lookup']===true){$potentialPicFile=$arr['directory'].$eachPic.'.jpg';
}else{
//die( '$arr:'.$selectedCollage.'=.='.var_export($arr,true) );
$toReturn= 'Error: Unable to find Collage ('.$arr['title'].'), and lookup==false in: '.$eachPic.' in '.getcwd().'
'."\n";
return ('Unable to find Collage ('.$arr['title'].')'.$toReturn.'
');
echo debugFileNotExist($filePathAndName=$arr['directory'].$eachPic);
continue;
}

if(isset($arr['colsOrWidth']) && $arr['colsOrWidth']=='original'){
if(file_exists('.'.$potentialPicFile) ){
[$orignalWidth,$originalHeight]=getimagesize('.'.$potentialPicFile);
}else{$orignalWidth=40;$originalHeight=40;}
$eachWidthofPic=$orignalWidth;
$debugPrintOut.='Setting $orignalWidth-: '.$orignalWidth.'
';
}else if(isset($arr['lockHeight'])&&$arr['lockHeight']===true ){
[$orignalWidth,$originalHeight]=getimagesize($potentialPicFile);
$eachWidthofPic=$orignalWidth;
$debugPrintOut.='Setting $originalHeight-: '.$orignalWidth.' x '.$originalHeight.'
';
}

$debugPrintOut.=''.
''.
''.
''.
'';
$toReturn.='

';//."\n"
if(file_exists('.'.$potentialPicFile) ){
$toReturn.='';
$toReturn.='

';//."\n"
if(isset($arr['otherDirectory']) ){
$toReturn.='';
$toReturn.='

';//."\n"

}
}else if(substr($eachPic,0,1)=='?') {
$eachPic=str_replace('?','',$eachPic);
$toReturn.='Unknown:
'.$eachPic.'
';//."\n"
}else if(isset($arr['lookup']) && $arr['lookup']===true){
if(strpos($arr['directory'],'movies')!==false){
$site='imdb';
$params=array('name'=> $eachPic,'year'=> $eachHeading,'picSize'=>'900');
}else{
$site='wikipedia';
$params=array('name'=> $eachPic,'picSize'=>'900');
}
//var_dump($params);die('asdasd');
if(getImageFromAndSaveTo($params,$saveTo=$potentialPicFile,$site) ){
$toReturn.='';
$toReturn.='

';
}else{
$debugPrintOut.='Unable to get '.$site.' file for '.$eachPic.'['.$eachHeading.']
';
$toReturn.='';
$toReturn.='

';
$arrNoImage[]=$eachPic;
}
}

//echo $debugPrintOut;die('aaa');
//prettyArray($arr);die('makeCollage::');
//echo $arr['selectedCollage'].'
';
//echo $eachPic.' ==>>> '.$eachHeading.' --==-- '.$arr['picTitles'].'
'."\n";
//echo 'eachPic: '.var_export($eachPic,true).'
';
//echo 'Check'.var_export(substr($eachPic,2,1)=='-').'
';
if(isset($arr['removeFromTitle']) && $arr['removeFromTitle']!==''){
$eachPic=str_replace($arr['removeFromTitle'], '',removeFileExtension($eachPic));
}

if(isset($arr['calcDate']) && $arr['calcDate']===true){
$mode='AA';
if(strlen($eachHeading)==4){
$desc='One Season';
}else if(strlen($eachHeading)==19){
$desc=''.$eachHeading.'';
}else if(strlen($eachHeading)==9 && substr($eachHeading,5,4)=='PRES'){
$desc=''.substr($eachHeading,0,4).' - '.
'Present';

}else if(strlen($eachHeading)==9 && substr($eachHeading,4,1)=='-'){
$to=substr($eachHeading,0,4);
$from=substr($eachHeading,5,4);
$numOfYears=$from-$to;
if($numOfYears==1){
$desc='Single Season';
}else if($numOfYears<5){
$desc=''.$numOfYears.' Seasons';
}else if($numOfYears>=10){
$desc=''.$numOfYears.' Seasons';
}else{
$desc=''.$numOfYears.' Seasons';
}
}else{
$desc=''.substr($eachHeading,4,1).'=='.strlen($eachHeading).'=='.$eachHeading.'';
}
$eachHeading=removeFileExtension($eachPic);
}else if(isset($arr['picTitles']) && $arr['picTitles']=='number'){
$mode='A';
$eachHeading=$i;
}else if(is_numeric(substr(removeFileExtension($eachPic),0,3) )&&substr(removeFileExtension($eachPic),3,1)==' ' ){
$mode='B';
$eachHeading=substr(removeFileExtension($eachPic),3);
}else if(substr_count(removeFileExtension($eachPic),' - ')==1){
$mode='C';
$eachTitles=explode( ' - ',removeFileExtension($eachPic) );
$eachHeading=$eachTitles[0];
$desc=$eachTitles[1];
}else if(substr_count(removeFileExtension($eachPic),' - ')==2){
$mode='D';
//echo removeFileExtension($eachPic).'
';
$eachTitles=explode( ' - ',removeFileExtension($eachPic) );
//foreach( $eachTitles as $i=>$eachTitlePart){
//echo 'eachTitlePart'.$i.'= "'.$eachTitlePart.'"
';
//}
$eachHeading=$eachTitles[0];
$desc=$eachTitles[1].'
'.$eachTitles[2];
}else if(substr_count(removeFileExtension($eachPic),' - ')==3){
$mode='D';
//echo removeFileExtension($eachPic).'
';
$eachTitles=explode( ' - ',removeFileExtension($eachPic) );
//foreach( $eachTitles as $i=>$eachTitlePart){
//echo 'eachTitlePart'.$i.'= "'.$eachTitlePart.'"
';
//}
$eachHeading=$eachTitles[0];
$desc=$eachTitles[1].'
'.$eachTitles[2].'
'.$eachTitles[3];
}else if(! is_array($eachHeading) && is_string($eachHeading) && DateTime::createFromFormat('Y-m-d', $eachHeading) !== FALSE){
$mode='E';
$date_a = new DateTime($eachHeading.' 00:00:00');
if($deathDate){
$date_b = new DateTime($deathDate.' 00:00:00');
//echo $deathDate.'
';
}else{
$date_b = new DateTime();
}
$interval = date_diff($date_a,$date_b);
$eachHeading=removeFileExtension($eachPic);
if($deathDate){
$eachHeading.='('.$interval->format('%Y').')';
}else{
$eachHeading.='('.$interval->format('%Y').')';
}
}else if(substr($eachPic,2,1)=='-' ){//$arr['lookup']===true
$mode='F';
//echo 'Starts with 2 digits
';
$eachHeading=substr(removeFileExtension($eachPic),3);
}else if($eachHeading==null){//No Title was given, so use file name
$mode='G';
$eachHeading=removeFileExtension($eachPic);
$desc=null;
}else if(is_array($eachHeading) ){
//echo 'eachPic: '.$eachPic.'
eachHeading: '.var_export($eachHeading,true).'
PicTitles: '.$arr['picTitles'].'
'."\n";
if(isset($eachHeading['override'])){
$mode='H1';
$eachHeading=$eachHeading['override'];
unset($desc);
}else if(isset($eachHeading['comment'])){
//var_dump($eachPic);echo '====';var_dump($eachHeading['comment']);
$mode='H2';
$desc=$eachHeading['comment'];//Has to go FIRST!
$eachHeading=removeFileExtension($eachPic);
}else{
$eachHeading=removeFileExtension($eachPic);
//die( 'aa');
unset($desc);
}
//}else if(strpos($arr['title'],'Simpsons')||strpos($arr['title'],'South Park') ){//$arr['lookup']===true
}else if($arr['title']=='Simpsons'||$arr['title']=='South Park'){//$arr['lookup']===true
$mode='I';
$desc=null;
$eachHeading=removeFileExtension($eachHeading);
}else if($eachHeading!=''){//$arr['lookup']===true
$mode='J';
$desc=$eachHeading;
$eachHeading=removeFileExtension($eachPic);
//echo $eachHeading.'=>'.$eachPic.'
';
}
if(isset($arr['addNumbers'])&&$arr['addNumbers']===true){
$eachHeading=$i.'-'.$eachHeading;
}
//$toReturn.='makeCollage Mode: '.$mode.'
'."\n";

if($eachHeading=='NEWLINE'||$eachPic=='NEWLINE'){
//DO Nothing
}else{
if( isset($arr['picTitles']) && $arr['picTitles']!=='none' ){
$toReturn.=''.$eachHeading.'';//."\n"
}
if(isset($desc) ){
$toReturn.='
('.$desc.')';
}
}
if(isset($_GET['checkNewDeaths'])||(isset($arr['checkNewDeaths'])&&$arr['checkNewDeaths']===true) ){
$checkNewDeaths=true;
//die('checkNewDeaths for: '.$arr['title'] );
}else{
$checkNewDeaths=false;
}
//var_dump($deathDate);echo '---'.$arr['title'].'
';
if($deathDate!==false){
$toReturn.=' Died: '.$deathDate.'

';//
//Turn off the "false" to check if anyone new has died:

}else if(!isset($arr['title']) || strpos( $arr['title'],'Old Celebrities' )!==false && $checkNewDeaths===true){
$info =stealInfoFromWebsite('wikipedia', $params=array('name'=>removefileextension($eachPic)) );
//var_dump($info);echo('=='.removefileextension($eachPic).'---'.$arr['title'].'
');

if(isset($info['deathDate']) ){
$newDeaths[$eachPic]=$info['deathDate'];
//die('There has been a new death: '.$eachPic);
}
}
if($eachHeading!=='NEWLINE'&&$eachPic!=='NEWLINE'){
$toReturn.='

'."\n";
}
//echo $i.' => '.$eachPic.' ==>>> '.$eachHeading.' --==--
'."\n";

$i++;
$iRow++;
if(
isset($arr['colsOrWidth']) &&
($arr['colsOrWidth']!=='original') &&
($arr['colsOrWidth']!=null) &&
($arr['colsOrWidth']<=11) &&
((($iRow)>($arr['colsOrWidth']-1)))
){
//echo 'Normal Newline @ '.$i.'
'."\n";
$toReturn.='

'."\n";
$iRow=0;
}

}//end foreach
//prettyArray($arr);die('makeCollage::');
if(count($newDeaths)>0){
//$toReturn.='

New Deaths:

Please Update globalVars.php.
'.implode('
',array_map_assoc(function($k,$v){return "$k ($v)";},$newDeaths);
$toReturn.='

New Celeb Deaths:

Please Update globalVars.php.
'.
http_build_query($newDeaths, '', '
').'

';

//$toReturn.='

New Deaths:

Please Update globalVars.php.
'.implode('
',$newDeaths);
}else if (isset($arr['directory']) &&strpos($arr['directory'],'celebs')!==false&&(isset($checkNewDeaths) && $checkNewDeaths) ){
$toReturn.='

No New Celeb Deaths

';
}
//die($arr['directory']);
if(count($arrNoImage)>0){
$toReturn.='

'."\n";
$toReturn.='

'.'

Missing Images:

'.$eachOne.'

';
}
$toReturn.='

'."\n";
if (isset($arr['sources'])&&$arr['sources']!=null ){
$toReturn.='

Sources:

';
if(is_array($arr['sources']) ){
foreach($arr['sources'] as $eachSource){
$toReturn.=''.$eachSource.'
'."\n";
}
}else{
$toReturn.=''.$arr['sources'].'
'."\n";
}
}
if(isset($arr['link']) && is_array($arr['link']) ){
foreach($arr['link'] as $eachLink){
$toReturn.=$eachLink.'
'."\n";
}
}else if(isset($arr['link']) && $arr['link']!='' ){
$toReturn.=$arr['link'].'
'."\n";
}
if (isset($arr['html'])&&$arr['html']!='' ){
$toReturn.=''.$arr['html'].'
'."\n";
}
$toReturn.='Collage Created using AdamWithers.CA/collages'."\n";
$toReturn.=''."\n";

//$toReturn.=$debugPrintOut.'

eachPic	eachHeading	potentialPicFile	potentialOtherFile	Death Date
Newline: '.$eachPic.'
'.$eachPic.'	'.prettyArrayReturn($eachHeading).'	'. $potentialPicFile.'	'.(isset($potentialOtherFile)?$potentialOtherFile:'NULL').'	'.$deathDate.'

';
return $toReturn;
}
***Declared In:/home4/adamwith/public_html/includes/functions/adamwithers.ca.php

Add a hyphen to lists when Copying & Pasting

const prependChar = "-";
document.addEventListener("copy", function(e) {
const selected = getSelectionHtml();
if (selected.html.includes("")) {
const ul = selected.container.querySelectorAll("li");
Array.from(ul).forEach(c => {
if (c.tagName === "LI") {
c.innerText = prependChar + " $$$ " + c.innerText;
}
});
// Remove unwanted whitespace
const html = selected.container.innerHTML.replace(/(^|>)[ \n\t]+/g, ">\n"); 
const list = `${html}`;
const wrap = document.createElement("div");
wrap.innerHTML = list;
e.clipboardData.setData("text/plain", wrap.textContent);
e.clipboardData.setData("text/html", list);
e.preventDefault();
}
});
function getSelectionHtml() {
let html = "";
let container;
if (typeof window.getSelection !== "undefined") {
const sel = window.getSelection();
if (sel.rangeCount) {
container = document.createElement("div");
for (let i = 0, len = sel.rangeCount; i < len; ++i) {
container.appendChild(sel.getRangeAt(i).cloneContents());
}
html = container.innerHTML;
}
} else if (typeof document.selection !== "undefined") {
if (document.selection.type === "Text") {
html = document.selection.createRange().htmlText;
}
}
return { html, container };
}