Construct the dynamic table with specified number of columns on each row as per the size of the data in Angular

The below code is to generate a table with multiple rows with the specified number of columns on each row as per the size of the data array.

Then, on the output, we would be able to generate a table with multiple rows with a specified number of columns.

Check if MySQL Query uses cache or not for most frequent query.

Run the following queries to check if most recent MySQL query uses cache or not.

-- Query 1
SHOW VARIABLES LIKE 'have_query_cache'; 

-- The Output Value should be Yes, if it uses cache.

-- Query 2
SHOW VARIABLES LIKE 'query_cache_size';
-- -- The Output Value should be integer value, if it uses cache.

Refer Link 1 and Link 2 for more detail.

Hope the help!

Get MySQL columns names as multiple row or as a concatenated string.

Query to list columns from particular table of the particular database in MySQL:
1. Get the list of columns as multiple rows:

    
SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='database_name'
AND `TABLE_NAME`='table_or_view_name';

2. Get the list of columns as a single concatenated string:

    
SELECT GROUP_CONCAT(`COLUMN_NAME` SEPARATOR ',')
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='database_name'
AND `TABLE_NAME`='table_or_view_name';

Resolve an issue to insert explicit value for identity column in SQL table asking for IDENTITY_INSERT to set ON/OFF in PHP (Laravel)

The following methods to control the `IDENTITY_INSERT` to ON/OFF for SQL table seeding process in PHP (Laravel) :

    
    function setIdentityInsert($table, $onStatus = true)
    {
        $status = $onStatus ? 'ON' : 'OFF';

        $this->sqlConnection->unprepared("SET IDENTITY_INSERT $table $status");
    }

    function insertTableData($table, $data)
    {
        return $this->sqlConnection->table($table)->insert($data);
    }

    function seedTable($table, $hasAutoIncrementPrimaryKey = false, $data = [])
    {
        if ($hasAutoIncrementPrimaryKey) {
            $this->setIdentityInsert($table);
            $response = $this->insertTableData($table, $data);
            $this->setIdentityInsert($table, false);

            return $response;
        }
        else {
            return $this->insertTableData($table, $data);
        }
    }

Note: Generally, the table requires to have an auto-increment primary key to set Identity Insert to `ON`, that's why I have the `$hasAutoIncrementPrimaryKey` flag. Otherwise, seeding may throw an error as:

    
    SQLSTATE[HY000]: General error: 544 Cannot insert explicit value for
    identity column in table 'test_table_name' when IDENTITY_INSERT is set to
    OFF. [544] (severity 16) [(null)]

Hope this helps!

Get a list of columns and their data type by passing table name on MySQL and MSSQL databases.

To get a list of columns and their data type by passing table name on MySQL and MSSQL databases.

    
    /**
     * Returns the list of columns and their data type by the table name on MySQL database.
     * @param $sqlConnection MySQL Database Connection Link
     * @param $tableName
     * @param bool $fullType
     * @return array
     */
    private function getMySqlTableColumnAndTypeList($mysqlConnection, $tableName, $fullType = false)
    {
        $table = $mysqlConnection->select("DESCRIBE $tableName");

        $fieldAndTypeList = [];
        foreach ($table as $field){
            $type = ($fullType || !str_contains($field->Type, '('))? $field->Type: substr($field->Type, 0, strpos($field->Type, '('));
            $fieldAndTypeList[$field->Field] = $type;
        }
        return $fieldAndTypeList;
    }

    /**
     * Returns the list of columns and their data type and nullable by the table name on MSSQL database.
     * @param $sqlConnection SQL Database Connection Link
     * @param $tableName
     * @return array
     */
    private function getSqlTableColumnsWithDataTypes($sqlConnection, $tableName)
    {
        $tableInfo = $sqlConnection->select("SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '". $tableName . "'");

        $fieldAndTypeList = [];
        foreach ($tableInfo as $field){
            $isNullable = ($field->IS_NULLABLE == 'YES') ? true : false;
            $fieldAndTypeList[$field->COLUMN_NAME] = (object)['type' => $field->DATA_TYPE, 'nullable' => $field->IS_NULLABLE, 'isNullable' => $isNullable];
        }

        return $fieldAndTypeList;
    }

Raw Query in MSSQL:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name';

Raw Query in MySQL:

DESCRIBE your_table_name;

Appreciated for any feedback!

Password protect to directory on the web server and Add a new user

Allow User to access the app 1. Create/Update `.htaccess` file in root directory:

    touch .htaccess
    vi .htaccess

Then, add following content:

    AuthUserFile /home/bitnami/htdocs/.htpasswd
    AuthGroupFile /dev/null
    AuthName "Application Authentication"
    AuthType Basic
    require user demo-user1
    require user test-user2

3. And, for passwords, create `.htpasswd` file in root directory.

    touch .htpasswd.

4. To add/edit password, run the following command:

    htpasswd -c .htpasswd demo-user1
    

And, follow instruction ahead.

Laravel - PHPUnit - run single test class or method

The following command runs the test on a single method: (e.g. we are testing the `testCreateUser()` method here)

vendor/bin/phpunit --filter testCreateUser UserControllerTest tests/feature/UserControllerTest.php
vendor/bin/phpunit --filter methodName ClassName path/to/file.php

Further, lets say you want to test a ClassName which exists into two location:

tests/unit/UserControllerTest.php // contains 3 tests
tests/feature/UserControllerTest.php // contains 2 tests

In this case, you simply run below command which will check for all the test on both location.

vendor/bin/phpunit --filter UserControllerTest

-----Output---------
Time: 2.56 seconds, Memory: 30.00MB

OK (5 tests, 22 assertions)

Please note, if you have phpunit available globally on your machine, you can simply run

phpunit --filter 

# instead of 
vendor/bin/phpunit --filter

Stackflow resource.