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.
Requirements:
- 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):
INSERT INTO `table`
(SELECT

NULL as `id`,

`a`,

`b`,

"ddd" as `c`

FROM `table`

WHERE `b`="xxx")

Result:

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/competition.inc.php
No syntax errors detected in ./codebase/controller/feed_data.inc.php
Errors parsing ./codebase/controller/site/contact.inc.php
No syntax errors detected in ./codebase/controller/compare_prices.inc.php

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/*.*'