Trouble Connecting to Database

[Randymarsh9]

I have been experiencing some weird issues with quests on my server after trying to implement an npc that offered a leaderboard for a side quest, and I am pretty sure that it has to do with my use of connecting to the mysql database. I'm telling it to connect to the database at the beginning of the script then disconnect to then end, and I am wondering if that is messing up everything else. Can anyone look over this and tell me if they see a problem?

sub EVENT_SAY
{
	use DBI;
	$db = DBI->connect('dbi:mysql:pelican','root','***')
	or die "Connection Error: $DBI::errstr\n";
	my $Leaderboard = quest::saylink("Leaderboard", 1);
	my $compete = quest::saylink("compete", 1);
	my $ready = quest::saylink("ready", 1);
	my $information = quest::saylink("information", 1);
	my $prize = quest::saylink("prize", 1);
	my $Tier1 = quest::saylink("tier1", 1, "Tier One");
	my $Tier2 = quest::saylink("tier2", 1, "Tier Two");
	my $Tier3 = quest::saylink("tier3", 1, "Tier Three");
	my $Tier4 = quest::saylink("tier4", 1, "Tier Four");
	my $Tier5 = quest::saylink("tier5", 1, "Tier Five");
	my $Tier6 = quest::saylink("tier6", 1, "Tier Six");
	my $Tier7 = quest::saylink("tier7", 1, "Tier Seven");
	my $Tier8 = quest::saylink("tier8", 1, "Tier Eight");
	my $Tier9 = quest::saylink("tier9", 1, "Tier Nine");
	my $Tier10 = quest::saylink("tier10", 1, "Tier Ten");
	###Tier one rewards#####
	my $Claim0 = quest::saylink("Claim0", 1, "Claim");
	my $itemLink0 = quest::varlink(77841);
	my $Claim1 = quest::saylink("Claim1", 1, "Claim");
	my $itemLink1 = quest::varlink(77829);
	my $Claim2 = quest::saylink("Claim2", 1, "Claim");
	my $itemLink2 = quest::varlink(77844);
	my $Claim3 = quest::saylink("Claim3", 1, "Claim");
	my $itemLink3 = quest::varlink(77850);
	my $Claim4 = quest::saylink("Claim4", 1, "Claim");
	my $itemLink4 = quest::varlink(77835);
	my $Claim5 = quest::saylink("Claim5", 1, "Claim");
	my $itemLink5 = quest::varlink(77838);
	my $Claim6 = quest::saylink("Claim6", 1, "Claim");
	my $itemLink6 = quest::varlink(77847);
	my $Claim7 = quest::saylink("Claim7", 1, "Claim");
	my $itemLink7 = quest::varlink(77867);
	my $Claim8 = quest::saylink("Claim8", 1, "Claim");
	my $itemLink8 = quest::varlink(77832);
	my $Claim9 = quest::saylink("Claim9", 1, "Claim");
	my $itemLink9 = quest::varlink(77865);
	my $Claim10 = quest::saylink("Claim10", 1, "Claim");
	my $itemLink10 = quest::varlink(77856);
	my $Claim11 = quest::saylink("Claim11", 1, "Claim");
	my $itemLink11 = quest::varlink(77857);

	if ($text=~/hail/i)
	{
		if (defined($qglobals{kunarkflag}))
		{
			my $sta = $db->prepare("SELECT name FROM `Leaderboard` where `name` = '$name'");
			$sta->execute();
			unless( $sta->rows)
			{
				my $rows = $db->do("INSERT INTO Leaderboard (Name,Level,Kills) VALUES ('$name', '$ulevel', '0')");	
			}
			$client->Message(4,"Baram tells you, 'Do you wish to $compete, check the $Leaderboard, choose a $prize, or hear more $information?'");

		}
		else
		{
			$client->Message(4,"...'");
		}
	}
	elsif ($text=~/compete/i)
	{
		if (defined($qglobals{kunarkflag}))
		{
			$client->Message(4,"Baram tells you, 'Eager for blood, are you? Whenever you're $ready, I'll send you off.'");
		}
	}
	elsif ($text=~/Leaderboard/i)
	{
		if (defined($qglobals{kunarkflag}))
		{
			my $sth = $db->prepare("SELECT * FROM `Leaderboard` Where `Kills` >0 ORDER BY `Kills` DESC");
			$sth->execute();
			$index = 0;
			while ($ref = $sth->fetchrow_arrayref)
			{
				if ($index == 0)
				{
					$first = $ref->[0];
					$firstKills = $ref->[2];
				}
				elsif ($index == 1)
				{
					$second = $ref->[0];
					$secondKills = $ref->[2];
				}
				elsif ($index == 2)
				{
					$third = $ref->[0];
					$thirdKills = $ref->[2];
				}
				elsif ($index == 3)
				{
					$fourth = $ref->[0];
					$fourthKills = $ref->[2];
				}
				elsif ($index == 4)
				{
					$fifth = $ref->[0];
					$fifthKills = $ref->[2];
				}
				else
				{
				}
				$index++;
			}
			quest::popup("Leaders", "1. $first - $firstKills<br />2. $second - $secondKills<br />3. $third - $thirdKills<br />4. $fourth - $fourthKills<br />5. $fifth - $fifthKills");
		}
	}
	elsif ($text=~/information/i)
	{
		if (defined($qglobals{kunarkflag}))
		{
			$client->Message(4,"...");
		}
	}
	elsif ($text=~/ready/i)
	{
		if (defined($qglobals{kunarkflag}))
		{
			my $Group = $client->GetGroup();
			$Group->DisbandGroup();
			plugin::SendToInstance("solo", "karnor", 1, -555, -77, 18, "survivalbattle", "7200");
     			quest::setglobal("survival", 1, 5, "F");
		}
	}
	elsif ($text=~/prize/i)
	{
		if (defined($qglobals{kunarkflag}))
		{
			my $stb = $db->prepare("SELECT kills FROM `Leaderboard` where `name` = '$name'");
			$stb->execute();
			$refb = $stb->fetchrow_arrayref();
			$myKills = $refb->[0];
			$client->Message(15,"You have $myKills kills");
			$client->Message(15,"You can only claim one prize from each tier");
			$client->Message(4,"$Tier1 - 10 kills required");
			$client->Message(4,"$Tier2 - 20 kills required");
			$client->Message(4,"$Tier3 - 30 kills required");
			$client->Message(4,"$Tier4 - 40 kills required");
			$client->Message(4,"$Tier5 - 50 kills required");
			$client->Message(4,"$Tier6 - 75 kills required");
			$client->Message(4,"$Tier7 - 100 kills required");
			$client->Message(4,"$Tier8 - 150 kills required");
			$client->Message(4,"$Tier9 - 200 kills required");
			$client->Message(4,"$Tier10 - 250 kills required");
		}	
	}
	elsif ($text=~/\btier1\b/)
	{
		if (defined($qglobals{kunarkflag}))
		{
			if (defined($qglobals{sT1Claim}))
			{
				$client->Message(13,"You have already claimed your prize");
			}
			else
			{
				$client->Message(14,"You can still claim this prize");
			}
			$client->Message(4,"$itemLink0 - $Claim0");
			$client->Message(4,"$itemLink1 - $Claim1");
			$client->Message(4,"$itemLink2 - $Claim2");
			$client->Message(4,"$itemLink3 - $Claim3");
			$client->Message(4,"$itemLink4 - $Claim4");
			$client->Message(4,"$itemLink5 - $Claim5");
			$client->Message(4,"$itemLink6 - $Claim6");
			$client->Message(4,"$itemLink7 - $Claim7");
			$client->Message(4,"$itemLink8 - $Claim8");
			$client->Message(4,"$itemLink9 - $Claim9");
			$client->Message(4,"$itemLink10 - $Claim10");
			$client->Message(4,"$itemLink11 - $Claim11");
		}	
	}
	elsif ($text=~/\bClaim0\b/)
	{
			my $stc = $db->prepare("SELECT kills FROM `Leaderboard` where `name` = '$name'");
			$stc->execute();
			$refc = $stc->fetchrow_arrayref();
			$myKills = $refc->[0];
			if (($myKills > 10) && (!defined($qglobals{sT1Claim})))
			{
				quest::summonitem(77841);
				quest::setglobal("sT1Claim", 1, 5, "F");
			}
			else
			{
				$client->Message(13,"You are not eligible for this prize");
			}
	}
	...

	$db->disconnect();
}

[Zamthos]

Well, I don't see why you need the '...' on line 195, you may also need to put '$sta->finish ();', '$stb->finish ();', '$stc->finish ();', '$sth->finish ();' before '$db->disconnect();'. It would also be better to allow $sth to be changed, so that you just have one variable rather than multiple for the same thing.

EDIT: By allow it to be changed I mean rather than use 'my' just allow it to be changed. Example below.

$sth = $dbh->prepare("SELECT  name, hp, mana, race, class, level, armor, aa FROM leaderboard ORDER BY mana DESC LIMIT 10");
$sth = $dbh->prepare("SELECT  name, hp, mana, race, class, level, armor, aa FROM leaderboard ORDER BY armor DESC LIMIT 10");

[Randymarsh9]

Ok, I did not know if I could use the same variable name for different statements. Also, the ellipsis is just there to indicate that there are a bunch of similar statements after the last one so that I did not post a ton of code that was only unique in a few places.

[Zamthos]

Oh, okay, and yeah, you can. Hopefully I helped, you have to finish your executions, to my current understanding anyway.