Cameron McLuckie, PHP / MySQL Information

I’m a web developer based in the UK. I often find myself looking up the same php / MySQL code all the time in Google, so instead I have created this simple webpage to store all the snippets that I use on a regular basis for quick reference.

PHP Arrays

One of the fundamental pieces of code I use in PHP is arrays, arrays are part of pretty much everything I do, below is an example of the code I will use as part of array building

//------------------------
// ARRAYS
//------------------------

//IMPLODE ARRAY (ARRAY TO STRING)
	$arr = array('Cameron','McLuckie','Website');
	implode(" ",$arr);	

	//TO IMPLODE THE ARRAY TO A STRING BUT IMPLODE THE KEY AND VALUE USE http_build_query

		$a=array("key1"=>"value1", "key2"=>"value2");
		echo http_build_query($a,'','&');

		Output: key1=value1&key2=value2 //Beware of the string encoding! If you are not building an URL maybe you do not want it on your array key&value - You can wrap http_build_query in urldecode to avoid it

//EXPLODE STRING TO ARRAY
	$pizza  = "my name is Cameron McLuckie welcome to my website";
	$pieces = explode(" ", $pizza);
	echo $pieces[0]; // my
	echo $pieces[1]; // name

String Functions

The following string functions in php may come in handy in the future

//------------------------
// STRING FUNCTIONS AND REGEX
//------------------------

//NEW LINE
	"\r\n" - needs double quotes

//remove height="..."
	height="([^"]*)"
	height=\"(.[0-9]*)\"

//MATCH EVERYTHING FROM THE FIRST CHARACTER (') TO THE END OF THE LINE
	'.*

//CASE INSENSITIVE REPLACE TEXT IN A STRING
	str_ireplace("WORLD","Cameron","Hello world!");

PHP Dates

I’m constantly forgetting my “H” from my “i” from my “s” – in those cases I normally go and visit http://php.net/manual/en/function.date.php. The following may also be useful.

$date = new DateTime('2000-01-01');
$date->modify('+6 months');
echo $date->format('Y-m-d H:i:s');

MYSQL

Working in PHP likely means that I am working just as much within MySQL. The following can be of help.

//SHOW MOST RECENT DEADLOCK ON THE DATABASE: 
SHOW ENGINE INNODB STATUS;

You should now be able to use the following to start, restart or stop MySQL:

  /etc/init.d/mysqld start
  /etc/init.d/mysqld restart
  /etc/init.d/mysqld stop
  sudo apachectl restart
	

//THIS LISTS AND KILLS PROCESSES ON THE SERVER
  SHOW PROCESSLIST
  KILL QUERY **ID**
	
//IF I CANNONT RESTART THE SERVER BUT JUST WANT TO KILL A BATCH OF QUERIES i CAN RUN THE FOLLOWING THAT WILL GENERATE THE KILL COMMANDS NEEDED
  select concat('KILL ',id,';') from information_schema.processlist
  where 
  #user='root'
  #and 
  time > 200 
  #and 
  #command='sleep'

//COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values - See more at: http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php#sthash.MTVSmOIK.dpuf

//CONCAT() function is used to add two or more strings - See more at: http://www.w3resource.com/mysql/string-functions/mysql-concat-function.php#sthash.6dzFeZks.dpuf

//GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group - See more at: http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php#sthash.FQAi8pne.dpuf

//GROUP_CONCAT(DISTINCT <column> ORDER BY <column> ASC) function returns a string with concatenated non-NULL value from a group and distinct


//REPLACE A STRING IN A DATABASE COLUMN - http://stackoverflow.com/questions/5956993/mysql-string-replace
  UPDATE your_table SET your_field = REPLACE(your_field, 'old_value', 'new_value') WHERE your_field LIKE '%old_value%'

//CHANGE FROM MYISM TO INNODB
  ALTER TABLE <table_name> ENGINE=InnoDB;

//TO CREATE A TABLE LIKE THE OLD ONE WITHOUT COPYING THE DATA
  CREATE TABLE new_table LIKE old_table;
  CREATE TABLE db1.variables LIKE db2.variables;
		
//SOMETIMES A WEBSITE MAY 500 ERROR OUT IF THERE'S TOO MANY QUERIES GOING TO THE SERVER FROM IT, TO CLEAR THIS USE: -
  flush hosts;

//Get row with highest or lowest value from a GROUP BY
// PLEASE SEE http://stackoverflow.com/questions/16910050/get-row-with-highest-or-lowest-value-from-a-group-by

//GET A LIST OF THE COLUMNS WITHIN A DATABASE
  SELECT `COLUMN_NAME`  FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='[DATABASE NAME]' AND `TABLE_NAME`='[TABLE NAME]'; OR describe [TABLE NAME];

//SEARCH THROUGH A CSV VALUE IN THE DATABASE - FIND_IN_SET
http://stackoverflow.com/questions/23815112/how-to-use-find-in-set-with-non-comma-separation
  //FIND_IN_SET('LCACU' ,accommodation_id ) - THIS WILL SEARCH THROUGH A CSV COLUMN CALLED accommodation_id FOR TEXT "LCACU"
  FIND_IN_SET('LCACU' ,REPLACE(accommodation_id, '|', ',') ) - THIS WILL SEARCH THROUGH PIPES

//MYSQL IF THE ELSE CASE EXAMPLE
  SELECT col1, col2,
			(CASE
				WHEN col3 = 0 THEN 'qw'
				WHEN col3 = 1 THEN 'er'
				WHEN col3 = 2 THEN 'ty'
				WHEN col3 = 3 THEN 'ui'
				ELSE ''
				END)  as col3,
  FROM table

//MYSQL - SELECT WITHIN A JOIN
		SELECT COL1, COL2, COL3
		FROM
		TABLE1 t1
		LEFT JOIN 
		(
			SELECT
			COL4, COL5, COL6
			FROM TABLE2
			WHERE STUFF IN (A,B,C)
			ORDER BY SOMETHING
		) t2 ON t1.COL1 = t2.COL4
		WHERE
		t1.STUFF BETWEEN "XXXX-XX-XX XX:XX:XX"
		AND "XXXX-XX-XX XX:XX:XX"
		GROUP BY
		t1.COL2;

Linux CLI

In addition to the above the following Linux command line instructions have came in handy in the past.

//DELETE / REMOVE FILES
//TO REMOVE THE FOLDER WITH ALL ITS CONTENTS(INCLUDING ALL INTERIOR FOLDERS) - https://askubuntu.com/questions/60228/how-to-remove-all-files-from-a-directory
  rm -rf /path/to/directory

//TRACEROUTE
  mtr www.cameronmcluckie.com
  mtr --report www.cameronmcluckie.com

//LINUX WATCH COMMAND
  watch -n 1 date
  watch -n 1 “ls /var/www/html/website/”
  //The above command would run ls /var/www/html/website/ every 1 second

//UBUNTU DATE AND TIME
  //GET THE CURRENT TIME ZONE ETC
    timedatectl
  //SYNC TIME WITH NTP SERVER
    sudo ntpdate time.nist.gov

//VIEW AND KILL RUNNING PROCCESSES
  ps xa
  ps aux //THIS GIVES ME THE START DATE

//VIEW RUNNING PROCESSWS WITH "CAMERON" IN IT
  ps aux | grep cameron

  kill **pid**
  killall <process-name>
  //https://www.tecmint.com/how-to-kill-a-process-in-linux/

//SEARCH FOR TEXT WITHIN PHP FILES USING GREP ON THE SERVER ---> grep -rl "string" /path   (http://askubuntu.com/questions/55325/how-to-use-grep-command-to-find-text-including-subdirectories)
  // -r (or --recursive) option is used to traverse also all sub-directories of /path, whereas
  // -l (or --files-with-matches) option is used to only print filenames of matching files, and not the matching lines (this could also improve the speed, given that grep stop reading a file at first match with this option).
  // -i make it case insensitive
		
  //EXAMPLE
    grep -rli "Cameron McLuckie" /var/www/html/website/ --include=*.php

//ONLY SEARCH HTACCESS FILES IN THE LOCATION
    grep -rli "Cameron McLuckie" /var/www/html/website/ --include=.htaccess

//HOW MUCH FREE SPACE IS LEFT ON SERVER
  df -h
	
//HOW MUCH MEMORY IS ON THE SERVER
  free -m
  or
  free -mt

//COPY FOLDER ON THE SERVER - as per instructions at http://www.cyberciti.biz/faq/copy-folder-linux-command-line/ - this doesn't copy hidden files like .htaccess
  //cp -avr source destination