Constructing SELECT Queries to the MySQL Database

Let's talk about some simple queries that you can run against the MySQL database using Perl's DBI.

Here is a simple query that will return all of the records listed in the table:

$sth=$dbh->prepare("SELECT * FROM tablename");

Here is a slightly more complex query that selects records based on some condition:

$sth=$dbh->prepare("SELECT * FROM tablename WHERE column_name= desired_value ");

SQL supports a type of pattern matching by using LIKE and the % sign as a wild card. Here is a search that will return records with 'foo' somewhere in the searched column:

$sth=$dbh->prepare("SELECT * FROM tablename WHERE column_name LIKE '%foo%' ");
# returns on barfoobar, foobar, foo, and barfoo.
# 'foo%' would only return on foobar and foo.
# '%foo' would return on barfoo and foo.

You can also pair up your conditionals by using AND and OR logic:

$sth=$dbh->prepare("SELECT * FROM tablename WHERE column_namea= first_value AND column_nameb=second_value");
$sth=$dbh->prepare("SELECT * FROM tablename WHERE column_namea= first_value OR column_nameb=second_value");

Numerical values can be compared mathematically using <>= or ! (not);

$sth=$dbh->prepare("SELECT * FROM tablename WHERE column_namea >= number ");

One of the nice features about using DBI is that you can dynamically build your query strings with your script before executing them.

$value_qw=$dbh->quote($inputs{'value'});

if($inputs{'value'} ne ""){ # if you test for the existence of $value_qw it will be true, it has quotes now!
$query_string=" column_namea=$value_qw ";}
else{$query_string=" column_namea=column_namea ";}

$sth=$dbh->prepare("SELECT * FROM companylist WHERE $query_string");

Exciting stuff, yes?