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.


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.

  • Same thing, though the output is all on one line per-row returned. Though I do prefer the piped style you suggest better than the sub-shell method I was using. – nathangiesbrecht Jan 7 '15 at 22:46

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
zip sample-images "$FileName"

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.

  • I will need the quotes for filenames with spaces in them though, no? – nathangiesbrecht Jan 8 '15 at 16:58
  • Yes you will need quotes, hence the quotes in zip images "$FileName". – Philip Kearns Jan 13 '15 at 10:12

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.