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