| |
#!/usr/bin/perl
# use strict; use warnings;
use CGI; use DBI; use HTML::Template;
# -----------------------------------------------
# listings.pl
# -----------------------------------------------
# Revisions:
# 04:40pm - 04 Dec 2003 -- Recoded from scratch,
# using template system to display data. SQL and
# server-side includes' based.
#
# 11.20pm - 11 Dec 2003 -- Updated GUI due to
# an addtional SQL column, 'video'. Listings
# screens were edited to represent this change.
#
# Notes:
# You are advised to call this script via SSI,
# if you wish to limit which listings appear,
# then please pass the variable titled 'i' to
# the script when calling, else leave it empty
# for full listings. Eg:
#
# All listings:
#
#
# Display 'beacon' listings only:
#
#
# -----------------------------------------------
# Developed and debug tested by:
# Zeeshan Muhammad
# -----------------------------------------------
# New CGI object
my $q = new CGI;
# SQL login details
my ($sql_username, $sql_password, $sql_host) = qw(marketinghomes_1
mhmysql
DBI:mysql:marketinghomes_com_1;mysql1.dca1.superb.net);
# Open the default HTML template file
my $t = HTML::Template->new(filename => 'listings.html',
die_on_bad_params => 0,
debug => 0
);
# Connect to SQL
my $dbh = DBI->connect($sql_host, $sql_username, $sql_password) or &error("Unable to connect to SQL server: $DBI::errstr.");
# Build SQL table before attempting to access it - code safety
{
local $dbh->{RaiseError};
local $dbh->{PrintError};
$dbh->do("CREATE TABLE `mhsites` (`mls` varchar(15) NOT NULL default '0',
`address` tinytext,
`price` varchar(15) default NULL,
`description` text,
`exposure` tinytext,
`size` tinytext,
`amenities01` tinytext,
`amenities02` tinytext,
`amenities03` tinytext,
`amenities04` tinytext,
`amenities05` tinytext,
`beds` tinytext,
`baths` tinytext,
`zonenumber` tinytext,
`subarea` tinytext,
`gallery` tinytext,
`listing` tinytext NOT NULL,
`realtor` tinytext,
`sold` tinytext,
`video` tinytext,
PRIMARY KEY (`mls`)) TYPE=MyISAM;"
);
};
my $sth;
# Check to see if a limited listing is requested
if ($q->param('i')) {
# Check to see if string is safe, we only allow
# a-z0-9 character set in -- prevents malicious abuse
if ($q->param('i') !~ m/^[a-z0-9]+$/) {
&error("Unexpected characters exists in 'i' variable. Program aborted for safety.");
};
# Form SQL directives which only return request listings type
$sth = $dbh->prepare("SELECT `mls`, `price`, `address`, `beds`, `baths`, `gallery`, `sold`, `video`, `listing` FROM `mhsites` WHERE `listing` = @{[$dbh->quote($q->param('i'))]} ORDER BY `sold` ASC") or &error("Unable to prepare SQL query via DBI: $DBI::errstr.");
}
else {
# Modified by Tyler to display all non cheri listings
# Form SQL directives which return all listing types
$sth = $dbh->prepare("SELECT `mls`, `price`, `address`, `beds`, `baths`, `gallery`, `sold`, `video`, `listing` FROM `mhsites` WHERE `listing`<>'cheri' ORDER BY `sold` ASC") or &error("Unable to prepare SQL query via DBI: $DBI::errstr.");
# old zeeshan code
# $sth = $dbh->prepare("SELECT `mls`, `price`, `address`, `beds`, `baths`, `gallery`, `sold`, `video`, `listing` FROM `mhsites` # WHERE `listing`<>'cheri' ORDER BY `sold` ASC") or &error("Unable to prepare SQL query via DBI: $DBI::errstr.");
};
$sth->execute or &error("Unable to execute SQL directive: $DBI::errstr.");
# Hash in array container, we must declare this to be global,
# else it will be empty outside of the while loop
my @t_loop;
while (my @i = $sth->fetchrow_array) {
my ($sql_mls, $sql_price, $sql_address, $sql_beds, $sql_baths, $sql_gallery, $sql_sold, $sql_video, $sql_listing) = @i;
my(%t_loop) = (MLS => $sql_mls,
Price => $sql_price,
Beds => $sql_beds,
Baths => $sql_baths,
PhotoURL => $sql_gallery,
);
# Fix the explict listings URL (for template.pl)
if ($sql_listing eq 'gabriola' or $sql_listing eq 'beacon' or $sql_listing eq 'newcastle') {
$t_loop{FixedURL} = 'cameronisland';
} elsif ($sql_listing eq 'cheri') {
$t_loop{FixedURL} = 'cheri';
} else {
$t_loop{FixedURL} = 'gh';
};
# Trim the the address
if (length($sql_address) > 45) {
$t_loop{Address} = substr($sql_address, 0, 42) . '...';
} else {
$t_loop{Address} = $sql_address;
};
# Set sold status
if ($sql_sold eq 'SOLD') {
$t_loop{Sold} = 1;
};
# Set video presence status
if ($sql_video) {
$t_loop{VideoStatus} = 1;
};
# Push the hash into the array
push(@t_loop, \%t_loop);
};
$t->param(DefaultAction => 1,
ListingsLoop => \@t_loop
);
# MIME header
print $q->header;
# Output template
print $t->output;
# Raise error flags and display HTML document
sub error {
# MIME header
print $q->header;
$t->param(DefaultError => $_[0]);
# Output template
print $t->output; exit;
}; |
