Gossamer Forum
(Advanced Search)
for
Home : General : Perl Programming :

SQL Thread

Nov 30, 2001, 2:54 AM
Wil
Veteran / Moderator (4108 posts)
Nov 30, 2001, 2:54 AM
Post #1 of 7
Views: 4007
Shortcut
Quote Reply
SQL Thread
Hi

I'm using the following code in a part of a script.

Code:
$sth = $dbh->prepare("SELECT * FROM $DB_MYSQL_NAME WHERE res_type = 'Online' ORDER BY id");
$sth->execute ();

my $count = 0;

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

if ($q::toggle_status) {

$sthisle = $dbh->prepare("UPDATE $DB_MYSQL_NAME SET islive = 'In Progress' WHERE id = '$res_id'");
$sthisle->execute();
$sthisle->finish;

}

...

What I'd like to avoid is to open up another connection to the database. Is there a way I can set the part shown in red on the same database connection as the call above it? Or would that mess up my threads? And if I can do this ... how?

Thanks.

- wil
Nov 30, 2001, 4:33 AM
Paul
Veteran (19537 posts)
Nov 30, 2001, 4:33 AM
Post #2 of 7
Views: 3795
Shortcut
Quote Reply
Re: [Wil] SQL Thread In reply to
You aren't making another connection.

If you were, you'd have:

$dbh = DBI->connect ( ... );

Not sure why you are using 2 queries, all you need is:

UPDATE $DB_MYSQL_NAME SET islive = 'In Progress' WHERE id = '$res_id' AND res_type = 'Online'

Last edited by:

PaulW: Nov 30, 2001, 4:35 AM
Nov 30, 2001, 4:38 AM
Wil
Veteran / Moderator (4108 posts)
Nov 30, 2001, 4:38 AM
Post #3 of 7
Views: 3808
Shortcut
Quote Reply
Re: [PaulW] SQL Thread In reply to
Yes, sorry. I know I'm not re-connecting to the database, but I am using a different statment handler. And that's what I'm trying to avoid. If possible?

- wil
Nov 30, 2001, 4:46 AM
Paul
Veteran (19537 posts)
Nov 30, 2001, 4:46 AM
Post #4 of 7
Views: 3795
Shortcut
Quote Reply
Re: [Wil] SQL Thread In reply to
Yeah use the query above.
Nov 30, 2001, 4:47 AM
Wil
Veteran / Moderator (4108 posts)
Nov 30, 2001, 4:47 AM
Post #5 of 7
Views: 3813
Shortcut
Quote Reply
Re: [PaulW] SQL Thread In reply to
That's what I'm using already? Unsure

- wil
Nov 30, 2001, 4:54 AM
Paul
Veteran (19537 posts)
Nov 30, 2001, 4:54 AM
Post #6 of 7
Views: 3802
Shortcut
Quote Reply
Re: [Wil] SQL Thread In reply to
Doesn't look like it in your first post.
Nov 30, 2001, 4:59 AM
Wil
Veteran / Moderator (4108 posts)
Nov 30, 2001, 4:59 AM
Post #7 of 7
Views: 3785
Shortcut
Quote Reply
Re: [PaulW] SQL Thread In reply to
Ah, I get what you're saying. That's not the whole code, that's where you're getting confused? The entire code block is (you kind of asked for it <g>):

Code:
$sth = $dbh->prepare("SELECT * FROM $DB_MYSQL_NAME WHERE res_type = 'Online' ORDER BY id");
$sth->execute ();

my $count = 0;

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

my $req = new HTTP::Request GET => $ref->{'url_en'};
my $res = $ua->request($req);

$res_id = $ref->{id};
$res_code = $res->code;
$res_msg = $res->message;

unless ($res_code eq "200") {

$count ++;

$data_status = $ref->{'islive'};

if ($q::toggle_status) {

$sthisle = $dbh->prepare("UPDATE $DB_MYSQL_NAME SET islive = 'In Progress' WHERE id = '$res_id'");
$sthisle->execute();
$sthisle->finish;

$data_status = "In Progress";
}

$tmpl_show_record .= qq|

<table width="95%" border="0" cellspacing="0" cellpadding="2">
<tr>
<td width="2%" align="middle">&nbsp;</td>
<td width="6%" bgcolor="#EEEECC" align="right" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">$ref->{id}</font>&nbsp;</td>
<td width="58%" bgcolor="#E9EBEF">&nbsp;<font face="Arial, Helvetica, sans-serif" size="2">$ref->{'name_en'}</font></td>
<td width="20%" bgcolor="#FFDDDD">&nbsp;<font face="Arial, Helvetica, sans-serif" size="2">$res_code : $res_msg</font></td>
<td width="14%" bgcolor="#EEEECC" valign="top" align="center"><a href="odb.cgi?action=edit_record&id=$ref->{'id'}"><img src="/images/icons/edit.gif" width="15" height="15" alt="[ edit ]" border="0"></a>
&nbsp;
<a href="odb.cgi?action=del_record&id=$ref->{'id'}" onClick="return confirm('Delete record $ref->{'id'}?')"><img src="/images/icons/delete.gif" width="15" height="15" alt="[ delete ]" border="0"></a>
&nbsp;
<a href="odb.cgi?action=toggle_live&id=$ref->{'id'}">
|;

if ($data_status eq "Live") {
$tmpl_show_record .= "<img src=\"/images/icons/liveyes.gif\" border=\"0\">";
}
else {
$tmpl_show_record .= "<img src=\"/images/icons/liveno.gif\" border=\"0\">";
}

$tmpl_show_record .= qq|

</a>
</td>
</tr>
</table>
<BR>
|;
}
}

$num_dead = $count;

if ($count == 0) {
&error_html("No dead links found!");
exit;
}

$sth->finish();

- wil