Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
Mysql in Perl

Posted by revtopo (revtopo), 2 October 2007
hi there,

I have got to execute two sql queries in a program. In this the result from the first should be used in the second query as a condition.

say $sequence is the result from the first query which has to be used in the second query as a condition. there are about 10 $sequence.

my second sql query is similar to this.

select rna_sequence, hit_string, hit_target, sequence_db from Hsp where rna_sequence  = '$sequence' and sequence_db = '1069'

so, my questions is,  is that correct to use $sequence directly in the place that i have used if notcould you suggest any other method to do.

thanks

Posted by admin (Graham Ellis), 2 October 2007
You're correct in principle, I think ... but as you don't give a code example I can't say whether of not you've correctly assigned the data from the first query into that $sequence variable.

Posted by revtopo (revtopo), 2 October 2007
my $sequence;
my $dsn = qq(DBI:mysql:database=$database; host=$host);
$connect = DBI-> connect($dsn, $user, $pass,{printError =>1}) or die $DBI::errstr;
my $query = $connect->prepare(qq(select rna_sequence_sequence, count(rna_sequence_sequence) as count, miRNA  from Cloned_rna where length(Rna_sequence_sequence) = 21 and miRNA is null group by Rna_sequence_sequence order by count desc limit 10  ));
$query->execute;
while (my( $sequence, $count, $miRNA) = $query->fetchrow_array()){
$miRNA='N' unless defined $miRNA; # gives the undefined mirna a value since they are null
$sequences{$sequence}= $count;
}
foreach my $sequence (sort keys %sequences) {
print "$sequence\t$sequences{$sequence}\n";
}

my $query1 = $connect->prepare(qq(select accession, hit_start, hit_end, Rna_sequence_sequence, hit_string, sequence_idsequence_db from Hsp where rna_sequence_sequence = '$sequence' and Sequence_db_idSequence_db ='1069' limit 10));
$query1->execute;
while (my ($accession, $hit_start, $hit_end, $Rna_sequence_sequence, $hit_string, $sequence_idsequence_db) = $query1->fetchrow_array()){
print "$accession, $hit_start, $hit_end, $Rna_sequence_sequence, $hit_string, $sequence_idsequence_db\n";
}

please note that ma using strict in my program

Posted by admin (Graham Ellis), 2 October 2007
You are getting 10 results back and just using the last of the 10 values of $sequence in your second query.  That's fine if it's what you want to do, but I suspect you really wanted to a do a loop there ...

.... which ....

.... deep breath ....

points me to wards suggesting you do the whole thing using a Join in MySQL.



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho