[set page_title]Search and replace a database field[/set] [set ui_class]Admin[/set] [set page_banner]Search and Replace: Mass-update data in a table[/set] [set page_perm]tables[/set] [set help_name]table.searchreplace[/set] [set icon_name]icon_config.gif[/set] [tmp meta_header] [include include/table_populator] [/tmp] [seti ui_body_extra][/seti] @_UI_STD_HEAD_@
1. Choose a table to operate on: [if !cgi area_table] [value name=area_table set="[either][cgi mv_data_table][or]__ProductFiles_1__[/either]" hide=1] [value name=area_column set=category hide=1] [/if]
2. Choose a column to operate on:
3. Set what to search for in that column: Case-sensitive? [display name=case_sensitive type=yesno cgi-default=1]
(Exact text match or regular expression)
4. Choose the replacement string
(Exact text to be inserted instead of search value. Replaces parenthisized text if parentheses are used.)
5. Click this button to do it: [button text="Search and Replace"] [flag type=write table="[cgi area_table]"] [seti ui_failure] [try] [perl tables="[cgi area_table]"] my $go_page = $CGI->{mv_nextpage}; $CGI->{mv_nextpage} = '@@MV_PAGE@@'; my $tab = $CGI->{area_table} or return "no source table"; my $db = $Db{$tab} or return "no source table"; my $key = $db->config('KEY'); my $col = $CGI->{area_column} or return "no column specified"; for(\$key, \$col) { $$_ = lc($$_); return "bad column name '$$_'" if $$_ =~ /\W/; } $col ne $key or return "No search and replace allowed on primary key field."; my $q = "select $key, $col from $tab"; my $ary = $db->query($q) or return qq{query "$q" failed}; $Scratch->{try_message} = "Compiling regex"; my $sf = $CGI->{search_for}; my $rw = $CGI->{replace_with}; my $cs = $CGI->{case_sensitive}; my $regex; if($sf =~ m{(^|[^\\])\(} and $sf =~ m{[^\\]\)}) { #Debug("regex with parens, '$sf'"); $sf =~ s/(.*?)\((.*)\)(.*)/($1)($2)($3)/; #Debug("regex with parens, now '$sf'"); $regex = $cs ? qr{$sf} : qr{(?i)$sf}; } else { #Debug("regex without parens, '$sf'"); $regex = $cs ? qr{()($sf)()} : qr{(?i)()($sf)()}i; #Debug("regex without parens, now '$sf'"); } delete $Scratch->{try_message}; my @set; for(@$ary) { next unless $_->[1] =~ s{$regex}{$1$rw$3}; push @set, $_; } my @messages; my $count = 0; if(@set) { my $settor = $db->field_settor($col); for(@set) { my $status = $settor->(@$_); ($count++, next) if $status; push @messages, qq{failed to set key=$_->[0] col=$col to '$_->[1]'}; } } else { @messages = qq{'$sf' not found in column $col}; } $CGI->{mv_nextpage} = $go_page; $CGI->{mv_data_table} = $tab; unshift @messages, "Updated $count rows."; $Scratch->{ui_message} = join "
", @messages; return; [/perl] [/try] [/seti] [catch] [seti ui_failure]Error: [scratch try_message][/seti] [/catch] [/button]
@_UI_STD_FOOTER_@