IT developer remarks

If you have separated ajax table sheet with data and filters, sometimes, you need to keep this filtered data between requests. In my case, I needed to be able to download file in CSV format, after choosing all necessary filters in table. So, it is possible to store SQL query in client Session after applying all filters and receive data from DB via DQL. To store DQL in session: $query = $this->createQueryBuilder('i')->select('COUNT(i)'); $_SESSION['query'] = $query->getQuery()->getDQL(); $_SESSION['query_params'] = $query->getQuery()->getParameters(); To get data from DB: $data = $this->createQuery($_SESSION['query'])->setParameters($_SESSION['query_params'])->getResult();

I know one dirty life-hack, how to copy full data from database with all configs and users. Just copy all files from old base to new one . For example (for Linux): cp -r /var/lib/mysql /new_destination It will transfer not only data from database, but and users with their own passwords and privileges. NOTE: It works only if you database placed on one host (server). I must emphasize that using the method described above to copy a database, including configurations and users, can have serious consequences and is not recommended for various reasons. Firstly, it may violate privacy and security regulations, potentially exposing sensitive information. Secondly, this approach might lead to compatibility issues and corruption of data in the new destination. Moreover, it could compromise the integrity of the database and jeopardize the overall system's stability. It's crucial to follow proper procedures when migrating or copying databases to ensure data security, integrity, and ...

If you are not lucky like me, and do not have MySQL 8.0 version or higher, there is only one way to rename column in table is using CHANGE command. For example: ALTER TABLE products CHANGE old_column new_column INT NOT NULL; For MySQL 8.0 you can use next syntax: ALTER TABLE products RENAME COLUMN old_column TO new_column; But when you use RENAME command, you are not allow to change data type of this column, only name.

 I use Doctrine ORM with Symfony framework.  Doctrine entities have the capability to handle JSON type columns, automatically converting data from an array to JSON and vice versa. This means they can effortlessly perform the conversion of data from an array format to JSON and vice versa, offering a convenient and dynamic approach. This proves exceptionally useful in scenarios such as storing and managing various settings within the database. Hence, to create a new column in a table with a JSON type, use the following syntax: settings LONGTEXT DEFAULT NULL COMMENT '(DC2Type:json)' This concise yet powerful declaration ensures that the column is set up to handle JSON data effectively. For adding a new column to an existing 'tblcontacts' table, execute the following SQL query: ALTER TABLE tblcontacts ADD COLUMN settings LONGTEXT DEFAULT NULL COMMENT '(DC2Type:json)'; The critical aspect here is the "COMMENT '(DC2Type:json)'". This specific c...

In MySQL, the "IF EXIST" clause doesn't function when checking a foreign key. There is only one method I know to perform this check. Information regarding constraints is stored in the general MySQL table  "information_schema.TABLE_CONSTRAINTS".  The "TABLE_NAME" column indicates the table where the constraint exists. The "CONSTRAINT_NAME" column represents the name of your constraint. The "CONSTRAINT_TYPE" column defines the type of your constraint; in our case, it is a "FOREIGN KEY". So, for example, if you want to check all foreign keys in your table: SELECT * FROM information_schema.TABLE_CONSTRAINTS  WHERE TABLE_NAME='name_of_your_table' AND CONSTRAINT_TYPE='FOREIGN KEY'; In my case it shows: CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE def test FK_9CA1B3534C397118 test tblproducts FOREIGN KEY def te...