Section 3 - SQL:

The other main alternative to flat files that has gotten very popular is using SQL. SQL (structured query language) has existed for quite a long time, and has been used by many different companies, as well as individuals.

One of today's hot moving SQL servers is MySQL, as it is open source and allows for a lot of options for the developers. I will mainly discuss how to store data in MySQL databases, however because SQL, and DBI are very portable, the code can be very easily to connect to other RDBMS.

I will use the same example that I used for flat file databases, but in this case using MySQL as the storage tool, and rather than having to handle everything in the program, I will let MySQL handle what it was designed to do.

Some of the requirements for this is that you have a MySQL database to connect to and know the database name, host, username, and password set as $db_name, $db_host, $db_user, and $db_pass respectively.

For this example you must create a simple table with the following information.

Retrieving the data from the database is much easier to do in SQL because you do not have to handle all the different cases of the actual retrieving data from disk, etc but rather just requesting MySQL to do that for you, thus reducing your job by a lot. This process is much more efficient in the long run because MySQL as a database product has been optimized greatly for speed and ease to suit many different situations that could occur and chances are it will suit your needs better than a flat file if you are doing the right thing. There are always cases when flat file databases however will be more efficient.

# First we must load the DBI module.
use DBI;
# Now again we connect to the database.
$dbh = DBI->connect("DBI:mysql:$db_name:$db_host", $db_user, 
$db_pass, { RaiseError => 1, PrintError => 1 }) or die($DBI::errstr);
# Now we must prepare the SQL query to send to MySQL to tell it 
# to retrieve data for us.  This query will retrieve ALL rows from the database.
$search_th = $dbh->prepare('select * from userinfo') or die($DBI::errstr);
# Now we must actually send the data to the MySQL server for processing.
$search_th->execute or die($DBI::errstr);
# Check if any data has been found, if not then we die.
unless ($search_th->rows) {
# Tell DBI we don't want anymore information from the query. (DBI will
# automatically do this for only ONE last query.  If you have more than one
# you must do this manually.)
	$search_th->finish;
	# Disconnect from MySQL and destroy $dbh object.
	$dbh->disconnect;
	# die('No data was found in the database.') 
}
# I will use the hash way of retrieving the information from the 
#database in this example because it is easier to understand, 
#but slower than using arrays.  This will set the $results to the 
#values in the columns until there is no more data left.
while (my $results = $search_th->fetchrow_hashref) {
	# Print the data out.
	print "Time: $results->{time}\n";
	print "IP: $results->{ip}\n\n";
}
# We don't want to do anything with the data anymore.
$search_th->finish;
# Now the final step is to disconnect from the database.
$dbh->disconnect;