How do I do multiple mySQL queries??

[jacktripper]

I've got a database which I'm trying to pull information from various table and various indexid's etc etc. I've scripted a Perl program to get email addresses, a message subject, and a message body. However, it ends up re-inserting info from the 1st query.

For example, the results look something like this:
email address 1
email address 2
email address 3
(new query for subject results in...)
email address 1
email address 2
email address 3
subject
(new query for message results in...)
email address 1
email address 2
email address 3
subject
message

All I really need are the last 5 lines. How can I do multiple queries without this "build up" of information? Is there a way to purge the previous query before starting another one?

 

[goBoating]

Hello Mr. Tripper,
a look at your code would be helpful. Remember to much code and no one will want to read it all and to little and we'll ask you for more again. So, try to make a guess at where you problem might be and post a concise version.

I'm sure you'll get the help you need.
If you are new to Tek-Tips, please use descriptive titles, check the FAQs,
and beware the evil typo.

 

[Coderifous]

Are you using DBI? Are you familiar w/ SQL and MySQL? Why can't you pull all of the info resulting from 3 separate queries, in just 1 query? I don't understand why you are doing it by this "build up" method in the first place.

You probably need to learn about joins in SQL, which will enable you too query from separate tables in one SQL statement. It's really easy. I think that's your prob anyway. Post some code. A little example maybe.

That would help.

--Jim

 

[jacktripper]

Okay, here's the code. I'm sure there is a way to pull all the info using just ONE query, but obviously that's what I don't know how to do.... so here I am. ha.

I know a little about SQL and mySQL, but not much. Its safe to say I know a little about Perl, but not much.

Anyway, see the code below. Its a little long, as it displays each query to the database:

--------


# connect to the database first time

$dbh = DBI->connect ("DBI:$databaseserver:$dbase",$user,$password,
{RaiseError=>0,PrintError=>1})
|| &security("Unable to connect to database $dbase"
$sth = $dbh->prepare("show tables&quot ||
&security("Unable to access script Step t1b"
$sth->execute() || die(&quot;Unable to execute query<BR>&quot;


# retreive the table data for email addresses

$sth = $dbh->prepare(&quot;select email from $tablename&quot;
$sth->execute() || die(&quot;Unable to execute&quot;

while (@ary = $sth->fetchrow_array()){
push(@tables,$ary[0]);
push(@types,$ary[1]);
push(@null,$ary[2]);
push(@key,$ary[3]);
push(@default,$ary[4]);
}

$length=@tables;
if (!$length){
print &quot;The table <B><FONT color=\&quot;#000080\&quot;>$tablename</FONT></B>
from database <FONT color=\&quot;#800000\&quot;>$dbase</FONT> contains no columns.\n&quot;;
}

foreach $line (@tables){
print &quot;$line\n&quot;;
}

$sth->finish();
$dbh->disconnect();
$sth=&quot;0&quot;;

# connect to the database second time

$dbh = DBI->connect (&quot;DBI:$databaseserver:$dbase&quot;,$user,$password,
{RaiseError=>0,PrintError=>1})
|| &security(&quot;Unable to connect to database $dbase&quot;
$sth = $dbh->prepare(&quot;show tables&quot ||
&security(&quot;Unable to access script Step t1b&quot;
$sth->execute() || die(&quot;Unable to execute query<BR>&quot;


# retreive the subject of the message

$sth = $dbh->prepare(&quot;select subject from $tablenameB where newsid='$newsid'&quot;
$sth->execute() || die(&quot;Unable to execute&quot;

while (@ary = $sth->fetchrow_array()){
push(@tables,$ary[0]);
push(@types,$ary[1]);
push(@null,$ary[2]);
push(@key,$ary[3]);
push(@default,$ary[4]);
}

$length=@tables;
if (!$length){
print &quot;The table <B><FONT color=\&quot;#000080\&quot;>$tablename</FONT></B>
from database <FONT color=\&quot;#800000\&quot;>$dbase</FONT> contains no columns.\n&quot;;
}

foreach $line (@tables){
print &quot;$line\n&quot;;
}

$sth->finish();
$dbh->disconnect();


# connect to the database for the third time

$dbh = DBI->connect (&quot;DBI:$databaseserver:$dbase&quot;,$user,$password,
{RaiseError=>0,PrintError=>1})
|| &security(&quot;Unable to connect to database $dbase&quot;
$sth = $dbh->prepare(&quot;show tables&quot ||
&security(&quot;Unable to access script Step t1b&quot;
$sth->execute() || die(&quot;Unable to execute query<BR>&quot;


# retreive the body of the message

$sth = $dbh->prepare(&quot;select thebody from $tablenameB where newsid='$newsid'&quot;
$sth->execute() || die(&quot;Unable to execute&quot;

while (@ary = $sth->fetchrow_array()){
push(@tables,$ary[0]);
push(@types,$ary[1]);
push(@null,$ary[2]);
push(@key,$ary[3]);
push(@default,$ary[4]);
}

$length=@tables;
if (!$length){
print &quot;The table <B><FONT color=\&quot;#000080\&quot;>$tablename</FONT></B>
from database <FONT color=\&quot;#800000\&quot;>$dbase</FONT> contains no columns.\n&quot;;
}

foreach $line (@tables){
print &quot;$line\n&quot;;
}

$sth->finish();
$dbh->disconnect();
exit;

 

[hmerrill]

1. Before you can re-use a statement handle, you *must*
&quot;finish&quot; it. You need a

$sth->finish || die &quot;Some die message&quot;;

After your 1st connect, but before query

$sth = $dbh->prepare(&quot;select email from $tablename&quot;

2. You only need to connect to the database once per
script - connecting more than once is a waste of
resources. Once you have a database handle($dbh), just
continue to use that database handle throughout the
script, and only disconnect at the end, or when you
are going to exit the script.

Not sure what else might be wrong with your code - I don't see anyplace where you assign values to $tablename. Hopefully 1 & 2 will help.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com