DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Mysqlcode

09.04.2006
| 1667 views |
  • submit to reddit
        // description of your code here

// insert code here..

###Commonly SQL statements for MYSQL#############
11/4/2002 - JDM
##################################################
sub alldetails{
#localserver
$DBD = "mysql";
$dbase = "myroseweb";
$dbuser = "root";
$dbpassword = "";
$server = "localhost";
$dbh = DBI->connect
("dbi:$DBD:$dbase",$dbuser,$dbpassword) or die "couldn't do it";
$drh = DBI->install_driver("mysql");
$sth = $dbh->prepare("SELECT tblidx,title,presenter,descrip,mo,dom,yr,loc,ceus from tbltraining order by tblidx DESC") or die;
$sth->execute;
$numrows = $sth->rows();
$looper = 0;
while ($looper < $numrows){
($tid,$ttitle,$tpresenter,$tdescrip,$tmo,$tdom,$tyr,$tloc,$tceus) = $sth->fetchrow_array;
$row = "$tid:$ttitle:$tpresenter:$tdescrip:$tmo:$tdom:$tyr:$tloc:$tceus";
push @alltrngs, $row;
$looper++;
#endwhile
}
$sth->finish;
$dbh->disconnect;
#endsub
}

sub deletetdetail{
&doheader;
if ($step == 1){
&connectdb;
$statement=qq(DELETE FROM tblscd WHERE (indx = '$id'));
$sth = $dbh->prepare($statement);
$sth->execute;
$sth->finish;
print "<font class='shdtxt'>The record has been deleted.</font>";
}else{
print "Are you sure you want to delete this record?<p>";
print "<a href='/mir/scdadmin.cgi?cmd=9&id=$id&step=1'><b>Yes</a><p>";
print "<a href=\"javascript\:history\.go\(-1\)\"><b>No</b></a><p>";

#endstepif
}
&dofooter;

#endsub
}


###SELECT###########
&connectdb;
$sth = $dbh->prepare("SELECT doy,pdate,postby,menu,dy,wk,facility from tblmenu where indx='$id'") or die;
$sth->execute;
($doy,$pdate,$postby,$menu,$dy,$wk,$facility) = $sth->fetchrow_array;
$sth->finish;
$dbh->disconnect;

###########################################
###UPDATE########################

&connectdb;
$qpdate = $dbh->quote( $pdate );
$qbev = $dbh->quote( $bev );
$qentre1 = $dbh->quote( $entre1 );
$qentre2 = $dbh->quote( $entre2 );
$qside1 = $dbh->quote( $side1 );
$qside2 = $dbh->quote( $side2 );
$qside3 = $dbh->quote( $side3 );
$qside4 = $dbh->quote( $side4 );
$qpostby = $dbh->quote( $postby );
#indx,bev,lcbev,entre1,lcent1,entre2,lcent2,side1,lcside1,side2,lcside2,side3,lcside3,side4,
#lcside4,meal,mm,dm,yy,doy,pdate,postby,menu,dy,wk,facility

$sth = $dbh->prepare("UPDATE tblmenu SET bev=$qbev,lcbev='$lcbev',entre1=$qentre1,facility='$facility' WHERE indx='$id'") or die;
$sth->execute;
$sth->finish;

###########################################
###INSERT##################################
&connectdb;
$qpdate = $dbh->quote( $pdate );
$qbev = $dbh->quote( $bev );
$qentre1 = $dbh->quote( $entre1 );
$qentre2 = $dbh->quote( $entre2 );
$qside1 = $dbh->quote( $side1 );
$qside2 = $dbh->quote( $side2 );
$qside3 = $dbh->quote( $side3 );
$qside4 = $dbh->quote( $side4 );
$qpostby = $dbh->quote( $postby );
$indx = "";
$statement=qq
(INSERT INTO tblmenu (indx,bev,lcbev,entre1,lcent1,entre2,lcent2,side1,lcside1,side2,lcside2,side3,lcside3,side4,lcside4,meal,mm,dm,yy,doy,pdate,postby,menu,dy,wk,facility) VALUES('$indx',$qbev,'$lcbev',$qentre1,'$lcent1',$qentre2,'$lcent2',$qside1,'$lcside1',$qside2,'$lcside2',$qside3,'$lcside3',$qside4,'$lcside4','$meal','$mm','$dm','$yy','$doy','$pdate','$postby','$menu','$dy','$wk','$facility')) or die;
$sth = $dbh->prepare($statement);
$sth->execute;
$sth->finish;
$dbh->disconnect;
##call email here

#######################################
####DELETE#########################

sub deletemenu {
&connectdb;
$statement=qq(DELETE FROM tblmenu WHERE (indx = '$id'));
$sth = $dbh->prepare($statement);
$sth->execute;
$sth->finish;
print $query->header;
print $query->start_html;
print "<h3>The menu has been deleted.</h3>";
print "<a href='/cgi-bin/menu.cgi'>View The Menu List</a><p>";
print $query->end_html;
#endsub
}


##############CONNECT TO DB#######################

sub connectdb{
$DBD = "mysql";
$dbase = "misc";
$dbuser = "root";
$dbpassword = "";
$dbh = DBI->connect("dbi:$DBD:$dbase",$dbuser,$dbpassword) or die "couldn't do it";
$drh = DBI->install_driver("mysql");
#endsub
}
sub connectdb{
$DBD = "mysql";
$dbase = "svdb";
$dbuser = "stillmanvalley";
$dbpassword = "magicbox1";
$dbserver = "mysql.stillmanvalley.org";
$dbh = DBI->connect("dbi:$DBD:$dbase:$dbserver",$dbuser,$dbpassword) or die "couldn't do it";
$drh = DBI->install_driver("mysql");
#endsub
}


###load DATA FROM CSV FILE
##############################################
use DBI;

$file = "dcs.csv";
$terminator="\,";
$db="misc";
$tbl="tbldcs";
$dbtbl = "$db"."\."."$tbl";
$fldnames="name,dod,totdays,reasonfordc";

&connectdb;
##$sth = $dbh->prepare("LOAD DATA LOCAL INFILE '$file' INTO TABLE misc.tbladmits FIELDS TERMINATED BY ':' LINES TERMINATED BY '\n' (eid,apid,orgid,last,first,relation,orgname,orgtype,atype,add1,add2,city,state,zip,county,ptype,ac,phone,ext,pid,phid)") or die;
$sth = $dbh->prepare("LOAD DATA LOCAL INFILE '$file' INTO TABLE $dbtbl FIELDS TERMINATED BY '$terminator' LINES TERMINATED BY '\n' ($fldnames)") or die;

$sth->execute;
$sth->finish;
$dbh->disconnect;

#############################################