Connecting to a database is a very common task for cgi scripts but it can be a very involved process. Below we’ll step through connecting to, inserting into and querying a database and displaying back the results.<\/p>\n
If you haven’t already created a MySQL database you can follow the How to create a MYSQL database<\/a> guide. For the purpose of this example we’ll use the database name my_database<\/strong>, remember to replace this with your actual database name.<\/p>\n To make connecting to and using the database easier we’re going to use the Perl DBI module. Lets start at the top of our script with the location of the Perl interpretor and then we tell Perl we want to use the DBI module and then create a DBI object.<\/p>\n <\/br>The DBI module is object-orientated, this means all of the DBI functions and data are accessed through an instance of DBI, in our script this instance is called $dbh<\/i>, short for database handle.<\/p>\n Lines 4 to 7<\/strong> – Defines local variable to store our connection data, make sure to update them with your actual database details.<\/p>\n Line 9<\/strong> – Creates the DBI object by calling the connect function using the variables we defined, if the connection fails the script will die.<\/p>\n Lets create a simple contacts table and put some details in it. We’ll store first name, surname and email address.<\/p>\n <\/br>Lines 10 to 15<\/strong> – We build a string of SQL to create our table. On line 18 we prepare our SQL statement and assign it to a new local varible $sth<\/i>, short for statement handle.<\/p>\n Line 18<\/strong> – We call the execute function on our $sth object and pass it our SQL string to actually create the table on the database.<\/p>\n Next we’ll put some data into our new table.<\/p>\n <\/br>Line 20<\/strong> – Here we prepare our new SQL statement, notice at the end of the statement we have a set of question marks. These are used as place-holders for the actual values we will put in when we execute the statement.<\/p>\n Lines 21 to 23<\/strong> – Executes our SQL statement passing in three values for first name, surname and email to replace the place-holders.<\/p>\n Now that we’ve added some data into our database lets query it for all contacts whose first name is Matt.<\/p>\n <\/br>Again, we prepare our SQL statement and use a place-holder for the value of our WHERE clause which tells the database to return all contact where the first_name field equals the value we pass in when we execute our query.<\/p>\n Finally, we want to display this data back in a web page.<\/p>\n <\/br>First print back the HTTP header to let the web browser know what type of content to expect. Then we print out the first part of the HTML page. We use a while loop to iterate over all the results returned by calling the fetchrow_hashref<\/i> function on our $sth<\/i> object, for each result we print out the field data and HTML for a table cell.<\/p>\n This example script shows a very basic way to get form contents emailed to you, it doesn’t however have the refinements of a professional script, e.g. input validation. Below is the finished script. We’ve added some comments (lines beginning with #) to help make it clearer.<\/p>\n <\/br>Replace the example database name, username and password, save this script as database_test.cgi<\/strong> and upload it to the cgi-bin<\/strong> on your web hosting. Make sure you set the file permissions for the script to 755 as described above<\/strong>.<\/p>\n Now you’re ready to test your database script. Load up your database test in your browser, http:\/\/www.domain.com\/cgi-bin\/database_test.cgi. If everything works you should see a html page show a table of contacts. If not, try checking out our guides below.<\/p>\n Connecting to a database is a very common task for cgi scripts but it can be a very involved process. Below we’ll step through connecting to, inserting into and querying a database and displaying back the results. Connecting to the database If you haven’t already created a MySQL database you can follow the How to […]<\/p>\n","protected":false},"author":1,"comment_status":"closed","ping_status":"open","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[15],"ht-kb-tag":[5],"class_list":["post-110","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-creating-a-new-website","ht_kb_tag-web-hosting"],"yoast_head":"\n#!\/usr\/bin\/perl\n\nuse DBI;\nmy $database_name = \"my_database\";\nmy $database_host = \"mysql.lcn.com\";\nmy $database_user = \"username\";\nmy $database_password = \"password\";\n\nmy $dbh = DBI->connect(\"dbi:mysql:$database_name:$database_host\", $database_user, $database_password) or die \"Unable to connect: $DBI::errstrn\";<\/pre>\n
Create a table<\/h3>\n
my $sql = \"CREATE TABLE IF NOT EXISTS contacts (\nid INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),\nfirst_name VARCHAR(64),\nsurname VARCHAR(64),\nemail VARCHAR(255))\";\nmy $sth = $dbh->prepare($sql);\n$sth->execute() or die \"SQL Error: $DBI::errstrn\";<\/pre>\n
Insert data into the database<\/h3>\n
$sth = $dbh->prepare(\"INSERT INTO contacts(first_name, surname, email) VALUES(?,?,?)\");\n$sth->execute(\"Nick\", \"Jones\", \"nick.jones@foo.com\");\n$sth->execute(\"Matt\", \"Smith\", \"matt.smith@bar.com\");\n$sth->execute(\"Matt\", \"Brown\", \"matt.brown@baz.com\");<\/pre>\n
Query the database<\/h3>\n
$sth = $dbh->prepare(\"SELECT first_name, surname, email FROM contacts WHERE first_name = ?\");$sth->execute(\"Matt\");<\/pre>\n
Display back database results<\/h3>\n
print \"Content-type: text\/plain\\n\\n\";\n\nprint &rlaquo;<HTML_PAGE;\n<html>\n<head>\n<title>Database Test<\/title>\n<\/head>\n<body>\n<h1>Database Test<\/h1>\n<table>\n<tr>\n<th>First Name<\/th>\n<th>Surname<\/th>\n<th>Email<\/th>\n<\/tr>\nHTML_PAGE\n\nwhile( $row = $sth->fetchrow_hashref ) {\nprint \"<tr><td>$row->{first_name}<\/td>\"; \nprint \"<td>$row->{surname}<\/td>\"; \nprint \"<td>$row->{email}<\/td><\/tr>\";\n}\n\nprint <<HTML_PAGE;\n<\/table>\n<\/body>\n<\/html>\nHTML_PAGE<\/pre>\nThe final script<\/h3>\n
#!\/usr\/bin\/perl\n\nuse DBI;\n\n# Define database details\nmy $database_name = \"my_database\";\nmy $database_host = \"mysql.lcn.com\";\nmy $database_user = \"username\";\nmy $database_password = \"password\";\n\n# Connect to database and create DBI object\nmy $dbh = DBI->connect(\"dbi:mysql:$database_name:$database_host\", $database_user, $database_password) or die \"Unable to connect: $DBI::errstrn\";\n\n# Create contacts table\nmy $sql = \"CREATE TABLE IF NOT EXISTS contacts ( \nid INT NOT NULL AUTO_INCREMENT, \nPRIMARY KEY(id), \nfirst_name VARCHAR(64), \nsurname VARCHAR(64), \nemail VARCHAR(255))\";\n\nmy $sth = $dbh->prepare($sql);\n$sth->execute() or die \"SQL Error: $DBI::errstrn\";\n\n# Insert data into the table\n$sth = $dbh->prepare(\"INSERT INTO contacts(first_name, surname, email) VALUES(?,?,?)\");\n$sth->execute(\"Nick\", \"Jones\", \"nick.jones@foo.com\");\n$sth->execute(\"Matt\", \"Smith\", \"matt.smith@bar.com\");\n$sth->execute(\"Matt\", \"Brown\", \"matt.brown@baz.com\");\n\n# Select data from the database\n$sth = $dbh->prepare(\"SELECT first_name, surname, email FROM contacts WHERE first_name = ?\");\n$sth->execute(\"Matt\");\n\n# Print out HTTP header\nprint \"Content-type: text\/html\\n\\n\";\n\nprint <<HTML_PAGE;\n<html>\n<head>\n<title>Database Test<\/title>\n<\/head>\n<body>\n<h1>Database Test<\/h1>\n<table>\n<tr>\n<th>First Name<\/th>\n<th>Surname<\/th>\n<th>Email<\/th>\n<\/tr>\nHTML_PAGE\n\n# Loop over database query results\nwhile( $row = $sth->fetchrow_hashref ) { \nprint \"<tr>$row->{first_name}<\/td>\"; \nprint \"<td>$row->{surname}<\/td>\"; \nprint \"<td>$row->{email}<\/td><\/tr>\";\n}\n\nprint <<HTML_PAGE;\n<\/table>\n<\/body>\n<\/html>\nHTML_PAGE<\/pre>\n\n