MySQL

In this post I will list a few options to migrate a table from MySQL to Redis logically. I will run examples using the classical world database. The world database can be downloaded here, so if you would like to try the examples, just import it in ... [More] your MySQL instance. mysql> use world; Database changed mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.01 sec) There are several ways to go ahead and import a table into Redis, let’s go through some of them. In the following examples I will choose a Hash data type, the most similar thing to a traditional table in Redis. Redis Hashes are maps between string fields and string values, so they are the perfect data type to represent objects (e.g. A User with a number of fields like name, surname, age, and so forth) Export in CSV format using SELECT INTO OUTFILE, import with Python In order to export data from MySQL and import it into Redis, we can use MySQL SELECT INTO OUTFILE facility, which generates an output file formatted as desired. In particular, I will make sure fields are separated by commas. Let’s try it: mysql> SELECT * INTO OUTFILE '/tmp/city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM world.city; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement Oh, MySQL default configuration disallows exporting data to the file system. Let’s configure the my.cnf configuration file and fit it: [mysqld] secure_file_priv=/tmp Now I can finally export: mysql> SELECT * INTO OUTFILE '/tmp/city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM world.city; Query OK, 4079 rows affected (0.01 sec) Here is an example: "1","Kabul","AFG","Kabol","1780000" "2","Qandahar","AFG","Qandahar","237500" "3","Herat","AFG","Herat","186800" "4","Mazar-e-Sharif","AFG","Balkh","127800" "5","Amsterdam","NLD","Noord-Holland","731200" "6","Rotterdam","NLD","Zuid-Holland","593321" "7","Haag","NLD","Zuid-Holland","440900" "8","Utrecht","NLD","Utrecht","234323" Now that data is in CSV format, I can import it with a simple Python script, to make an example it is possible to use the following (refer to instructions to setup a Python environment to connect to Redis). #!/usr/bin/python3 import redis from csv import reader r = redis.Redis(host='127.0.0.1', port=4321, password='') with open('/tmp/city.csv', 'r') as cities: csv_reader = reader(cities) for row in csv_reader: r.hset("city:" + row[0], mapping={ "Name" : row[1], "CountryCode" : row[2], "District" : row[3], "Population" : row[4] }) Let’s check if it was imported correctly: 127.0.0.1:4321> SCAN 0 MATCH city:* COUNT 5 1) "3072" 2) 1) "city:2316" 2) "city:749" 3) "city:570" 4) "city:3625" 5) "city:3328" 6) "city:1771" I can verify the content of a Hash entry as well: 127.0.0.1:4321> HGETALL city:4059 1) "Name" 2) "Cary" 3) "CountryCode" 4) "USA" 5) "District" 6) "North Carolina" 7) "Population" 8) "91213" Export in CSV format using mysqldump Instead of using SELECT INTO OUTFILE (which to some it may represent a security issue because it is the server itself to dump the record to the file system, and it needs secure_file_priv to be enabled), it is possible to export data remotely, using mysqldump backup utility which, acting as a client, does not require any change to server configuration. mysqldump --host=127.0.0.1 --user=root --password --tab=/tmp --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\n' world city Once the file is exported (mysqldump exports two files, table definitions in city.sql file, and the CSV formatted file, city.txt), data can be imported as explained. Import CSV dump with RIOT Assuming you have a CSV dump of your MySQL table (in reality the CSV can proceed from any arbitrary source of data, an Excel spreadsheet, Postgres…) and you don’t want to code any single line to import it into Redis, you can take advantage of RIOT (Redis Input Output Tools), that serve the purpose of migrating heterogeneous sources of data to Redis. Redis Input Output Tools: RIOTRedis Input/Output Tools (RIOT) is a series of utilities designed to help you get data in and out of Redis. In this section I will explore the CSV import utility of RIOT, that is called RIOT File. First, install it (I have a Mac, but refer to the documentation for other installation methods). brew install redis-developer/tap/riot-file Once installed, test the help: (redisvenv) bash-3.2$ riot-file --help The operation couldn’t be completed. Unable to locate a Java Runtime. Please visit http://www.java.com for information on installing Java. And yes, you need also to download a JRE from www.java.com. Once done, go ahead: (redisvenv) bash-3.2$ riot-file import hset --help Usage: riot-file import hset [OPTIONS] Set hashes from input -H, --help Show this help message and exit -s, --separator= Key separator (default: :) -r, --remove Remove key or member fields the first time they are used --ignore-missing Ignore missing fields -p, --keyspace= Keyspace prefix -k, --keys=... Key fields --include=... Fields to include --exclude=... Fields to exclude Now we have to map the CSV source of data to the proper Hash data structure. Our initial CSV file had no header in the first line. If we would like to have it, we could even extract them from MySQL information_schema in the right order as follows. Note: MySQL SELECT INTO OUTFILE does not add a header to the exported dataset, this must be added manually using the proper SQL syntax, or editing the exported CSV file. mysql> SELECT group_concat(COLUMN_NAME ORDER BY ORDINAL_POSITION separator ',') FROM information_schema.COLUMNS WHERE TABLE_NAME='city' AND TABLE_SCHEMA='world'; +-------------------------------------------------------------------+ | group_concat(COLUMN_NAME ORDER BY ORDINAL_POSITION separator ',') | +-------------------------------------------------------------------+ | ID,Name,CountryCode,District,Population | +-------------------------------------------------------------------+ 1 row in set (0.00 sec) And add the line 'ID,Name,CountryCode,District,Population‘ as the first line in the CSV file. But we can still go ahead and do the mapping manually as follows. Here we specify the order of the fields as they come in the CSV file, what keyspace name we want, and what key will complement the keyspace name (e.g. city:1234). riot-file -h 127.0.0.1 -p 4321 import /tmp/city.csv --fields ID Name CountryCode District Population hmset --keyspace city --keys ID Alternatively, if the CSV file has the header obtained from MySQL, we can just add --header to use the CSV header added manually. riot-file -h 127.0.0.1 -p 4321 import /tmp/city.csv --header hmset --keyspace city --keys ID Use RIOT DB to import from MySQL In order to import from MySQL with no intermediate exported dump, you can code your solution including connectors to both the source database and the target Redis database, or simply use RIOT DB. So install it: brew install redis-developer/tap/riot-db Then proceed to import as usual, specifying the keyspace name and the key, and providing the correct connection string. And obviously, the query that defines the dataset to be imported. riot-db -h 127.0.0.1 -p 4321 import "SELECT * FROM city" --url jdbc:mysql://root:Oracle1*@127.0.0.1:3306/world hmset --keyspace city --keys ID Export and do mass insertion in RESP format So far I have explored logical approaches, but for mass insertion, where top speed is required to insert millions of keys, there is a different approach using the pipe mode of redis-cli, which reads data when it is available and send it to the server as soon as possible. In particular, using this method, we will use Redis Protocol, RESP. Read more about mass insertion. It is possible to export a table in RESP format and stream it to the Redis Server in a shot. So let’s see how this works. Using SQL we can generate entries to be sent to Redis Server using this format: * $ $ ... $ Where: args is the number of argumentslenN is the length of the argument that will followargN is the argumentSo assuming that we want to add a Hash for every row in the MySQL table, and every Hash will insert 4 fields, the SQL statement to produce the dump in RESP protocol would be the following: SELECT CONCAT( "*10\r\n", '$', LENGTH(redis_cmd), '\r\n',redis_cmd, '\r\n','$', LENGTH(redis_key), '\r\n',redis_key, '\r\n', '$', LENGTH(hkey1), '\r\n',hkey1, '\r\n','$', LENGTH(hval1), '\r\n', hval1, '\r\n' '$', LENGTH(hkey2), '\r\n',hkey2, '\r\n','$', LENGTH(hval2), '\r\n', hval2, '\r\n' '$', LENGTH(hkey3), '\r\n',hkey3, '\r\n','$', LENGTH(hval3), '\r\n', hval3, '\r\n' '$', LENGTH(hkey4), '\r\n',hkey4, '\r\n','$', LENGTH(hval4), '\r\n', hval4, '\r\n' ) FROM ( SELECT 'HSET' AS redis_cmd, CONCAT('city:',ID) AS redis_key, 'Name' AS hkey1, Name AS hval1, 'CountryCode' AS hkey2, CountryCode AS hval2, 'District' AS hkey3, District AS hval3, 'Population' AS hkey4, Population AS hval4 FROM world.city ) AS t; Save this SQL in a resp.sql file and stream from MySQL into Redis using the pipe mode: bash-3.2$ mysql -h 127.0.0.1 -uroot -p -Dworld --skip-column-names --raw --scan --pattern city:* -i 0.01 | xargs redis-cli -p unlink Wrapping Up You are now able to export a table from MySQL (or other relational database: any database can surely export to CSV, and either RIOT DB or RedisCDC includes connectors to several databases). Connectors supported by RedisCDC are here, connectors supported by RIOT DB are here. And now what? You may be wondering what you can do with those Hashes created in the Redis instance. So in my next post I will share a few example commands to store, retrieve, change, delete, index and search data in Redis. Stick around, I will write about it soon! The post Migrate a MySQL Table to Redis appeared first on mortensi. [Less]

In this post I will list a few options to migrate a table from MySQL to Redis logically. I will run examples using the classical world database. The world database can be downloaded here, so if you would like to try the examples, just import it in ... [More] your MySQL instance. mysql> use world; Database changed mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.01 sec) There are several ways to go ahead and import a table into Redis, let’s go through some of them. In the following examples I will choose a Hash data type, the most similar thing to a traditional table row in Redis. Redis Hashes are maps between string fields and string values, so they are the perfect data type to represent objects (e.g. A User with a number of fields like name, surname, age, and so forth) Export in CSV format using SELECT INTO OUTFILE, import with Python In order to export data from MySQL and import it into Redis, we can use MySQL SELECT INTO OUTFILE facility, which generates an output file formatted as desired. In particular, I will make sure that the fields are separated by commas. Let’s try it: mysql> SELECT * INTO OUTFILE '/tmp/city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM world.city; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement Oh, MySQL default configuration disallows exporting data to the file system. Let’s configure the my.cnf configuration file and fix it: [mysqld] secure_file_priv=/tmp Now I can finally export: mysql> SELECT * INTO OUTFILE '/tmp/city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM world.city; Query OK, 4079 rows affected (0.01 sec) Here is an example: "1","Kabul","AFG","Kabol","1780000" "2","Qandahar","AFG","Qandahar","237500" "3","Herat","AFG","Herat","186800" "4","Mazar-e-Sharif","AFG","Balkh","127800" "5","Amsterdam","NLD","Noord-Holland","731200" "6","Rotterdam","NLD","Zuid-Holland","593321" "7","Haag","NLD","Zuid-Holland","440900" "8","Utrecht","NLD","Utrecht","234323" Now that data is in CSV format, I can import it with a simple Python script, to make an example it is possible to use the following (refer to instructions to setup a Python environment to connect to Redis). #!/usr/bin/python3 import redis from csv import reader r = redis.Redis(host='127.0.0.1', port=4321, password='') with open('/tmp/city.csv', 'r') as cities: csv_reader = reader(cities) for row in csv_reader: r.hset("city:" + row[0], mapping={ "Name" : row[1], "CountryCode" : row[2], "District" : row[3], "Population" : row[4] }) Let’s check if it was imported correctly: 127.0.0.1:4321> SCAN 0 MATCH city:* COUNT 5 1) "3072" 2) 1) "city:2316" 2) "city:749" 3) "city:570" 4) "city:3625" 5) "city:3328" 6) "city:1771" I can verify the content of a Hash entry as well: 127.0.0.1:4321> HGETALL city:4059 1) "Name" 2) "Cary" 3) "CountryCode" 4) "USA" 5) "District" 6) "North Carolina" 7) "Population" 8) "91213" Export in CSV format using mysqldump Instead of using SELECT INTO OUTFILE (to some it may represent a security issue because it is the server itself to dump the rows to the file system, and this needs secure_file_priv to be enabled), it is possible to export data remotely, using mysqldump backup utility which, acting as a client, does not require any change to server configuration. mysqldump --host=127.0.0.1 --user=root --password --tab=/tmp --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\n' world city Once the file is exported (mysqldump exports two files, table definitions in city.sql file, and the CSV formatted file, city.txt), data can be imported as explained. Import CSV dump with RIOT Assuming you have a CSV dump of your MySQL table (in reality the CSV can proceed from any arbitrary source of data, an Excel spreadsheet, Postgres…) and you don’t want to code any single line to import it into Redis, you can take advantage of RIOT (Redis Input Output Tools), that serve the purpose of migrating heterogeneous sources of data to Redis. Redis Input Output Tools: RIOTRedis Input/Output Tools (RIOT) is a series of utilities designed to help you get data in and out of Redis. In this section I will explore the CSV import utility of RIOT, that is called RIOT File. First, install it (I have a Mac, but refer to the documentation for other installation methods). brew install redis-developer/tap/riot-file Once installed, test the help: (redisvenv) bash-3.2$ riot-file --help The operation couldn’t be completed. Unable to locate a Java Runtime. Please visit http://www.java.com for information on installing Java. And yes, you need also to download a JRE from www.java.com. Once done, go ahead: (redisvenv) bash-3.2$ riot-file import hset --help Usage: riot-file import hset [OPTIONS] Set hashes from input -H, --help Show this help message and exit -s, --separator= Key separator (default: :) -r, --remove Remove key or member fields the first time they are used --ignore-missing Ignore missing fields -p, --keyspace= Keyspace prefix -k, --keys=... Key fields --include=... Fields to include --exclude=... Fields to exclude Now we have to map the CSV source of data to the proper Hash data structure. Our initial CSV file had no header in the first line. If we would like to have it, we could even extract them from MySQL information_schema in the right order as follows. Note: MySQL SELECT INTO OUTFILE does not add a header to the exported dataset, this must be added manually using the proper SQL syntax, or editing the exported CSV file. mysql> SELECT group_concat(COLUMN_NAME ORDER BY ORDINAL_POSITION separator ',') FROM information_schema.COLUMNS WHERE TABLE_NAME='city' AND TABLE_SCHEMA='world'; +-------------------------------------------------------------------+ | group_concat(COLUMN_NAME ORDER BY ORDINAL_POSITION separator ',') | +-------------------------------------------------------------------+ | ID,Name,CountryCode,District,Population | +-------------------------------------------------------------------+ 1 row in set (0.00 sec) And add the line 'ID,Name,CountryCode,District,Population‘ as the first line in the CSV file. But we can still go ahead and do the mapping manually as follows. Here we specify the order of the fields as they come in the CSV file, what keyspace name we want, and what key will complement the keyspace name (e.g. city:1234). riot-file -h 127.0.0.1 -p 4321 import /tmp/city.csv --fields ID Name CountryCode District Population hmset --keyspace city --keys ID Alternatively, if the CSV file has the header obtained from MySQL, we can just add --header to use the CSV header added manually. riot-file -h 127.0.0.1 -p 4321 import /tmp/city.csv --header hmset --keyspace city --keys ID Use RIOT DB to import from MySQL In order to import from MySQL with no intermediate exported dump, you can code your solution including connectors to both the source database and the target Redis database, or simply use RIOT DB. So install it: brew install redis-developer/tap/riot-db Then proceed to import as usual, specifying the keyspace name and the key, and providing the correct connection string. And obviously, the query that defines the dataset to be imported. riot-db -h 127.0.0.1 -p 4321 import "SELECT * FROM city" --url jdbc:mysql://root:Oracle1*@127.0.0.1:3306/world hmset --keyspace city --keys ID Export and do mass insertion in RESP format So far I have explored logical approaches, but for mass insertion, where top speed is required to insert millions of keys, there is a different approach using the pipe mode of redis-cli, which reads data when it is available and send it to the server as soon as possible. In particular, using this method, we will use Redis Protocol, RESP. Read more about mass insertion. It is possible to export a table in RESP format and stream it to the Redis Server in a shot. So let’s see how this works. Using SQL we can generate entries to be sent to Redis Server using this format: * $ $ ... $ Where: args is the number of argumentslenN is the length of the argument that will followargN is the argumentSo assuming that we want to add a Hash for every row in the MySQL table, and every Hash will insert 4 fields, the SQL statement to produce the dump in RESP protocol would be the following: SELECT CONCAT( "*10\r\n", '$', LENGTH(redis_cmd), '\r\n',redis_cmd, '\r\n','$', LENGTH(redis_key), '\r\n',redis_key, '\r\n', '$', LENGTH(hkey1), '\r\n',hkey1, '\r\n','$', LENGTH(hval1), '\r\n', hval1, '\r\n' '$', LENGTH(hkey2), '\r\n',hkey2, '\r\n','$', LENGTH(hval2), '\r\n', hval2, '\r\n' '$', LENGTH(hkey3), '\r\n',hkey3, '\r\n','$', LENGTH(hval3), '\r\n', hval3, '\r\n' '$', LENGTH(hkey4), '\r\n',hkey4, '\r\n','$', LENGTH(hval4), '\r\n', hval4, '\r\n' ) FROM ( SELECT 'HSET' AS redis_cmd, CONCAT('city:',ID) AS redis_key, 'Name' AS hkey1, Name AS hval1, 'CountryCode' AS hkey2, CountryCode AS hval2, 'District' AS hkey3, District AS hval3, 'Population' AS hkey4, Population AS hval4 FROM world.city ) AS t; Save this SQL in a resp.sql file and stream from MySQL into Redis using the pipe mode: bash-3.2$ mysql -h 127.0.0.1 -uroot -p -Dworld --skip-column-names --raw --scan --pattern city:* -i 0.01 | xargs redis-cli -p unlink Wrapping Up You are now able to export a table from MySQL (or other relational database: any database can surely export to CSV, and either RIOT DB or RedisCDC includes connectors to several databases). Connectors supported by RedisCDC are here, connectors supported by RIOT DB are here. And now what? You may be wondering what you can do with those Hashes created in the Redis instance. So in my next post I will share a few example commands to store, retrieve, change, delete, index and search data in Redis. Stick around, I will write about it soon! The post Migrate a MySQL Table to Redis appeared first on mortensi. [Less]

MySQL is an extremely popular open-source RDMS, and it’s widely used by millions of companies and professionals. In this article, you will learn how to check the current MySQL version, and how to update it if necessary. Contents Why do you need to ... [More] know your MySQL version? Different ways how to get your MySQL version […] The post Check Your Current MySQL Version in 6 Ways appeared first on Devart Blog. [Less]

InnoDB Cluster has been around for what feels like a long time. It is the core platform for MySQL High Availability. InnoDB Cluster NOW extends that core feature into a platform that also enables DR support where multiple Disaster Recovery Regions are capable.

After 17 years with MySQL, I wrote a book: Efficient MySQL Performance. I’ll make a bold claim: a MySQL book like this has never been written—not even close. The preface explains why this book is unique:

In this blog post, we will compare the performance of performing a backup from a MySQL database using mysqldump, MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. All these available options are open source and ... [More] free to use for the entire community. To start, let’s see the results of the test. Benchmark Results The benchmark was run on an m5dn.8xlarge instance, with 128GB RAM, 32 vCPU, and 2xNVMe disks of 600GB (one for backup and the other one for MySQL data). The MySQL version was 8.0.26 and configured with 89Gb of buffer pool, 20Gb of redo log, and a sample database of 177 GB (more details below). We can observe the results in the chart below: And if we analyze the chart only for the multi-threaded options: As we can see, for each software, I’ve run each command three times in order to experiment using 16, 32, and 64 threads. The exception for this is mysqldump, which does not have a parallel option and only runs in a single-threaded mode. We can observe interesting outcomes: When using zstd compression, mydumper really shines in terms of performance. This option was added not long ago (MyDumper 0.11.3). When mydumper is using gzip, MySQL Shell is the fastest backup option. In 3rd we have Percona XtraBackup. mysqlpump is the 4th fastest followed closer by mydumper when using gzip. mysqldump is the classic old-school style to perform dumps and is the slowest of the four tools. In a server with more CPUs, the potential parallelism increases, giving even more advantage to the tools that can benefit from multiple threads. Hardware and Software Specs These are the specs of the benchmark: 32 CPUs 128GB Memory 2x NVMe disks 600 GB Centos 7.9 MySQL 8.0.26 MySQL shell 8.0.26 mydumper 0.11.5 – gzip mydumper 0.11.5 – zstd Xtrabackup 8.0.26 The my.cnf configuration:[mysqld] innodb_buffer_pool_size = 89G innodb_log_file_size = 10G Performance Test For the test, I used sysbench to populate MySQL. To load the data, I choose the tpcc method:$ ./tpcc.lua  --mysql-user=sysbench --mysql-password='sysbench' --mysql-db=percona --time=300 --threads=64 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql prepareBefore starting the comparison, I ran mysqldump once and discarded the results to warm up the cache, otherwise our test would be biased because the first backup would have to fetch data from the disk and not the cache. With everything set, I started the mysqldump with the following options:$ time mysqldump --all-databases --max-allowed-packet=4294967295 --single-transaction -R --master-data=2 --flush-logs | gzip > /backup/dump.dmp.gzFor the Shell utility:$ mysqlsh MySQL JS > shell.connect('root@localhost:3306'); MySQL localhost:3306 ssl test JS > util.dumpInstance("/backup", {ocimds: true, compatibility: ["strip_restricted_grants","ignore_missing_pks"],threads: 16})For mydumper:$ time mydumper  --threads=16  --trx-consistency-only --events --routines --triggers --compress --outputdir /backup/ --logfile /backup/log.out --verbose=2PS: To use zstd, there are no changes in the command line, but you need to download the zstd binaries. For mysqlpump:$ time mysqlpump --default-parallelism=16 --all-databases > backup.outFor xtrabackup:$ time xtrabackup --backup --parallel=16 --compress --compress-threads=16 --datadir=/mysql_data/ --target-dir=/backup/ Analyzing the Results And what do the results tell us? Parallel methods have similar performance throughput. The mydumper tool cut the execution time by 50% when using zstd instead of gzip, so the compression method makes a big difference when using mydumper. For the util.dumpInstance utility, one advantage is that the tool stores data in both binary and text format and uses zstd compression by default. Like mydumper, it uses multiple files to store the data and has a good compression ratio.  XtraBackup got third place with a few seconds of difference from MySQL shell. The main advantage of XtraBackup is its flexibility, providing PITR and encryption for example.  Next, mysqlpump is more efficient than mydumper with gzip, but only by a small margin. Both are logical backup methods and works in the same way. I tested mysqlpump with zstd compression, but the results were the same, hence the reason I didn’t add it to the chart. One possibility is because mysqlpump streams the data to a single file. Lastly, for mysqldump, we can say that it has the most predictable behavior and has similar execution times with different runs. The lack of parallelism and compression is a disadvantage for mysqldump; however, since it was present in the earliest MySQL versions, based on Percona cases, it is still used as a logical backup method. Please leave in the comments below what you thought about this blog post, if I missed something, or if it helped you. I will be glad to discuss it! Useful Resources Finally, you can reach us through the social networks, our forum, or access our material using the links presented below: Blog  Solution Briefs White Papers Ebooks Technical Presentations archive Videos/Recorded Webinars Forum Knowledge Base (Percona Subscriber exclusive content) [Less]

CodeIgniter 4 Query Builder class has an update() function used to process UPDATE Data Manipulation Language (DML) commands. Using update() as a standalone function call is perfectly valid. However, there is also a set() function used for setting ... [More] column values as you would with the SET keyword in an SQL UPDATE statement. Used in conjunction with the Query Builder where() function, you can easily UPDATE column values for an individual row or multiple rows. Continue reading for more information… Image by OpenIcons from Pixabay  Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! The Newsletter for PHP and MySQL Developers I’m using a simple ‘employees‘ table (MySQL) having this fictitious data for the examples: All employee information. I have this basic EmployeeModel with an all_emps() method which provides all the data from the ’employees’ table as shown in the screenshot above: Query Builder set(), update, and where() functions I’ve created this updateEmpLastName() method in the Model and use the Query Builder set(), update() and where() functions to edit an employee’s last name: Then we can call the model updateEmpLastName() method in a Controller method I have created named, updateLastName(): You can see in the screenshot below, after calling the Controller updateLastName() method, employee ‘Jessica Rabbit’ now has the last name of ‘Jones’: Updated the last_name column using Query Builder set(), where(), and update() functions. If you find value in the content on my blog and want to support my work, you can toss some spare change in my Tip Jar. Thank you so much!!! Tip Jar Query Builder set() function – multiple calls through chaining Multiple calls to the Query Builder set() function are possible by chaining them one after the other, allowing you to set values on multiple columns at one time. In this next Model method, updateEmpFirstAndLastName(), I chain 2 Query Builder set() functions together; one for the ‘first_name’ column value and one for the ‘last_name’ column: Again, focussing on fictional employee 3, ‘Jessica Jones’, I update both ‘first_name’ and ‘last_name’ columns with new data. Results are shown in the follow-up screenshot: Updated the first_name and last_name columns using Query Builder set(), update(), and where() functions. Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! CodeIgniter 4 Query Builder informational resources Visit the official CodeIgniter 4 documentation on any of the functions covered in the post: As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading. Closing The Query Builder set() function can be used with insert() function calls as well and I plan to cover that use in a future blog post. Be sure to subscribe to my free newsletter, OpenLamp.tech, the newsletter for PHP/MySQL developers, and stay up to date on PHP and SQL-related content. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.The post CodeIgniter 4 Query Builder set() function with update() appeared first on Digital Owl's Prose. [Less]

CodeIgniter 4 Query Builder class has an update() function used to process UPDATE Data Manipulation Language (DML) commands. Using update() as a standalone function call is perfectly valid. However, there is also a set() function used for setting ... [More] column values as you would with the SET keyword in an SQL UPDATE statement. Used in conjunction with the Query Builder where() function, you can easily UPDATE column values for an individual row or multiple rows. Continue reading for more information… Image by OpenIcons from Pixabay  Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! The Newsletter for PHP and MySQL Developers I’m using a simple ‘employees‘ table (MySQL) having this fictitious data for the examples: All employee information. I have this basic EmployeeModel with an all_emps() method which provides all the data from the ’employees’ table as shown in the screenshot above: Query Builder set(), update, and where() functions I’ve created this updateEmpLastName() method in the Model and use the Query Builder set(), update() and where() functions to edit an employee’s last name: Then we can call the model updateEmpLastName() method in a Controller method I have created named, updateLastName(): You can see in the screenshot below, after calling the Controller updateLastName() method, employee ‘Jessica Rabbit’ now has the last name of ‘Jones’: Updated the last_name column using Query Builder set(), where(), and update() functions. If you find value in the content on my blog and want to support my work, you can toss some spare change in my Tip Jar. Thank you so much!!! Tip Jar Query Builder set() function – multiple calls through chaining Multiple calls to the Query Builder set() function are possible by chaining them one after the other, allowing you to set values on multiple columns at one time. In this next Model method, updateEmpFirstAndLastName(), I chain 2 Query Builder set() functions together; one for the ‘first_name’ column value and one for the ‘last_name’ column: Again, focussing on fictional employee 3, ‘Jessica Jones’, I update both ‘first_name’ and ‘last_name’ columns with new data. Results are shown in the follow-up screenshot: Updated the first_name and last_name columns using Query Builder set(), update(), and where() functions. Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! CodeIgniter 4 Query Builder informational resources Visit the official CodeIgniter 4 documentation on any of the functions covered in the post: As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading. Closing The Query Builder set() function can be used with insert() function calls as well and I plan to cover that use in a future blog post. Be sure to subscribe to my free newsletter, OpenLamp.tech, the newsletter for PHP/MySQL developers, and stay up to date on PHP and SQL-related content. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.The post CodeIgniter 4 Query Builder set() function with update() appeared first on Digital Owl's Prose. [Less]

CodeIgniter 4 Query Builder class has an update() function used to process UPDATE Data Manipulation Language (DML) commands. Using update() as a standalone function call is perfectly valid. However, there is also a set() function used for setting ... [More] column values as you would with the SET keyword in an SQL UPDATE statement. Used in conjunction with the Query Builder where() function, you can easily UPDATE column values for an individual row or multiple rows. Continue reading for more information… Image by OpenIcons from Pixabay  Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! The Newsletter for PHP and MySQL Developers I’m using a simple ‘employees‘ table (MySQL) having this fictitious data for the examples: All employee information. I have this basic EmployeeModel with an all_emps() method which provides all the data from the ’employees’ table as shown in the screenshot above: Query Builder set(), update, and where() functions I’ve created this updateEmpLastName() method in the Model and use the Query Builder set(), update() and where() functions to edit an employee’s last name: Then we can call the model updateEmpLastName() method in a Controller method I have created named, updateLastName(): You can see in the screenshot below, after calling the Controller updateLastName() method, employee ‘Jessica Rabbit’ now has the last name of ‘Jones’: Updated the last_name column using Query Builder set(), where(), and update() functions. If you find value in the content on my blog and want to support my work, you can toss some spare change in my Tip Jar. Thank you so much!!! Tip Jar Query Builder set() function – multiple calls through chaining Multiple calls to the Query Builder set() function are possible by chaining them one after the other, allowing you to set values on multiple columns at one time. In this next Model method, updateEmpFirstAndLastName(), I chain 2 Query Builder set() functions together; one for the ‘first_name’ column value and one for the ‘last_name’ column: Again, focussing on fictional employee 3, ‘Jessica Jones’, I update both ‘first_name’ and ‘last_name’ columns with new data. Results are shown in the follow-up screenshot: Updated the first_name and last_name columns using Query Builder set(), update(), and where() functions. Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! CodeIgniter 4 Query Builder informational resources Visit the official CodeIgniter 4 documentation on any of the functions covered in the post: As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading. Closing The Query Builder set() function can be used with insert() function calls as well and I plan to cover that use in a future blog post. Be sure to subscribe to my free newsletter, OpenLamp.tech, the newsletter for PHP/MySQL developers, and stay up to date on PHP and SQL-related content. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.The post CodeIgniter 4 Query Builder set() function with update() appeared first on Digital Owl's Prose. [Less]

CodeIgniter 4 Query Builder class has an update() function used to process UPDATE Data Manipulation Language (DML) commands. Using update() as a standalone function call is perfectly valid. However, there is also a set() function used for setting ... [More] column values as you would with the SET keyword in an SQL UPDATE statement. Used in conjunction with the Query Builder where() function, you can easily UPDATE column values for an individual row or multiple rows. Continue reading for more information… Image by OpenIcons from Pixabay  Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! The Newsletter for PHP and MySQL Developers I’m using a simple ‘employees‘ table (MySQL) having this fictitious data for the examples: All employee information. I have this basic EmployeeModel with an all_emps() method which provides all the data from the ’employees’ table as shown in the screenshot above: Query Builder set(), update, and where() functions I’ve created this updateEmpLastName() method in the Model and use the Query Builder set(), update() and where() functions to edit an employee’s last name: Then we can call the model updateEmpLastName() method in a Controller method I have created named, updateLastName(): You can see in the screenshot below, after calling the Controller updateLastName() method, employee ‘Jessica Rabbit’ now has the last name of ‘Jones’: Updated the last_name column using Query Builder set(), where(), and update() functions. If you find value in the content on my blog and want to support my work, you can toss some spare change in my Tip Jar. Thank you so much!!! Tip Jar Query Builder set() function – multiple calls through chaining Multiple calls to the Query Builder set() function are possible by chaining them one after the other, allowing you to set values on multiple columns at one time. In this next Model method, updateEmpFirstAndLastName(), I chain 2 Query Builder set() functions together; one for the ‘first_name’ column value and one for the ‘last_name’ column: Again, focussing on fictional employee 3, ‘Jessica Jones’, I update both ‘first_name’ and ‘last_name’ columns with new data. Results are shown in the follow-up screenshot: Updated the first_name and last_name columns using Query Builder set(), update(), and where() functions. Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! CodeIgniter 4 Query Builder informational resources Visit the official CodeIgniter 4 documentation on any of the functions covered in the post: As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading. Closing The Query Builder set() function can be used with insert() function calls as well and I plan to cover that use in a future blog post. Be sure to subscribe to my free newsletter, OpenLamp.tech, the newsletter for PHP/MySQL developers, and stay up to date on PHP and SQL-related content. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.The post CodeIgniter 4 Query Builder set() function with update() appeared first on Digital Owl's Prose. [Less]