Skip navigation.

Shell Scripting with PHP...

Hall of Fame | Bad Architecture

David sent this in last week. It is part of a database migration shell script written in PHP. It looks like the script moves data from one table to another. Of course it does it in one of the most worst ways imaginable.

= $sconn->prepare($sql);
       $res = $sconn->execute($sth);

       $fp = fopen("./tmp.emaillist","w");
       while($tmp = $res->fetchRow()){
               print "hi";
               $data = "$tmp[0]\t$tmp[1]\t$tmp[2]\t$tmp[3]\t$tmp[4]\t$tmp[5] ... \t$tmp[56]\t$tmp[57]\t$tmp[58]\n";
       $cmd = "mysql -e \"load data local infile './tmp.emaillist' into table account\" --password=$dpass --database=$ddb";

I had to trim down the $data = " ... " line so that it fits. It was really, really long and fairly pointless. A few of things that make this WTF exceptionally bad:

  1. The use of an OOP DB abstraction layer. There must be an include(), or a require() somewhere that pulls in the library. Not grossly bad, but PHP shell scripts should be kept as individual as possible.
  2. Dumping DB data into an external tab separated file first...
  3. then running another shell command to read the data into the database.
  4. Using "LOAD DATA LOCAL INFILE", it's use is discouraged, and disabled by default. That likely explains why the programmer had to dump to a text file and run the MySQL CLI client to get LOAD DATA INFILE to work. I smell workaround. Yuck!

A better way to do this? Perhaps using a single INSERT ... SELECT from MySQL.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

hm... 1. does not make any se

hm... 1. does not make any sense to me

I should not use a DB abstraction layer with shell scripts because they "should be kept as individual as possible"?

What does that mean? And who says that? And, more important, why?

IMO if a PHP cli needs a database there is no reason not to use an abstraction layer there, too.

I prefer PHP shell scripts to

I prefer PHP shell scripts to have as few dependancies as possible. This makes them a little easier to move and use on various servers without having to modify the code. For throw-away shell scripts it doesn't really make much difference. For shell scripts that will likely have a permanent life I think it is more important to write them as protected against external changes as much as possible.

Since most of the permanent shell scripts are usually for cron purposes (in my case at least), I generally forget about their requirements. Having them tied into a database library means there is one more thing that might go wrong if something is changed or updated.

I just prefer to program defensively and keep my systems as independant as possible, especially low level things designed to run automatically behind the scenes.

Silly. The fastes and best w

Silly. The fastes and best way to actually _do_ this is via shell script, if you feel like writing it, but mysqldump --database [databases here] --add-drop-table -u[username] -p[password] > file.sql
and then import it back into another db with mysql -D [db name] -u[username] -p[password] < filename.sql
There... Solved... No PHP :)

How is that faster than using

How is that faster than using an INSERT ... SELECT query in the MySQL CLI? If we're nit-picking, it is even faster to pipe the two of them together: mysql_dump --opt --add-drop-table -u[username] -p[password] [database] | mysql -u [username] -p [database]

bah... You're right... didn't

bah... You're right... didn't thought about piping... my bad...