Sir / Madam,
I would like to map and extract common numbers between 5 column (i.e.Start) and 6th column (End) for refFlat table with 2nd column (Start) and 3rd column (End) for mirna table. Could you please modify the script attach here. I would be glad for your support.

With regards,
Rocky
Seoul, Korea


This is a simple script to extract Start and End column.

#!/usr/bin/perl

use strict;
use DBI;

my $user = 'root';
my $password = '1004';

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mirvar", $user, $password) || die "Failed connect DB : $!\n";
my $sql;

$sql = "select * from mirna limit 10";

my $sth = $dbh->prepare($sql);
$sth->execute || die "Error! : $sql\n";

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

   my $start = $mirna->{'Start'};
   my $end = $mirna->{'End'};
  
   print "$chr\t$start\t$end\n";
#!/usr/bin/perl

use strict;
use DBI;

my $user = 'root';
my $password = '1004';

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mirvar", $user, $password) || die "Failed connect DB : $!\n";
my $sql;

$sql = "select * from mirna, refFlat limit 10";

my $sth = $dbh->prepare($sql);
$sth->execute || die "Error! : $sql\n";

while( my $mirna && $refFlat = $sth->fetchrow_hashref() ){

   my $chr = $mirna->{'chr'};
   my $start = $mirna->{'Start'};
   my $end = $mirna->{'End'};

   my $chr = $refFlat->{'chr'};
   my $start = $refFlat->{'Start'};
   my $end = $refFlat->{'End'};
   
   print "$chr\t$start\t$end\n";
}

Recommended Answers

All 10 Replies

Sir/ Madam,

I would like to compare columns in tables in mysql database. From dbsnp table start and end columns with mirna table start and end. These are genomic positions in chromosome. I would like to extract common START and END positions by reading row by row. I have written a script attach here . Could you please modify this error.

#!/usr/bin/perl

use strict;
use DBI;

my $user = 'root';
my $password = '1004';

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mirvar", $user, $password) || die "Failed connect DB : $!\n";

my $sql;

$sql = "select d.chr, m.Start as m_start, m.End as m_end, d.Start as d_start, d.End as d_end from mirna as m, dbsnp as d limit 10000";
my $sql1 = "select * from mirna limit 10";
my $sql2 = "select * from dbsnp limit 10";


my $sth = $dbh->prepare($sql);

my $sth1 = $dbh->prepare($sql1);
my $sql1 = "select * from mirna limit 10";
my $sql2 = "select * from dbsnp limit 10";


my $sth = $dbh->prepare($sql);

my $sth1 = $dbh->prepare($sql1);
my $sth2 = $dbh->prepare($sql2);

$sth1->execute || die "Error! : $sql\n";
$sth2->execute || die "Error! : $sql\n";

$sth->execute || die "Error! : $sql\n";

while ( my $line1 = $sth1->fetchrow_hashref() ) {
       while ( my $line2 = $sth2->fetchrow_hashref() ) {
               if ( $line2->{'Start'} <= $line1->{'Start'} && $line1->{'End'} <= $line2->{'End'} ) {
                       print "test";
               }
       }
}

Regards,

Rocky

Seoul National University


[1]: http://i.stack.imgur.com/y8a4P.png

Please attach your data as *.txt file(s) instead of images or *.pdf files. Instead of having to look at the images and manually enter your data into MySQL we could copy and paste it to try and reproduce the error.

Hello,
I would like to map and extract common numbers between 5 column (i.e.Start) and 6th column (End) for refFlat table with 2nd column (Start) and 3rd column (End) for mirna table. I can run this script but I could not map Start and End columns in pre-mirna AND refFlat text files and extract the common with the Start and End columns.

#!/usr/bin/perl

use strict;
use DBI;

my $user = 'root';
my $password = '1004';

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mirvar", $user, $password) || die "Failed connect DB : $!\n";

my $sql;

$sql = "select ref.chr, m.Start as m_start, m.End as m_end, ref.Start as ref_start, ref.End as ref_end from mirna as m, refFlat as ref limit 10";
#my $sql1 = "select * from mirna limit 10";
#my $sql2 = "select * from dbsnp limit 10";


my $sth = $dbh->prepare($sql);

#my $sth1 = $dbh->prepare($sql1);
#my $sth2 = $dbh->prepare($sql2);

#$sth1->execute || die "Error! : $sql\n";
#$sth2->execute || die "Error! : $sql\n";

$sth->execute || die "Error! : $sql\n";

#while ( my $line1 = $sth1->fetchrow_hashref() ) {
#       while ( my $line2 = $sth2->fetchrow_hashref() ) {
#               if ( $line2->{'Start'} <= $line1->{'Start'} && $line1->{'End'} <= $line2->{'End'} ) {
#                       print "test";
#               }
#       }
#}


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


  my $chr = $refFlat->{'chr'};
  my $m_start = $refFlat->{'m_start'};
  my $ref_start = $refFlat->{'ref_start'};
  my $m_end = $refFlat->{'m_end'};
  my $ref_end = $refFlat->{'ref_end'};
   # to do~ comparison code~ if(

  print "$chr\t$m_start\t$m_end\t$ref_start\t$ref_end\n";
}

Try moving the statement that executes $sth2 into the outer loop, something like the following (not tested):

#!/usr/bin/perl
use strict;
use warnings;

use DBI;

my $user = 'root';
my $password = '1004';

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mirvar", $user, $password) || die "Failed connect DB : $!\n";

my $sql;

$sql = "select d.chr, m.Start as m_start, m.End as m_end, d.Start as d_start, d.End as d_end from mirna as m, dbsnp as d limit 10000";
my $sql1 = "select * from mirna limit 10";
my $sql2 = "select * from dbsnp limit 10";


my $sth = $dbh->prepare($sql);
my $sth1 = $dbh->prepare($sql1);
my $sth2 = $dbh->prepare($sql2);

$sth1->execute || die "Error! : $sql\n";

$sth->execute || die "Error! : $sql\n";

while ( my $line1 = $sth1->fetchrow_hashref() ) {
    $sth2->execute || die "Error! : $sql\n";#Moved into loop
    while ( my $line2 = $sth2->fetchrow_hashref() ) {
        if ( $line2->{'Start'} <= $line1->{'Start'} && $line1->{'End'} <= $line2->{'End'} ) {
                print "test";
        }
    }
}

Dear Sir,

I ran this perl script but there was a error while running it. The output comes like --

[haojamrocky@melon hg18]$ perl test2.pl
Global symbol "$chr" requires explicit package name at test2.pl line 32.
Global symbol "$m_start" requires explicit package name at test2.pl line 32.
Global symbol "$m_end" requires explicit package name at test2.pl line 32.
Global symbol "$d_start" requires explicit package name at test2.pl line 32.
Global symbol "$d_end" requires explicit package name at test2.pl line 32.
Execution of test2.pl aborted due to compilation errors.

I would be happy to see your help.

Regards,
Rocky

#!/usr/bin/perl
use strict;
use warnings;

use DBI;

my $user = 'root';
my $password = '1004';

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mirvar", $user, $password) || die "Failed connect DB : $!\n";

my $sql;

$sql = "select d.chr, m.Start as m_start, m.End as m_end, d.Start as d_start, d.End as d_end from mirna as m, dbsnp as d limit 10000";
my $sql1 = "select * from mirna";
my $sql2 = "select * from dbsnp";


my $sth = $dbh->prepare($sql);
my $sth1 = $dbh->prepare($sql1);
my $sth2 = $dbh->prepare($sql2);

$sth1->execute || die "Error! : $sql\n";

$sth->execute || die "Error! : $sql\n";

while ( my $line1 = $sth1->fetchrow_hashref() ) {
    $sth2->execute || die "Error! : $sql\n";#Moved into loop
    while ( my $line2 = $sth2->fetchrow_hashref() ) {
        if ( $line2->{'Start'} <= $line1->{'Start'} && $line1->{'End'} <= $line2->{'End'} ) {
                print "$chr\t$m_start\t$m_end\t$d_start\t$d_end\n";
        }
    }
}

Your script has not declared variables named $chr, $m_start, $m_end, $d_start, and $d_end. You must declare them and assign values to them. See an example of this error and its remedy. If you don't assign values to them you will get a different error message saying values for those variables are undefined so you can't use them for concatenation in the following statement: print "$chr\t$m_start\t$m_end\t$d_start\t$d_end\n";

Hello,
I would like to map columns chr, start, end for dbSNP with chr, start, end for mirna and extract common lines with same chr, start, end positions. I have written a perl script where I found an error while running. could you please check it out and send me. I would be glad for your help.

Regards,
Rocky

#!/usr/bin/perl -w

use strict;
use warnings;
use DBI;

my $user = 'root';
my $password = '1004';

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mirvar", $user, $password) || die "Failed connect DB : $!\n";

#my $sql;

#$sql = "select d.chr, m.Start as m_start, m.End as m_end, d.Start as d_start, d.End as d_end from mirna as m, dbsnp as d limit 100";
#my $sql1 = "select * from mirna limit 100";
my $sql1 = "select * from mirna limit 100";
my $sql2 = "select * from dbsnp limit 100";

#my $sth = $dbh->prepare($sql);
my $sth1 = $dbh->prepare($sql1);
my $sth2 = $dbh->prepare($sql2);

#$sth->execute || die "Error! : $sql\n";
$sth1->execute || die "Error! : $sql1\n";
$sth2->execute || die "Error! : $sql2\n";

#while ( my $line1 = $sth1->fetchrow_hashref() ) {
#       while ( my $line2 = $sth2->fetchrow_hashref() ) {
#               if ( $line2->{'Start'} <= $line1->{'Start'} && $line1->{'End'} <= $line2->{'End'} ) {
#                       print "test";
#               }
#       }
#}


while( my $dbsnp = $sth1->fetchrow_hashref() ){

  my $dbsnp_chr = $dbsnp->{'chr'};
  my $dbsnp_start = $dbsnp->{'d_start'};
  my $dbsnp_end = $dbsnp->{'d_end'};
   
while( my $mirna = $sth2->fetchrow_hashref() ){

  my $mirna_chr = $mirna->{'chr'};
  my $mirna_start = $mirna->{'Start'};
  my $mirna_end = $mirna->{'End'};

# to do~ comparison code~

  if($dbsnp_chr eq $mirna_chr && $dbsnp_start == $mirna_start || $dbsnp_end == $mirna_end || ($dbsnp_start >= $mirna_start && $dbsnp_end >= $mirna_end))
print "$dbsnp_chr\t$dbsnp_start\t$dbsnp_end\t$mirna_chr\t$mirna_start\t$mirna_end\n";
}
}

OUTPUT

[haojamrocky@melon hg18]$ perl test5.pl
syntax error at test5.pl line 52, near ")
print"
Execution of test5.pl aborted due to compilation errors.

Hello,
I would like to map columns chr, start, end for dbSNP with chr, start, end for mirna and extract common lines with same chr, start, end positions and also considering within the start and end positions too. I have written a perl script where I found an error while running. could you please check it out and send me. I would be glad for your help.

Regards,
Rocky

Dear Sir,

I would like to map values in columns and extract attributes with same values as below.

CONDITION:-
same chrID in dbsnp129 with mirna text files
same chromStart and chromEnd or within these two positions.

I hereby attached the two tables in mysql.

Regards,
Rocky

#!/usr/bin/perl -w
use warnings;
###Perl Script to map SNPs in microRNAs####
###USAGE: Map_miRNA_SNP.pl <input miRNA file> <input SNP file> <output file>###


#if (scalar @ARGV <3)
#       {
#               print "Check Usage of the program\n";
#       }
#$infile_mirna=$ARGV[0];
open (IN, "/home/haojamrocky/DATA/hg18/mirna/pre-mirna") or die;
while ($read = <IN>)
        {
                chomp $read;
                $read =~ s/\r//g;
                @datarr = split '\t', $read;

                push @Chr, $datarr[0];
                push @Chrstart, $datarr[1];
                push @Chrend, $datarr[2];
                push @Chrstrand, $datarr[3];
                push @Chr_mirna, $datarr[4];
        }
                #$infile_SNP="$ARGV[1]";
                open (SNP, "/home/haojamrocky/DATA/hg18/dbsnp/dbsnp129/dbsnp129.txt") or die print "Enter a valid file name on command line\n";
                #open WRITE, ">$ARGV[2]" or die;
                #print WRITE "Chr\tmiRNA\tStrand\tSNP\tSNPstrand\tmiRStart\tmiRStop\tSNP_POS\tAllele\trefNCBI\trefUCSC\tObserved\tValidation Status\n";

                while ($snp_each = <SNP>)
                        {
                                chomp $snp_each;
                                $snp_each=~ s/\r//g;
                                @snp_data = split("\t", $snp_each);
                                for ($i = 0; $i <= scalar@Chr; $i++)
                                        {
                                                                if ($Chr[$i] =~ /^$snp_data[1]$/ && $snp_data[3] >= $Chrstart[$i] && $snp_data[3] <=  $Chrend [$i] )
                                                        {
                                                                print WRITE "$Chr[$i]\t$Chr_mirna[$i]\t$Chrstrand[$i]\t$snp_data[4]\t$snp_data[6]\t$Chrstart[$i]\t$Chrend[$i]\t$snp_data[2]\t$snp_data[9]\t$snp_data[8]\t$snp_data[7]\t$snp_data[10]\t$snp_data[12]\n";
                                                        }
                                        }


                        }

Mr/ Mrs,
I am running a perl script for mapping the genomic positions i.e. the start and end positions to retrieve positions within the start and end. The script runs correctly in smaller text files, but it was running slowly for almost 4 days, how it could be run faster by rectifying the script attach here. The size for snp129.txt is 1.60 GB and TARGETSCAN is 2.20 MB I would be glad for your kindness. Hoping to recieve response soon.

Regards,
Rocky
SNU, College of Medicine
Seoul

#!/usr/bin/perl -w
#use strict;


open(IN, "/home/haojamrocky/snp129.txt") or die print "Enter a valid file name on command line\n";
@snp = <IN>; ## assigning snp 
close IN;

open(IN2, "/home/haojamrocky/DATA/hg18/miRTarget/TARGETSCAN") or die print "Cannot open mirna file\n";
@PITA = <IN2>; ## assigning mirna
close IN2;

for ($i=0; $i <= $#snp; $i++){
###################### SNP############################
        #$geneName = (split(/\t/,$refFlat[$i]))[0];
        #$name = (split(/\t/,$refFlat[$i]))[1];
        $chrom1 = (split(/\t/,$snp[$i]))[1];
        $chromStart1 = ((plit(/\t/,$sn)[$i]))[2];
        $chromEnd1 = (split(/\t/,$snp[$i]))[3];
########################(############)################
        foreach my $line(@PITA){     )
###################### m(rna #)#######################
                $chrom2 = (split(/\t/,$line))[0];
                $chromStart2 = (split(/\t/,$line))[1];
                $chromEnd2 = (split(/\t/,$line))[2];
#######################################################
        #            print "$chrom1 $chrom2\n";

                        if( $chrom1 eq $chrom2 && ( $chromStart1 >= $chromStart2 && $chromEnd1 <= $chromEnd2 )){
                        chomp $snp[$i]; chomp $line;
                        print "$snp[$i] \t $line \n";
                        }
                }
        }
print "\n";
exit;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.