ansaurus

Question

Answer 1

A:

IF you have all the data already and you're just wondering how to output it in columns you should look at sprintf and printf which allow you to format output strings.

Dave Webb 2009-07-29 09:39:32

I have the data in two separate tables within MYSQL, i need to the match up the positions of both the tables and print the symbols for the corresponding position. I also want to print out the entire list of positions which is a column in main_table.

2009-07-29 09:53:13

So I guess that means that you know neither Perl nor SQL?

innaM 2009-07-29 12:22:19

Answer 2

+1 A:

It sounds like you need to do a join operation in your SQL query, but you'll need some kind of relationship in order for this to work properly. You might be able to figure out what you need using the MySQL reference manual's section on JOIN syntax.

On the Perl side you'll need to write the logic for your output. I would recommend making a hash, using the "position" as your key and then any symbols as values. Fill the hash first, then do your output. It would simplify your process for outputting your query the way you would like.

Weegee 2009-07-29 13:33:21

Answer 3

A:

use strict;
use DBI;

my %ucsc;

my $dbh  = DBI->connect('DBI:mysql:disc1pathway;user=home;password=home');
my $dbs  = DBI->connect('DBI:mysql:results;user=home;password=home');

my $main = $dbh->prepare("select chr, position from main_table");
$main->execute();

my $q1 = $dbs->prepare("select position, symbol, chrom from query1");
$q1->execute();


while (my $main_ref = $main->fetchrow_hashref()) {
    $ucsc{$main_ref->{chr}}{$main_ref->{position}} = 1;
}

while (my $gene_ref = $q1->fetchrow_hashref()) {
    my $q1position = $gene_ref->{position};
    my $q1symbol   = $gene_ref->{symbol};
    my $q1chr      = $gene_ref->{chr};

    foreach my $ucsc (keys %{$ucsc{$q1chr}}) {
        print "$ucsc $q1symbol\n";
    }
}

$dbh->disconnect();
$dbs->disconnect();   

exit (0);

=====================================================================================

The above code just lists the position and the symbol, but does not match them up. I cant seem to get my head around on how to match them up. Any suggestions.

Thanks. Caren

2009-08-03 09:06:49

Answer 4

A:

Weegee has the right answer, you can specify the location of a table like this: ipaddress.database.table. If you are on the same machine you can drop the ipaddress portion, and if you are in the same database you can drop the database portion. So your code should wind up looking like:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect(
    'DBI:mysql:disc1pathway',
    "home",
    "home",
    {
        ChopBlanks       => 1,
        AutoCommit       => 1,
        PrintError       => 0,
        RaiseError       => 1,
        FetchHashKeyName => 'NAME_lc',
    }
) or die "could not connect to database: ", DBI->errstr;

my $sth = $dbh->prepare("
    SELECT
        disc1pathway.main_table.chr,
        disc1pathway.main.position,
        results.query1.symbol,
        results.query1.chrom
    FROM disc1pathway.main_table, results.query1
    JOIN results.query1 ON (
        disc1pathway.main_table.position = results.query1.position
    )
");        

$sth->execute;

while (my $col = $sth->fetchrow_hashref) {
    print join(" ", @{$col}{qw/chr position symbol chrom/}), "\n";        
}

$sth->finish;

$dbh->disconnect;

Chas. Owens 2009-08-03 14:30:45

ansaurus

tags:

views:

answers:

Matching up MySQL tables using Perl

related questions