/ Gists

Gists

On gists

PHP in the command line

PHP

commands.cmd #

# To parse and execute a php script:
php file
 
# To check syntax on (i.e. lint) a PHP script:
php -l file
 
# To run PHP interactively:
php -a
 
# To run PHP code (Notes: Don't use <? ?> tags; escape double quotes with backslash):
php -r "code"
 
# To start a PHP built-in web server in the current directory:
php -S host:port
On gists

PHP Method Chaining

PHP

PHP Method Chaining.php #

<?php
 
class QueryMaker
{
    $table = '';
$field = '';
 
    // this is a static method, it doesn't
    // run on an object, it only runs on a class
    public static function make()
    {
        // create an instance of class
        // QueryMaker and return it
        return new static();
    }
 
    // this is not static, it doesn't run on
    // a class, only on an object
    public function setTable($name)
    {
        $this->table = $name;
        return $this;
    }
 
    // this is also not static
    public function setField($name)
    {
        $this->field = $name;
        return $this;
    }
 
    // again, not static, just renders
    // the "query"
    public function flush()
    {
        return "select {$this->field} from {$this->table}";
    }
}
 
// Here is the implementation with method chaining
$query = QueryMaker::make()->setTable('users')->setField('name')->flush();
 
// Here is the implementation without method chaining
$object = new QueryMaker();
$object->setTable('users');
$object->setField('name');
$query = $object->flush();
// the output is: select name from users
 
// The methods setTable() and setField() return $this.
// In that context $this is the QueryMaker object that was created by make().
 
// Let's go step by step:
 
$query = QueryMaker::make()->setTable('users')->setField('name')->flush();
// The static method QueryMaker::make() returns an object instantiation of the QueryMaker class.
 
$query = $object->setTable('users')->setField('name')->flush();
// $object->table is set, setTable() returns the object instance.
 
$query = $object->setField('name')->flush();
// $object->field is set, setField() returns the object instance.
 
$query = $object->flush();
// The flush method is called on the object, returning the string.
On gists

Find Duplicate Entries in MySQL

MySql

Find Duplicate Entries in MySQL. #

-- =============================================================================
-- Find Duplicate Input With MySQL
-- http://kvz.io/blog/2013/03/04/find-duplicate-input-with-mysql/
--
-- At my company we have employees creating customer accounts every day.
-- Sometimes we make mistakes, for instance, we forget to check if the company
-- already was a customer (maybe 10y ago they may have had a product).
--
-- Duplicate accounts can cause all sorts of problems, so I wanted way to detect
-- them with SQL.
--
-- The problem was, the company names may have been entered with different
-- punctuation, whitespace, etc. So I needed similar names to surface from the
-- depths of our database, not just exact matches (that would have been too easy
-- :)
--
-- For the solution I turned to SOUNDEX for fuzzy matching similar sounding
-- company names, and then review the results myself (false positives are
-- possible, but since they would be few, it becomes a simple task to
-- doublecheck) and report back to our company.
--
-- I thought I'd share
--
-- * partly because it could be useful to others (obviously this could be used
--   to detect all kinds of user generated typos and similar entries);
-- * mostly because I'm curious to find if there is a better (more performant)
--   way to write this query.
-- =============================================================================
 
--
-- Select all the individual company names that have a
-- soundex_code that occurs more than once (I now use a subquery for that)
SELECT
  `id`,
  `customer_name`,
  SOUNDEX(`customer_name`) AS soundex_code
FROM `customers`
WHERE SOUNDEX(`customer_name`) IN (
  -- Subquery: select all soundex_codes that occur more than once,
  -- (this does not return the individual company names that share them)
  SELECT SOUNDEX(`customer_name`) AS soundex_code
  FROM `customers`
  WHERE 1 = 1
    AND `is_active` = 1
    -- More specific criteria to define who you want to compare
  GROUP BY soundex_code
  HAVING COUNT(*) > 1
)
ORDER BY
  soundex_code,
  `customer_name`
 
--
-- This e.g. returns:
--
-- -----|------------------|------------------
-- id      customer_name       soundex_code
-- -----|------------------|------------------
-- 291     F.S. Hosting        F2352
-- 1509    FS hosting          F2352
-- 9331    R  Schmit           R253
-- 9332    R Schmit            R253
On gists

MySQL delete dupes on multiple columns

•
MySql

delete-duplicite.sql #

# If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name



# If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name
On gists

MySQL delete dupes, keep newer

MySql

MySQL delete dupes, keep newer.s #

DELETE mytable
    FROM mytable
    INNER JOIN (
            SELECT max(id) AS lastId, someColumnWithDupes
            FROM mytable
            GROUP BY someColumnWithDupes
            HAVING count(*) > 1
            ) duplic ON duplic.someColumnWithDupes = mytable.someColumnWithDupes
    WHERE mytable.id < duplic.lastId;
On gists

mysql command

MySql CLI

mysql command.cmd #

# To connect to a database:
mysql database_name
 
# To connect to a database, user will be prompted for a password:
mysql -u user --password database_name
 
# To connect to a database on another host:
mysql -h database_host database_name
 
# To connect to a database through a Unix socket:
mysql --socket path/to/socket.sock
 
# To execute SQL statements in a script file (batch file):
mysql database_name < script.sql
 
# ---
 
# To connect to a database
$ mysql -h localhost -u root -p
 
# To backup all databases
$ mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql
 
# To restore all databases
$ mysql -u root -p  < ~/fulldump.sql
 
# Create database with utf-8 collation and utf-8 charset
$ mysql> create database <database_name> character set utf8 collate utf8_general_ci;
 
# Create a table with an auto-incrementing primary key (id), w/ utf-8
$ mysql> create table <table_name> (`id` int unsigned auto_increment, primary key (`id`)) default character set utf8 collate utf8_general_ci;
 
# List all databases on the sql server.
$ mysql> show databases;

# To dump a database to a file (Note that your password will appear in your command history!):
mysqldump -uusername -ppassword the-database > db.sql
 
# To dump a database to a file:
mysqldump -uusername -p the-database > db.sql
 
# To dump a database to a .tgz file (Note that your password will appear in your command history!):
mysqldump -uusername -ppassword the-database | gzip -9 > db.sql
 
# To dump a database to a .tgz file:
mysqldump -uusername -p the-database | gzip -9 > db.sql
 
 # To dump all databases to a file (Note that your password will appear in your command history!):
mysqldump -uusername -ppassword --all-databases > all-databases.sql
 
# To dump all databases to a file:
mysqldump -uusername -p --all-databases > all-databases.sql
 
# To export the database structure only:
mysqldump --no-data -uusername -p the-database > dump_file
 
# To export the database data only:
mysqldump --no-create-info -uusername -p the-database > dump_file
On gists

Replace text in all string columns of a database

MySql

Replace text in all string colum #

Select Concat( 'update ', table_schema, '.', table_name,
               ' set ', column_name,
               '=replace(', column_name, ',''old_text'',''new_text'');'
             )
From information_schema.columns
Where (data_type Like '%char%' or data_type like '%text' or data_type like '%binary')
  And table_schema = 'dbname';
On gists

List MySQL Database sizes

MySql

List MySQL Database sizes.sql #

SELECT
  table_schema AS 'Db Name',
  Round(Sum(data_length + index_length) / 1024 / 1024, 3) AS 'Db Size (MB)',
  Round(Sum(data_free) / 1024 / 1024, 3) AS 'Free Space (MB)'
FROM
  information_schema. TABLES
GROUP BY
  table_schema;
On gists

Get Array Values Recursively

PHP

demo.php #

<?php
 
/**
 * Get Array Values PHP Recursively
 *
 * https://davidwalsh.name/get-array-values-with-php-recursively
 *
 * @param array $array The source array.
 * @return array The flattened array values.
 */
function array_values_recursive($array)
{
    $flat = array();
 
    foreach ($array as $value) {
        if (is_array($value)) {
            $flat = array_merge($flat, array_values_recursive($value));
        } else {
            $flat[] = $value;
        }
    }
 
    return $flat;
}
On gists

Sanitize all PHP Super Globals

PHP Protecting

demo.php #

<?php
 
//
// Sanitize all dangerous PHP super globals.
//
// The FILTER_SANITIZE_STRING filter removes tags and remove or encode special
// characters from a string.
//
// Possible options and flags:
//
//   FILTER_FLAG_NO_ENCODE_QUOTES - Do not encode quotes
//   FILTER_FLAG_STRIP_LOW        - Remove characters with ASCII value < 32
//   FILTER_FLAG_STRIP_HIGH       - Remove characters with ASCII value > 127
//   FILTER_FLAG_ENCODE_LOW       - Encode characters with ASCII value < 32
//   FILTER_FLAG_ENCODE_HIGH      - Encode characters with ASCII value > 127
//   FILTER_FLAG_ENCODE_AMP       - Encode the "&" character to &amp;
//
//
// <?php
//
// // Variable to check
// $str = "<h1>Hello WorldÆØÅ!</h1>";
//
// // Remove HTML tags and all characters with ASCII value > 127
// $newstr = filter_var($str, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
// echo $newstr;
//  -> Hello World!
//
// ?>
//
 
foreach ($_GET as $key => $value)
{
    $_GET[$key] = filter_input(INPUT_GET, $key, FILTER_SANITIZE_STRING);
}
 
foreach ($_POST as $key => $value)
{
    $_POST[$key] = filter_input(INPUT_POST, $key, FILTER_SANITIZE_STRING);
}
 
foreach ($_COOKIE as $key => $value)
{
    $_COOKIE[$key] = filter_input(INPUT_COOKIE, $key, FILTER_SANITIZE_STRING);
}
 
foreach ($_SERVER as $key => $value)
{
    $_SERVER[$key] = filter_input(INPUT_SERVER, $key, FILTER_SANITIZE_STRING);
}
 
foreach ($_ENV as $key => $value)
{
    $_ENV[$key] = filter_input(INPUT_ENV, $key, FILTER_SANITIZE_STRING);
}
 
$_REQUEST = array_merge($_GET, $_POST);