In a previous article, I talked about my idea of setting up a intermediate web page to serve as a go to for getting and retrieving database operations. Here I expand on this idea.
A few of you asked me to show more insight in the Database Submitter discussed in a previous article. This involved setting up a WAMP server local to your Windows system and utilizing the MySql and Apache components.
By doing so, we could automate our web projects and persist our data. The idea is to create a html form containing a textarea and button. The textarea will be supplied with an SQL statement. When the Go button is clicked, it requests MySql to perform the database operation.
The result shown can be scraped by your web automation tool, like Ubot Studio or Selenium. I realize that there are two different types of people who are going to read this article. First is the automation scripter who may not know web development. Second is the web developer who knows programming. I am going to assume the former, to keep this discussion on ground floor footing.
In the land of web automation, this idea solves the gap between most Web Automation tools not having a database driver. That makes it a significant solution, as trivial as it may seem.
For those who may not be familiar with web programming, this may seem a bit daunting. However, in order to fully bring more power to your web automation projects, you must familiarize yourself with a programming language like PHP or C# along with a smattering of Apache and MySql.
What I will supply below is the complete source code for the Database Submitter. In order to use, you must have previously installed a WAMP stack on your Windows computer. Then, setup a MySql database along with user account.
<!doctype html>
<html>
<head>
<title>Database Submitter</title>
<style type="text/css">
body { margin: 2em auto; width: 640px; background-color: #eee; font-size: 1.5em; }
#page { padding: 24px; background-color: white; }
td { padding: 0.5em }
input#btnGo { padding: 0.5em; }
#result { font-size: 80%; }
</style>
</head>
<body>
<div id="page">
<h1>Database Submitter</h1>
<form name="frmSql" method="post">
<table>
<tr><td><label>SQL statement:</label></td><td><textarea name="dbSql" id="dbSql" rows="20" cols="50"></textarea></td></tr>
<tr><td colspan="2"><input name="btnGo" id="btnGo" type="submit" value="Go!"/></td></tr>
</table>
</form>
<?php
// -------------------------------------
// If go button wasn't pressed exit
// -------------------------------------
if (!isset($_POST['btnGo']))
exit(0);
// -------------------------------------
// If no SQL command exit
// -------------------------------------
if (!isset($_POST['dbSql'])) {
die("Error: Enter in a SQL statement.");
}
// -------------------------------------
// Fill in database stuff here
// --------------------------------------
$dbName = "MyDatabase";
$dbUsername = "root";
$dbPassword = "password";
$dbSql = $_POST['dbSql'];
// -------------------------------------
// Connect to database
// -------------------------------------
$dbLink = mysql_connect("localhost", $dbUsername, $dbPassword);
if (!$dbLink) {
die("Error: Cannot create database connection.");
}
// -------------------------------------
// Select database
// -------------------------------------
$dbSelected = mysql_select_db($dbName, $dbLink);
if (!$dbSelected) {
die("Error: Cannot select database.");
}
// -------------------------------------
// Query
// -------------------------------------
$dbResult = mysql_query($dbSql, $dbLink);
if (!$dbResult) {
die("Error: Cannot execute SQL statement.");
}
// --------------------------------------------------
// Determine sql command and do appropriate output
// --------------------------------------------------
$aSqlCommand = explode(" ", $dbSql);
$sqlCommand = $aSqlCommand[0];
switch(
strtoupper($sqlCommand)) {
case "INSERT":
break;
case "SELECT":
$strResult = "";
while($row = mysql_fetch_row($dbResult)) {
for ($i=0; $i < count($row); ++$i) {
$strResult .= $row[$i];
if ($i < count($row)-1)
$strResult .= ",";
}
}
echo "<div id='result'>";
echo $strResult;
echo "</div>";
break;
}
// -------------------------------------
// Close database link
// -------------------------------------
mysql_close($dbLink);
?>
</div>
</body>
</html>
The code consists of two parts:
The HTML and CSS portion simply marks up a form. Your web automation tool will enter in a SQL statement into the textarea and then click the go button. On return a comma separated variable string will be shown in the case of SELECT sql statements. This allows you to scrape the result (via id='result') and parse the output to your liking. You can enter in any SQL statement.
The PHP portion build a MySql connection and selects your specified web automation database. The query supplied is executed. If there were no problems, we then build the comma separated value string from each database column and return its values. The database connection is then closed.
Notice none of this discusses the database schema. Thats the beauty of it. You design your own database as you see fit.
If your project, for example, is to automate the indexing of your company's email addresses from your corporate employees blogs, you would build up a database table including the following:
# email accounts
create table if not exists email_account (
user_id int not null auto_increment,
first_name varchar(16) default null,
last_name varchar(16) default null,
email varchar(32) default null,
PRIMARY KEY (email_account_id)
);Then, build a script with your web automation tool to scrape each blog searching for the email address on their profile page. The script would navigate to your database submitter page, and do an SQL INSERT:
INSERT into email_account (first_name,last_name,email) VALUES ('Fred', 'Flintstone', 'fredflinstone@mycompany.com')I'm sure you could come up with many more ideas using this technique. Happy programming!
