Perl script make MySQL query, binds variables, outputs JSON

Friday, July 16, 2010 - 8:40 pm
By Colin
Bookmark and Share

An example of a Perl script that is called by an ajax request. The script takes in just one parameter, opens a MySQL DB connection, binds the parameter to the query, and sends the query. The result is then formatted into JSON and returned to the browser.

This can be seen in action on this page: http://www.ruddwire.com/friendly-data/naics-explorer/, in the NAICS search box.

 
#!/usr/bin/perl
 
use strict;
use DBI();
use CGI qw(:standard);
 
 
my $dbh;
my $sth;
my $mySQL;
my $result = '({"failure":1})';
 
#QUERY PARAMS
my $q;
 
 
 
my $ref;
my $table = "";
my $tableRow;
 
my $query = new CGI;
 
 
 
 
 
if($query->param()){
 
 
    $q = $query->param('q');
    $q =~ s/\s+/%/;  # REPLACE SPACES WITH WILD CARD CHARACTER, CAUSES TO SEARCH FOR ALL WORDS, NOT JUST PHRASE
 
    ### Connect to the database.
	my $dbh = DBI->connect("DBI:mysql:database=***DBNAME***;host=localhost",
                         "***USERNAME***", "***PASSWORD***",
                         {'RaiseError' => 1});
 
    	### GET INDUSTRY NAME AND CODE
    	$mySQL = "SELECT code,industry FROM naics2007IndustryIndex WHERE industry LIKE ? ORDER BY industry";
 
 
     $sth = $dbh->prepare($mySQL);
     $sth->execute("%".${q}."%");
 
      while ($ref = $sth->fetchrow_hashref()) {
 
		  while ((my $key, my $value) = each(%$ref)){
		  	  $value =~ s/"/\\"/g;
			  $tableRow .= "\"${value}\",";
		  }
 
		  $tableRow =~ s/,$//;
		  $tableRow = "[${tableRow}],";
 
		  $table .= $tableRow;
		  $tableRow = "";
      }
 
		$table =~ s/,$//;
 
	  	$result = '{"failure":0,"result":['.$table.']}';         
 
}### END IF QUERY
 
 
 
print "Content-type: text/javascript; charset=iso-8859-1\n\n";
 
print $result;
 
exit(0);
Bookmark and Share

Filed under: code, tech.
Tags: , ,
Follow responses to this entry through the RSS 2.0 feed.

Leave a Reply