How do I enter the variable value of my bash command into MySQL?

Question

The following code extracts all the domain names from a website and sets them to the value of $domain from a httrack data stream.

domain=$(httrack --skeleton http://www.ilovefreestuff.com -V "cat \$0" | grep -iEo '[[:alnum:]-]+\.(com|net|org)')

The value of $domain looks like this...

googlesyndication.com facebook.com facebook.com ilovefreestuff.com ilovefreestuff.com facebook.com facebook.com ilovefreestuff.com ilovefreestuff.com peadig.com facebook.net ilovefreestuff.com w3.org ilovefreestuff.com yoast.com ilovefreestuff.com

I have my database setup and this command works perfectly.

mysql -pPASSWORD -e "INSERT INTO domains.domains (domains) VALUES ($domain)”

I’m trying to insert each individual domain within the variable $domain into my MySQL database on the fly from the httrack data stream within one combined command line. So in my crazy mind it should look something similar to below… Unfortunately this doesn’t work. I get no output, just another bash prompt.

domain=$(httrack --skeleton http://www.ilovefreestuff.com -V "cat \$0" | grep -iEo '[[:alnum:]-]+\.(com|net|org)') | mysql -pPASSWORD -e "INSERT INTO domains.domains (domains) VALUES ($domain)"

I’m not sure how to cut up the $domain variable into individual domains so I can enter one domain per MySQL data cell, and I’m not certain how to pipe the result to MySQL given that the command is a data stream. Maybe I need a for loop and a cut command?

Answer 1

Maybe I need a for loop and a cut command?

You said it. It won’t be a one-liner but this should work:

domain=$(httrack --skeleton http://www.ilovefreestuff.com -V "cat \$0" | grep -iEo '[[:alnum:]-]+\.(com|net|org)')

for single_domain in $domain
do
  mysql -pPASSWORD -e "INSERT INTO domains.domains (domains) VALUES ($single_domain)"
done