Dave’s Ramblings

MySQL Examples& PERL code snippets15 Jun 2006 12:21 pm

Like all programing languages, there are many ways to connect to databases, this in particular shows a the simplest method, though burying the host, username and password in an include would be better for security reasons, this is effective enough for most purposes.

The only requirements for perl may be the DBD::mysql module if not already installed, this should get all the needed prerequisites that may not be present. Either download them directly from CPAN or use the interactive installer shell

PLAIN TEXT

CODE:

$perl -MCPAN -e shell
$install DBD::mysql

To use this example copy this code into your script, edits $username, $password, and $db_name values to suit your environment.

PLAIN TEXT

CODE:

#Invoke the Perl Database Libraries
use DBI;
# MySQL server hostname
my $host = "mysql.serverhost.com";
#your account username and MySQL password
my $username = "your_user";
my $password = "your_MySQL_password";
#Edit this to point to the database you wish to connect to
my $db_name = "your_database";
#
#The following lines do not need to be edited
#
#put the database and server in to the connect statement
$dsn = "DBI:mysql:database=$db_name;host=$host";
#Generate the full connect statement
$dbh = DBI->connect($dsn, $username, $password);
#run the connect statement
$self->dbh = $dbh;

MySQL Examples31 May 2006 10:09 am

SQL Sub Queries

A quick query structure to retrive the last row entry to a logging table, in this case there can be multiple rows that would return the same id and date but I want only the very last so this query works well.

PLAIN TEXT

CODE:

$query="SELECT a.user_id, a.access_date FROM superlog a
WHERE a.access_date = (SELECT MAX(b.access_date)
FROM superlog b
WHERE a.user_id=b.user_id)
AND ip = CONVERT( _utf8 '10.10.10.10' USING latin1 )
COLLATE latin1_swedish_ci;

MySQL Examples08 May 2006 10:43 am

MySQL DROP Command

The Drop command is used to delete all the records in a table using the table name as shown below:

Syntax:

$dropSQL=("DROP tblName");

Example

PLAIN TEXT

CODE:

$dropSQL=("DROP tblstudent");

MySQL Examples08 May 2006 10:42 am

MySQL UPDATE Command

The Update command is used to update the field values using conditions. This is done using 'SET' and the fieldnames to assign new values to them.

Syntax:

$updateSQL=("UPDATE Tblname SET (fieldname1=value1,fieldname2=value2,...) WHERE fldstudid=IdNumber");

Example:

PLAIN TEXT

CODE:

$updateSQL=("UPDATE Tblstudent SET (fldstudName=siva,fldstudmark=100) WHERE fldstudid=2");

MySQL Examples08 May 2006 10:42 am

MySQL INSERT Command

The Insert command is used to insert records into a table. The values are assigned to the field names as shown below:

Syntax:

$insertSQL=("INSERT INTO tblname(fieldname1,fieldname2..) VALUES(value1,value2,...) ");

Example

PLAIN TEXT

CODE:

$insertSQL=("INSERT INTO Tblstudent(fldstudName,fldstudmark)VALUES(Baskar,75) ");

MySQL Examples

Monthly Archives

Categories

Search: