Mass rows copying (duplicating) with filed customization – MySQL

Let’s suppose we have a table with the following structure and data:

# `table`
id | a | b | c |
1 | “aaa” | “xxx” | “ccc”
2 | “aab” | “yyy” | “ccc”
3 | “aba” | “yyy” | “ccc”
4 | “abc” | “xxx” | “ccc”
5 | “dcz” | “xxx” | “eee”

Now, we want to copy some records (only some columns) to the same table AND change some of them with a fixed value.
– duplicate all the rows with `b` equal to “xxx”
– for the new rows inserted, the value of `c` has to be changed to “ddd” (fixed value!)
– `id` is the primary key, auto increment

Query (pay attention to the quote characters):

NULL as `id`,
“ddd” as `c`
FROM `table`
WHERE `b`=”xxx”)


id | a | b | c |
1 | “aaa” | “xxx” | “ccc” #row1
2 | “aab” | “yyy” | “ccc”
3 | “aba” | “yyy” | “ccc”
4 | “abc” | “xxx” | “ccc” #row4
5 | “dcz” | “xxx” | “eee” #row5
6 | “aaa” | “xxx” | “ddd” #copied from #row1
7 | “aab” | “xxx” | “ddd” #copied from #row4
8 | “dcz” | “xxx” | “ddd” #copied from #row5

Note 1:
The insert operation by using a subquery is not an ‘atomic’ operation for the recent versions of MySQL. If there are table constraints and a new record is not accepted (e.g: duplicate record for a key defined on columns), only that record won’t inserted (not all of them!).
Example: If before there was a record with the same values as in line 9 and there was a unique key on columns (a,b,c), the query would insert only rows on line 6,7 and 8 (9 fails).
Some old version of MySQL stop execution in case of duplicate entries. In this case, add IGNORE to the query to skip duplicates: INSERT IGNORE INTO
Note 2:
If you add a new column to the table, the query will fail !!
Tested on MySQL 5.1.36

How to recursively check syntax of PHP files

The executable of PHP supports the ‘-l’ option, that checks the syntax instead of parsing the file.
Using the command ‘find’, it’s possibile to do a interesting operation: syntax checking of all the files recursively, to avoid parse errors in some script !!

find ./ -type f -name *.php -exec php -l {} ;

the result will be a list of files, example:

No syntax errors detected in ./codebase/controller/
No syntax errors detected in ./codebase/controller/
Errors parsing ./codebase/controller/site/
No syntax errors detected in ./codebase/controller/

We can improve the script and print only the file with suntax errors using ‘grep’

find ./ -type f -name *.php -exec php -l {} ; | grep “Errors parsing “;

To launch it from a PHP script

passthru(‘find ./ -type f -name *.php -exec php -l {} ; | grep “Errors parsing ” ‘);

Updated: To skip .svn directories add the option :
-not -regex ‘.*/.svn/*.*’