Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Moving Data from One Column to Another

Quote Reply
Moving Data from One Column to Another
Greetings All!

I'm trying to tidy things up in my database - as when I first started I didn't really organise things all that well (as a fairly green novice!).

I originally created a range of different columns eg. one each for 'pps', 'ppl', 'ppc' etc ... Each one of these has a value of either 'Yes' or 'No'.

In hindsight, it would have been far better to have one column called 'Program Type' and filled it with multiple values (via checkboxes or similar).

Is there a way to do this - I am assuming using mySQL Monitor or similar where I can eg. where pps eq 'Yes' insert into 'Program_Type' 'pps' and then do that again for each of the other columns?

I could then delete the redundant columns - thus reducing my Admin Database page to a readable size:-))

Any ideas would be appreciated.


Regards,


Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] Moving Data from One Column to Another In reply to
Hi,

I don't think there is a really good (or easy) way to do it with a MySQL query.

You could try something like this:

Code:
#!/usr/local/bin/perl

use strict;
use CGI::Carp qw(fatalsToBrowser);
use lib './';
use Links qw/$DB $IN $USER $CFG/;

local $SIG{__DIE__} = \&Links::fatal;

Links::init('./');
Links::init_user();

my $test = 1;

print $IN->header();


my $tbl = $DB->table('Links');

my @fields = split / /, qq|pps ppl ppc|;
my $dest_field = "Field_To_Go_In";

my $sth = $DB->table('Links') || die $GT::SQL::error;

while (my $hit = $sth->fetchrow_hashref) {

my @for_joining;
foreach (@fields) {
if ($hit->{$_} =~ /yes/i) {
push @for_joining, $_;
}
}
my $tmp = join("\n",@for_joining);

print qq|Updating $hit->{ID} - for field "$dest_field" - with value: $tmp \n\n|;

if ($test < 1) {
$DB->table('Links')->update( { $dest_field => $tmp } , { ID => $hit->{ID} } ) || die $GT::SQL::error;
} else { print qq|Didn't actually update, as \$test = 1| }

}
Totally untested - as I don't have any of those fields =)

What the script should do, is go through the fields held in @fields, and put them into a single field. For example, if we have the following setup for a link:


Code:
ID => 1234
Field_To_Go_In => NULL
pps => No
ppl => Yes
ppc => Yes

Then, once the script is run - it should look more like:



Code:
ID => 1234
Field_To_Go_In => ppl
ppc
pps => No
ppl => Yes
ppc => Yes

I would suggest making a totally new field - just to make sure its doing what you want :)

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Clint] Moving Data from One Column to Another In reply to
Hi,

Actually, thinking about it - you could probably do it via MySQL commands =)

Code:
UPDATE glinks_Links SET Field_to_Go_In = Field_to_Go_In + "ppl\n" WHERE ppl = "Yes"

Obviously update the bits in red :) [again - untested - so be sure to backup before running anything =))

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Moving Data from One Column to Another In reply to
Quote:
my @fields = split / /, qq|pps ppl ppc|;

Using split above is pointless when you can just do...

Code:
my @fields = qw|pps ppl ppc|;

Also if each column is either Yes or No as described then it's much more efficient to use:

if ($hit->{$_} eq 'Yes') {
Quote Reply
Re: [Wychwood] Moving Data from One Column to Another In reply to
Hi,

Quote:
my @fields = qw|pps ppl ppc|;

Very true - was a bit early in the morning =)

Quote:
if ($hit->{$_} eq 'Yes') {

True again - although I'm not sure it would matter too much in this case, as it is only a pretty small site (around a few thousand listings) - so there wouldn't be a noticeable speed difference (nor would the script need to be run more than a couple of times, one for testing - and the other for doing the job)

Good feedback though Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Moving Data from One Column to Another In reply to
Hi Andy:

I prefer the mySQL commands - however I ran it and nothing seems to get inserted into the new column. I'm certain the column type is set up okay. This is what I ran:-

Code:
UPDATE glinks_Links SET Program_Type = Program_Type + "Pay Per Sale\n" WHERE pps = "Yes"
The only value inserted into 'Program_Type' is 'NULL'.

Any ideas?

Thanks for your help ....

Regards,


Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] Moving Data from One Column to Another In reply to
Hi,

Try:

Code:
SELECT COUNT(*) FROM glinks_Links WHERE pps = "Yes"

Does that give you any results? It could be you are using 1 = yes, 0 = no - in which case, you would need to use something like:

Code:
UPDATE glinks_Links SET Program_Type = Program_Type + "Pay Per Sale\n" WHERE pps = 1

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Moving Data from One Column to Another In reply to
Hi Andy:

Yes - 5233.

I've physically looked at the column and it contains either 'Yes' or 'No'. (not using 0 or 1 - another beginner mistake:-))

Thanks,

Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] Moving Data from One Column to Another In reply to
Hi,

Mmm.. odd. Have you tried my script? If you are wanting to have stuff like "Pay Per Click", instead of "ppc", try the below script. Just update $dest_names, to add in new types as you need it :)

Code:
#!/usr/local/bin/perl

use strict;
use CGI::Carp qw(fatalsToBrowser);
use lib './';
use Links qw/$DB $IN $USER $CFG/;

local $SIG{__DIE__} = \&Links::fatal;

Links::init('./');
Links::init_user();

my $test = 1;

print $IN->header();


my $tbl = $DB->table('Links');

my @fields = qq|pps ppl ppc|;

my $dest_names;
$dest_names->{pps} = "Pay Per Sale";
$dest_names->{ppl} = "Pay Per Lead";
$dest_names->{ppc} = "Pay Per Click";

my $dest_field = "Program_Type";

my $sth = $DB->table('Links') || die $GT::SQL::error;

while (my $hit = $sth->fetchrow_hashref) {

my @for_joining;
foreach (@fields) {
if ($hit->{$_} eq "Yes") {
push @for_joining, $dest_names->{$_};
}
}
my $tmp = join("\n",@for_joining);

print qq|Updating $hit->{ID} - for field "$dest_field" - with value: $tmp \n\n|;

if ($test < 1) {
$DB->table('Links')->update( { $dest_field => $tmp } , { ID => $hit->{ID} } ) || die $GT::SQL::error;
} else { print qq|Didn't actually update, as \$test = 1| }

}

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Moving Data from One Column to Another In reply to
Thanks Andy:

Gave it a go and got the following error:

Code:
GT::SQL::Table (22536): Unknown method 'fetchrow_hashref'
called at /path/to/script.cgi line 33.

Regards,

Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Andy] Moving Data from One Column to Another In reply to
Surely all you need is something like:

Quote:
UPDATE glinks_Links SET Program_Type = "Pay Per Sale" WHERE pps = 'Yes'

Last edited by:

Wychwood: Oct 4, 2007, 8:50 AM
Quote Reply
Re: [Wychwood] Moving Data from One Column to Another In reply to
Hi:

Thanks for this, however because I need to do this a couple of times, in other words, the Program_Type column, may end up having 'Pay Per Sale', 'Pay Per Lead' and 'Pay Per Click' in that column, when I do this for each column, it only overwrites the new data in the column and doesn't add to it. eg.

Code:
UPDATE glinks_Links SET Program_Type = "Pay Per Sale" WHERE pps = 'Yes'
UPDATE glinks_Links SET Program_Type = "Pay Per Click" WHERE ppc = 'Yes'
So what I am after is when there are entries of 'Yes' in both 'pps' and 'ppc' then 'Pay Per Sale' and 'Pay Per Click' are both added to that category.

Regards,


Clint



Regards,

Clint
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] Moving Data from One Column to Another In reply to
Try CONCAT, eg...

Quote:
UPDATE glinks_Links SET Program_Type = CONCAT(Program_Type, "Pay Per Sale") WHERE pps = 'Yes'
UPDATE glinks_Links SET Program_Type = CONCAT(Program_Type, "Pay Per Click") WHERE ppc = 'Yes'

etc.
Quote Reply
Re: [Wychwood] Moving Data from One Column to Another In reply to
Thanks for the suggestion.

Tried that, but it doesn't insert any data in the column at all.

Regards,

Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] Moving Data from One Column to Another In reply to
You will need a few different queries based on whether the data will span multiple lines or not. This assumes you use a multi-select box to display the values in the Admin side and a varchar/char column type, and there are 3 values (pps, ppl and ppc):

Code:
# update where PPS is only value
UPDATE glinks_Links SET Program_Type = 'PPS' WHERE pps = 'Yes' AND ppl='No' AND ppc='No'

# update PPS where other values will be concatenated later
UPDATE glinks_Links SET Program_Type = CONCAT(Program_Type, 'PPS\n') WHERE pps = 'Yes' AND (ppl='Yes' OR ppc='Yes')

The logic for the other queries is pretty complicated but this should get you on the right track.

Last edited by:

aus_dave: Oct 7, 2007, 7:44 PM
Quote Reply
Re: [aus_dave] Moving Data from One Column to Another In reply to
Thanks for the suggestion Dave,

but I tried this and it didn't insert any data at all.

Regards,

Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory
Quote Reply
Re: [Clint] Moving Data from One Column to Another In reply to
It must be something to do with your table setup, MySQL version etc.

Can you post a sample SQL file with some of your data (say 50 records or so)?
Quote Reply
Re: [aus_dave] Moving Data from One Column to Another In reply to
Hi Dave:

I've sorted the problem out. Whenever I create a column in Links it puts into the table the value of 'NULL'

I used this Command in MySQL Monitor:

Code:
UPDATE glinks_Links SET Program_Type = ''

and then followed your instructions and it worked perfectly. Strange ...

Thanks again,

Regards,


Clint.
--------------------------
http://AffiliatesDirectory.com
The Affiliate Programs Directory