map and extract common numbers between two columns

[haobijam]

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 of chromosome (chr). So for this, I have written a perl script where I found an error while running. could you please check it out and suggest me some points or modify. I would be glad for your help.

Regards,
Rocky
Seoul National University

#!/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";
}
}

[miller]

I suspect that your code does not do what you want.

You need to either save the results of the 2nd query so that you can loop through them multiple times for each record in the 1st query, or you need to reset your 2nd query during each iteration of the first.

- Miller