CODEWITHSUNDEEP
mysqlmacoscommand-line
alexw
alexw

Reputation: 8668

running a SQL query in a loop from the command line

I have a fairly complex query that I need to run a few thousand times, with the parameter taken from the values in another table's column. It is too complex to write as a simple JOIN in any sane way.

Each execution of the query takes about 6 seconds, so writing a PHP script to perform this loop would probably just crash my web server. Is there a way I can do this in the command line in OSX? (I realize this is starting to get into Superuser territory, should I be asking it there instead?).

What I'd like to do is:

  1. Run a query to fetch the values that I need from table1.columnA.
  2. Store those values (valueA) to an array of some sort.
  3. Iterate over this array, and execute my complex query on each value:

INSERT INTO table2 (id, :valueA) SELECT id from table3 WHERE id NOT IN (COMPLEX_SUBQUERY) AND tableXYZ.value = :valueA

The best solution seems to be doing this in a shell script of some sort, but frankly Unix scripting is still somewhat of black magic to me. I'm much more comfortable scripting in PHP, but I imagine Apache will freak out if I try to do this as a PHP script.

Upvotes: 0

Views: 1061

Answers (1)

pala_
pala_

Reputation: 9010

I can't comment, but I can let you know that you're perfectly able to use php as a command line scripting language, without the webserver being involved at all.

Try it from the cli: php script.php

Upvotes: 0

Related Questions