How can I pass the result set of filenames from an MySQL query to zip in Linux

Question

My problem is pretty straight foward, and I think I'm close but need a boost in the right direction.

I have a MySQL table that contains a lot of file names that are used by a website. These files all exist in a directory images. What I'm trying to achieve is to be able to SELECT some of those file names based on various conditions, and then have those files, and only those files added to a .zip file.

I realized right away that I was having problems with file names that had spaces in them. So I modified my SQL query such that the filenames would be quoted. So what I've ended up with was:

zip sample-images $(mysql -u username -pPassword -e "SELECT CONCAT('"', ifnull(products_image, ''), '"'), CONCAT('"', ifnull(products_image_2, ''), '"').... WHERE condition=true" db_name --silent --raw)

This outputs a list of all the filenames I'm looking for in the database (along with empty "" entries for the null values), however the result for every line is zip warning: name not matched: "filename.jpg".

Can anybody help me out with this?

UPDATE I believe the issue is something with file name escaping. I've escaped the double-quotes in the query such that the CONCAT's have become CONCAT('\"', ifnull(products_image, ''), '\"'),.... but still have the same result.

Answer 1

Something like this should work:

mysql -u username -pPassword -e "SELECT CONCAT('\"', products_image, '\"'), CONCAT('\"', products_image_2, '\"') WHERE condition=true AND products_image IS NOT NULL && products_image_2 IS NOT NULL" db_name --silent --raw --skip-column-names | zip myzipfile.zip -@

Although I can't test it right now.

Answer 2

I would take out the quotes you've added to the SQL and do something like this:

mysql -u username -pPassword -e \
"SELECT products_image,products_image_2
WHERE condition=true
AND products_image IS NOT NULL && products_image_2 IS NOT NULL" \
db_name --silent --raw --skip-column-names |
while read FileName
do
zip sample-images "$FileName"
done

I've split the line and put in \ to make it easier to read, but you can join it back together if you wish. Also, if you prefer you can put the shell code on one line:

mysql ... | while read FileName; do zip sample-images "$FileName"; done

It's just not as easy to read.