Hello all for the record I have the Perfect Server Debian 8 LAMP version with MySQL 5.6.39 In my MySql server I have about 150 databases that start with p_. These are databases for cloned sites. Is it possible to create a bash script that I can run when I need to that will: 1) log into mysql as root 2) loop through the "p_" databases 3) run some sql queries (which will include a join with the main database) 4) close the selection
This is what I have so far. I want to know that it should work and if not what should I change. # list of all databases all_dbs="$(mysql -u $USER -p$PASS -Bse 'SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'p\_%')" for db in $all_dbs do mysql -u$USER -p$PASS $db -sN -e "UPDATE products pt JOIN mymaindb.products st ON st.products_id=ft.products_id SET pt.products_price_w=st.products_price;" done markup="$(mysql -u $USER -p$PASS -Bse 'SELECT configuration_value FROM configuration WHERE configuration_key=\'STORE_MARKUP\';')" rounding="$(mysql -u $USER -p$PASS -Bse 'SELECT configuration_value FROM configuration WHERE configuration_key=\'STORE_ROUNDING\';')" if $rounding=1 then mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=TRUNCATE((products_price_w *($markup + 1)), 2)" elif $rounding=2 then mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=((TRUNCATE((products_price_w *($markup + 1)), 0)) - 0.01)+1" else mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=((TRUNCATE((products_price_w *($markup + 1)), 0))+1; )" fi Will the $markup variable work or do I need to do it like PHP? I know the actually SQL statements work like they should
It is not possible to make sence of your script when line breaks are messed up. Put the script in code tags to preserve the line breaks. You can test your script by putting the commads in echo " " statements. That way they are not executed, just written on screen and you can see what would happen. And put Code: #!/bin/bash as first line to make it a bash script.
Code: #!bin/bash #Script to update peturncatalog pricing all at once USER="user" PASS="pass" # list of all databases all_dbs="$(mysql -u $USER -p$PASS -Bse 'SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'p\_%')" for db in $all_dbs do mysql -u$USER -p$PASS $db -sN -e "UPDATE products pt JOIN foreverpets.products st ON st.products_id=ft.products_id SET pt.products_price_w=st.products_price;" done markup="$(mysql -u $USER -p$PASS -Bse 'SELECT configuration_value FROM configuration WHERE configuration_key=\'STORE_MARKUP\';')" rounding="$(mysql -u $USER -p$PASS -Bse 'SELECT configuration_value FROM configuration WHERE configuration_key=\'STORE_ROUNDING\';')" if $rounding=1 then mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=TRUNCATE((products_price_w *($markup + 1)), 2);)" elif $rounding=2 then mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=((TRUNCATE((products_price_w *($markup + 1)), 0)) - 0.01)+1;)" else mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=((TRUNCATE((products_price_w *($markup + 1)), 0))+1; )" fi
If you are testint the script, I would put all_dbs = "onedb seconddb" and take a backup of those two databases before testing. If you have two databases that can be broken, even better. But this way you can test the script logic and only have to restore two databases if something goes wrong. When test runs OK you can put back the old all_dbs line. I propose adding these three lines at the beginning: Code: #!bin/bash #Script to update peturncatalog pricing all at once set -o nounset set -o errexit set -x USER="user" PASS="pass" See for example https://www.davidpashley.com/articles/writing-robust-shell-scripts/ for their meaning. The set -x makes every command echo on the standard output, so you can see what the script is doing. When you have finished testing comment out the set -x -line. I trust you have checked those mysql commands on the command line to see they return what you expect them to return?
And the better this python is php ... In this case? Another question, advise a text editor, which is better Sublime Text or Atom Edit
If you suggest Python or PHP are better tools for writing this script, you can prove your point by supplying a version in your preferred programming language. I you just say the script can be written in some other language, I can say it can be written in Cobol. I do not want to take part in text editor wars. I use Emacs, and like it. https://en.wikipedia.org/wiki/Editor_war I suggest to everyone to use the editor that you know how to use. If there really is a need to ask question which is better. you have to define the criteria by which to evaluate the "betterness".
