We accept safe and secure Credit Card and PayPal Payments.
 
Perl Scripts

All Count
Attachment Mailer
Perl Scripts Build A FAQ Plus
Perl Scripts Clock In Center
eBackup Automated
Easy Poll
eSurvey
Fetch a File
Form Maker
Mailing List Server
MySQL Mate
PDF Creation
QCart
Quick Fix
Quote of the day
Speed Search
Task Manager
Traffic Pack
Upload Plus
Upload Gold
Upload Pro
Website Manager

 
Free Perl Downloads

Free Perl Scripts Main Page
Free Perl Scripts 404 Alerter
AccessLog Viewer
Build A FAQ
PHP Scripts eBackup
Free PHP Scripts ErrorLog Viewer
eVars - Server Info
HT Pass Creator
Upload Lite
Website Manager

 
JavaScripts

Free Java Scripts Alert Boxes
Free JavaScripts Browser Sniffer
Check email
Generators
Slide Show
Sudoku
Window Maker
More...

 
Extra Utilities

ASP Scripts Ascii Codes
Free ASP Scripts Color Picker
Font Finder
HT Pass Creator
Meta Cloak
Meta Magic
Pano Zooms
SlideShow
Server Size

 
Online Tutorials

Free HTML Scripts Glossary
Free HTML Scripts HTML
JavaScript
MySQL
SSI
Build Traffic
Other

 
Miscellaneous

About Us
Graphics
Testimonials
Installations
Latest versions

 
Hawk Eye in Tennis

Should Hawk Eye replace linesmen and lineswomen at all tennis tournaments?






 
View all Polls

Run Polls on your site

Run your own Surveys

 
Store Download FAQs Contact Us Support Programming Policies  
Using MySQL with Perl Please also see our dedicated mySQL FAQ

Overview
What you need
Connecting
Basic Queries
Advanced Queries
Table Joins
Tips and Functions




Purchase MySQL Mate, a MySQL Database Manager for just $10!

Manage multiple MySQL Databases and send yourself table back ups before its too late!



Overview

MySQL is the world's most popular open source, binary database server. It is capable of supporting over 50,000,000 records. SQL (Structured Query Language) is the language used to to communicate with the MySQL server. With the aid of this tutorial, you will learn how to build a RDBMS (Relational DataBase Management System). Table Joins, are the key to building efficient databases and harnessing the essential power of MySQL. Without table joins, a complex relational database will be as slow as a standard flat file database. PerlScripts JavaScripts.com will teach you how to utilise table joins and greatly increase the speed of your database queries. PerlScripts JavaScripts.com has taught lead programmers of other programming firms how to utilize the true power of MySQL.

Top




What you need

You will need a web host that offers access to a MySQL server. Most hosts offer some sort of control panel, allowing you to set up and create your databases. If you need MySQL hosting, we have MySQL enabled plans from as little as $13.70. Once you have a host, you will need to ask them for certain information or use your control panel to create a database. Our Basic hosting plan ($13.70/mo) allows you to create up to 3 databases. The information you need is :

Path to the server

Database name

Username

Password

Top




Connecting to the DB Server

Connecting to the database is easy. First you must load the MySQL module. Near the top of your script add this code :

use Mysql;

The line above will load the module ( or class of predefined subroutines ) named Mysql.pm into your script. To make database connections quick and easy, place your login details into a set of variables. These variables will be passed as arguments to the connect subroutine and should be declared near the top of your script or before using the connect command.

$DBHOST = "localhost";
$DBNAME = "mydatabase";
$DBUSER = "perlscripts";
$DBPASS = "ywe6ywnq";

You now need to begin constructing what is known as an object. Below is an example of how to utilize the connect subroutine.

$DB = Mysql->connect($DBHOST, $DBNAME, $DBUSER, $DBPASS);

Once the above code is executed, assuming the login details are correct, you are connected to the MySQL server. For demonstration purposes only, the code below could be used to select and print the first 100 records from a table named "employees".

$DB  = Mysql->connect($DBHOST, $DBNAME, $DBUSER, $DBPASS);
$qry = qq~SELECT * FROM employees WHERE id < 100~;

while( @emps = $qry->fetchrow) {
    print qq~
    $emps[0], $emps[1], $emps[2] <br>
    ~;
}
The code above, when translated into English, says "Connect to the server, select all columns from the table named employees where id is less than 100, then while the data is placed into an array called emps using the fetchrow method, print columns 1, 2 and 3 then a line break."

Top




Basic Queries

Some of the more popular queries are the easiest to execute, but can also be the most dangerous. For example, to delete a record, you might use :

DELETE FROM employees WHERE age > 35

However, if you forgot to enter a WHERE clause and condition, you would delete the entire table.

Top




Advanced Queries

Advanced queries should be used in preference to extracting data then formatting it using another language. Many programmers either do not have the knowledge or just do not understand the power and speed of MySQL. The MySQL server is all about speed and efficiency. Manipulate and format the data as much as possible using MySQL before passing it on to Perl or PHP. Use built in functions and nested functions to achieve your desired result. In most cases, you will be able to extract all necessary records from all tables in one call to the database.

Top




Table Joins

Table Joins are by far the most useful function on large and complex databases. They allow you to select and retrieve records from multiple tables in one action. When selecting records from multiple tables, you must use a common denominator other wise many more records than you expected will be returned. There must a column name common to two or more tables. Let's assume we have two tables with the following structure :

Table 1

employees

Columns

id
firstname
lastname
address

Table 2

salary

Columns

id
hourly
ssn


Note that the common column is the id column. Your where clause must contain a common column, otherwise more records than you expect will be returned. In the following select statement, each table is separated by a comma. We are selecting all columns (denoted by an asterisk *) where the id is identical in both tables.

SELECT * FROM employees, salary WHERE employees.id = salary.id &&
employees.firstname = 'Grayson'

In the example above, column names are preceded by table names to avoid confusion. Strings (as opposed to integers) must be quoted. The id is an integer (a whole number) so it does not need to be quoted. The first name is a string, so it must be quoted. Note that the common column is the id. Without the id check (employees.id = salary.id), all records from both tables that contain the string 'Grayson' would be returned.

Top




MySQL Tips and Functions

Comparison operators
= Equals
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
<> Not equal to
!= Not equal to
LIKE Compare strings


Mathematical operators
( ) Use parentheses to force precedence
+ add
- subtract
* multiply
/ divide
% modulo


Conditional operators
|| Logical "or"
| Bitwise "or"
OR Same as Logical "or"
&& Logical "and"
& Bitwise "and"
AND Same as Logical "and"


Some Common Functions
CONCAT(str1,str2) Concatenates strings
DAYNAME(date) Returns the dayname for the given date
LAST_INSERT_ID( ) Returns the last record ID inserted
LENGTH(column) Returns the length of a string in characters
TRUNCATE(n,n) Returns stated number of decimal places
UNIX_TIMESTAMP( )   Returns the epoch


Some Aggregate Functions
AVG(column) Average number from returned results
MAX(column) Returns the largest number from returned results
MIN(column) Returns the smallest number from returned results
SUM(column) Returns the sum of all returned results



Note : not all databases servers support all functions, many depend on the version your server is running. There are hundreds of functions that can be used alone or nested within one another. You really should buy a book for a complete reference.

Top

Speed Search our site

Search script for your site
Mailing list management made easy

Linux Hosting Plans from $9.12 per month, includes Plesk Control Panel, MySQL databases, cgi-bin, crontab manager and 50 email accounts.

Discounted Scripts
Subscribe to our periodial newsletter to receive special offers.

Bathroom Hygiene
How often do you thoroughly clean your bathroom?








View all Polls

Run Polls just like this one on your website!


About us | Contact us | Script FAQs | Script Support | Website Hosting | Our Policies | Store | Testimonials
Subscribers log in here. Subscribe to our periodical newsletter for special offers on commercial scripts, hosting, exciting business opportunities, and industry news and events. Our Mailing List Software is powered by Consolidator    
HTML Plain text
©1999 - 2011 All content Copyright PerlScriptsJavaScripts.com Proudly hosted by LinuxHostingPlans.com