[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_@ [form-session-id]

[calc]++$numbering[/calc]. | Choose a table to operate on: | [if !cgi area_table] [value name=area_table set="[either][cgi mv_data_table][or]__ProductFiles_0__[/either]" hide=1] [value name=area_column set=category hide=1] [/if]
[calc]++$numbering[/calc]. | Choose a column to operate on: |
[calc]++$numbering[/calc]. | 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)
[calc]++$numbering[/calc]. | Choose the replacement string | Replace all occurences ? [display name=replace_all type=yesno cgi-default=1] (Exact text to be inserted instead of search value. Replaces parenthisized text if parentheses are used.)

[calc]++$numbering[/calc].

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; if ($CGI->{replace_all}) { for(@$ary) { next unless $_->[1] =~ s{$regex}{$1$rw$3}g; push @set, $_; } } else { 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_@