Database Arsip - Bardimin

Remove duplicate rows in MySQL

7 December 2021 | Database

If in your database there are repeated rows of data or duplicate rows of data occur, you can remove the duplication easily just by using a MySQL script. In this article, we will learn to remove duplicate data rows from MySQL by using a script that we will run using phpMyAdmin. Prepare sample data table Before we start, set up a table named " duplicate_row " with the following structure and data: CREATE TABLE `duplicate_row` ( `id` int(11) DEFAULT NULL, `refID` int(11) DEFAULT NULL, `data` varchar(7) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0; INSERT INTO `duplicate_row` (`id`, `refID`, `data`) VALUES (1, 3526, 'aaaaaaa'), (2, 3527, 'bbbbbbb'), (3, 3528, 'ccccccc'), (4, 3529, 'ddddddd'), (5, 3527, 'eeeeeee'), (6, 3528, 'fffffff'), (7, 3527, 'ggggggg'); COMMIT; Remove duplicate rows using DELETE JOIN Open phpMyAdmin and select the database name from the " duplicate_row " table . Then click the “ SQL ” tab . Then copy the following script. DELETE t1 FROM...

Script Insert Thousands of Random Data into MySQL

4 December 2021 | Database, Technology

When we create applications that use databases, of course, we need database simulations as in actual conditions. With this simulation, we want to measure the performance of the application we are building. Both under ideal and extreme conditions.In this article, Bardimin will share an easy way by using a script to insert thousands of random data into MySQL. The first script we will share is a function from MySQL to generate random data. While the second script is a procedure to insert data from the first script. Before starting, you must first create a database, then create a table that we will insert with the data. Create a random data table Create a table with the name “ random_data ” which has 3 columns with names (id, column01, colum02) CREATE TABLE `random_data` ( `id` int(11) NOT NULL, `Column01` varchar(20) NOT NULL, `Column02` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; MySQL Random Data Script Open phpMyAdmin. Then, in the left column, click on the database...

Real-Time Application Dashboard with Yii2, Node.js, MySQL and Chart.js

23 September 2021 | Database, Framework, Technology

In this article we will create a dashboard that can display graphics from data in real-time. The data we use as simulations is the population of a city. If you update your resident data, add or delete it, the graphic will change in real-time without the need to refresh from the page. Before we begin, we need to prepare: Webserver is installed, see How to Configure Virtual Host XAMPP in Windows 10.Yii2 is installed, see How to Install Yii2 Advanced via Composer.Node.js is already installed, see Create real-time applications with Nodes.js.Chart.js. Steps to Create a Real Time Application Dashboard with Yii2, Node.js, MySQL and Chart.js A. Create a table with MySQL Create tables with the name "city", and columns with the names "id" and "population". Or it's easier to run the following sql scripts. CREATE TABLE 'city' ( 'id' CHAR(16) NOT NULL PRIMARY KEY, 'population' INT(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; B. Create a Node server.js Set up a Node server.js as...

Create real-time applications with Node.js and MySQL

8 September 2021 | Database, Framework, Technology

In the previous example, Creating a real-time application with Node.js data was not stored permanently. This means that every time the Node server.js we restart, the data will be empty again. To be able to store the inputted data, we must use a database. In this article, we will use the mySQL database that is already quite famous. The same technique you can use if you use another database. The project we will create is a continuation of the project on Creating real-time applications with Nodes.js. If you don't already have one, please follow the article. Steps to create a realtime application with Node.js and MySQL Download and install MySQL.Create a MySQL database like the following script CREATE TABLE 'voting' ( 'id' INTEGER(11) NOT NULL AUTO_INCREMENT, 'name' VARCHAR(12) COLLATE utf8mb4_general_ci NOT NULL, 'time' DATETIME NOT NULL DEFAULT current_timestamp(), PRIMARY KEY USING BTREE ('id') ) ENGINE=InnoDB AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8mb4' COLLATE...