How to fix (errno: 150 “Foreign key constraint is incorrectly formed”) MySQL, MariaDB

If you catch errno: 150 “Foreign key constraint is incorrectly formed” when you try to create a new table in mySQL(or another relational DB) then you can use a simple way to find a bad foreign key(s). Just execute this query:

SELECT
  CONSTRAINT_NAME, 
  TABLE_NAME, 
  COLUMN_NAME, 
  REFERENCED_TABLE_NAME, 
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = '<your table name here>' OR TABLE_NAME = '<your table name here>';

After that you can see where this bad table using and you can go and delete bad foreign key(s).

Happy SQLing)

Web-server on Ubuntu (PHP + MySQL + Apache) in 2 commands

Fast and easy install (PHP + MySQL + Apache) LAMP on Ubuntu

Step 1: install tasksel

sudo apt-get install tasksel

Step 2: Run install PHP, MySQL & Apache as LAMP server

sudo tasksel install lamp-server

Completed! Now you have installed PHP, MySQL, and Apache on your Ubuntu

Additional: Create site area
Continue reading Web-server on Ubuntu (PHP + MySQL + Apache) in 2 commands

UPDATE with random number between range 1-n [MYSQL UPDATE RAND]

Sometimes you need to update your latest added field in DB table by random numbers.
For that reason you can use this simple SQL command:
[1 – min, 100-max]

UPDATE `...` 
SET `columnName` = FLOOR( 1 + RAND( ) *100 );
WHERE ...

In this way you can easy update `columnName` by random value in defined range.
Happy SQLing… Continue reading UPDATE with random number between range 1-n [MYSQL UPDATE RAND]

How to add one/two/n days or hours to date in MySQL (DATE_ADD in MYSQL)

Suppose you have a table in the database where the stored data and you need to check or get that dates but with the changes – calculate directly by MySQL.
Example, you want to get users end dates (7-day licence after registration):

SELECT `register_date`, `uid`, 
DATE_ADD(`register_date`, INTERVAL 7 DAY) AS `end_date` 
FROM `...`
WHERE...

Perfect, now you can show for user that his licence if over `end_date`…
In addition, you can subtract the required number of days
Continue reading How to add one/two/n days or hours to date in MySQL (DATE_ADD in MYSQL)

How to strip all special symbols/characters from string for save to mysql database

/***
* Function for delete utf special symbols.
    Units of measure:
        \x{00B0} for the degree symbol.
        \x{2103} for the degree celsius symbol.
        \x{2109} for the degree fahrenheit symbol.
        \x{23CD} for the square foot symbol.
        \x{32CC} to \x{32CE} for units symbols.
        \x{3300} to \x{3357} for ideographic units symbols.
        \x{3371} to \x{33DF} for more units symbols.
        \x{33FF} for the gallon symbol.

    Ideograph radicals, strokes, symbols, and descriptors:
        \x{2E80} to \x{2EF3} for CJK radicals.
        \x{2F00} to \x{2FD5} for KangXI radicals.
        \x{2FF0} to \x{2FFB} for ideographic descriptors.
        \x{3037} to \x{303F} for miscellaneous ideographic indicators.
        \x{3190} to \x{319F} for ideographic annotation marks.
        \x{31C0} to \x{31CF} for CJK strokes.
        \x{32C0} to \x{32CB} for ideograph month symbols.
        \x{3358} to \x{3370} for ideograph time symbols.
        \x{33E0} to \x{33FE} for ideograph day symbols.
        \x{A490} to \x{A4C6} for YI radicals.

***/
function removeSpecSymbols($text = '')
    {
        $units = '\x{00B0}\x{2103}\x{2109}\x{23CD}';
        $units .= '\x{32CC}-\x{32CE}';
        $units .= '\x{3300}-\x{3357}';
        $units .= '\x{3371}-\x{33DF}';
        $units .= '\x{33FF}';

        $ideo = '\x{2E80}-\x{2EF3}';
        $ideo .= '\x{2F00}-\x{2FD5}';
        $ideo .= '\x{2FF0}-\x{2FFB}';
        $ideo .= '\x{3037}-\x{303F}';
        $ideo .= '\x{3190}-\x{319F}';
        $ideo .= '\x{31C0}-\x{31CF}';
        $ideo .= '\x{32D0}-\x{32FE}';
        $ideo .= '\x{3358}-\x{3370}';
        $ideo .= '\x{33E0}-\x{33FE}';
        $ideo .= '\x{A490}-\x{A4C6}';

        $text = preg_replace('/\p{So}(?<![' . $units . $ideo . '])/u', ' ', $text);

        return $text;
    }

$text = removeSpecSymbols($text);

Yii 2 database field types

The following abstract column types are supported:

  • pk : an auto-incremental primary key type, will be converted into “int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY”
  • bigpk: an auto-incremental primary key type, will be converted into “bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY”
  • string: string type, will be converted into “varchar(255)”
  • text: a long string type, will be converted into “text”
  • smallint: a small integer type, will be converted into “smallint(6)”
  • integer: integer type, will be converted into “int(11)”
  • bigint: a big integer type, will be converted into “bigint(20)”
  • boolean: boolean type, will be converted into “tinyint(1)”
  • float: float number type, will be converted into “float”
  • decimal: decimal number type, will be converted into “decimal”
  • datetime: datetime type, will be converted into “datetime”
  • timestamp: timestamp type, will be converted into “timestamp”
  • time: time type, will be converted into “time”
  • date: date type, will be converted into “date”
  • money: money type, will be converted into “decimal(19,4)”
  • binary: binary data type, will be converted into “blob”

 
Continue reading Yii 2 database field types

How to set right page encoding using PHP (UTF-8 HTTP header)?

Quite frequently, many are faced with the problem: incorrect definition of the browser page coding.
The easy way to fix it – set the right page encoding by PHP.
Example (we use the encoding utf-8) ↓

<?php
header('Content-Type: text/html; charset=utf-8');
/*....*/
?>

Additionally, you can specify the encoding using HTML
Continue reading How to set right page encoding using PHP (UTF-8 HTTP header)?