Press "Enter" to skip to content

Posts published in “MySQL Examples”

Proper Grammar

Proper Grammar

On my 66th birthday, I got a gift certificate from my wife. The certificate paid for a visit to a medicine man living on a nearby reservation who was rumored to have a wonderful cure for erectile dysfunction. After being persuaded, I drove to the reservation, handed my ticket to the medicine man and wondered what would happen next.

The old man slowly, methodically produced a potion, handed it to me, and with a grip on my shoulder, warned, “This is powerful medicine and it must be respected. You take only a teaspoonful and then say ‘1-2-3. ‘When you do that, you will become more manly than you have ever been in your life and you can perform as long as you want.”

I was encouraged. As I walked away, I turned and asked, “How do I stop the medicine from working?” “Your partner must say ‘1-2-3-4,’ he responded. “But when she does, the medicine will not work again until the next full moon.”

I was very eager to see if it worked so I went home, showered, shaved, took a spoonful of the medicine, and then invited my wife to join me in the bedroom. When she came in, I took off my clothes and said, “1-2-3!”

Immediately, I was the manliest of men. My wife was excited and began throwing off her clothes. And then she asked, “What was the 1-2-3 for?”

And that, boys and girls, is why we should never end our sentences with a preposition!

ONE COULD END UP WITH A DANGLING PARTICIPLE!

Perl MySQL Connect

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

[code]
$perl -MCPAN -e shell
$install DBD::mysql
[/code]

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

[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;
[/code]

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.

[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;
[/code]

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

[code]
$dropSQL=(“DROP tblstudent”);
[/code]

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:

[code]
$updateSQL=(“UPDATE Tblstudent SET (fldstudName=siva,fldstudmark=100) WHERE fldstudid=2”);
[/code]

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

[code]
$insertSQL=(“INSERT INTO Tblstudent(fldstudName,fldstudmark)VALUES(Baskar,75) “);
[/code]

MySQL DELETE Command

The Delete command is used to delete the records from a table using conditions as shown below:

Syntax:

$deleteSQL=(“DELETE * FROM tablename WHERE condition”);

Example:

[code]
$deleteSQL=(“DELETE * FROM tblstudent WHERE fldstudid=2”);
[/code]

MySQL SELECT Command

The Select command is used to select the records from a table using its field names. To select all the fields in a table, ‘*’ is used in the command. The result is assigned to a variable name as shown below:

Syntax:

$selectSQL=(“SELECT field_names FROM tablename”);

Example:

[code]
$selectSQL=(“SELECT * FROM tblstudent”);
[/code]

MySQL CREATE Command

CREATE Command

The Create command is used to create a table by specifying the tablename, fieldnames and constraints as shown below:

Syntax:

$createSQL=(“CREATE TABLE tblName”);

Example:

[code]

$createSQL=(“CREATE TABLE tblstudent(fldstudid int(10) NOTNULL AUTO_INCREMENT PRIMARY KEY,fldstudName VARCHAR(250) NOTNULL,fldstudentmark int(4) DEFAULT ‘0’ “);

[/code]